Select subscriptions

select-active-subscribers.sql

create table wp_custom_payments
select
    p.ID as order_id,
    p.post_date,
    p.post_status,
    max( CASE WHEN pm.meta_key = '_billing_email' and p.ID = pm.post_id THEN pm.meta_value END ) as billing_email,
    max( CASE WHEN pm.meta_key = '_billing_first_name' and p.ID = pm.post_id THEN pm.meta_value END ) as _billing_first_name,
    max( CASE WHEN pm.meta_key = '_billing_last_name' and p.ID = pm.post_id THEN pm.meta_value END ) as _billing_last_name,
    max( CASE WHEN pm.meta_key = '_order_total' and p.ID = pm.post_id THEN pm.meta_value END ) as order_total,
    max( CASE WHEN pm.meta_key = '_customer_ip_address' and p.ID = pm.post_id THEN pm.meta_value END ) as order_ip,
    max( CASE WHEN pm.meta_key = '_billing_country' and p.ID = pm.post_id THEN pm.meta_value END ) as country,
    max( CASE WHEN pm.meta_key = '_payment_method' and p.ID = pm.post_id THEN pm.meta_value END ) as payment_method,
    max( CASE WHEN pm.meta_key = '_order_tax' and p.ID = pm.post_id THEN pm.meta_value END ) as order_tax,
    max( CASE WHEN pm.meta_key = '_paid_date' and p.ID = pm.post_id THEN pm.meta_value END ) as paid_date,
    ( select group_concat( order_item_name separator '|' ) from wp_woocommerce_order_items where order_id = p.ID ) as order_items
from
    wp_posts as p,
    wp_postmeta as pm
where
    p.ID = pm.post_id and
    post_date BETWEEN '2017-11-01' AND '2018-03-31'
group by
    p.ID