double_active_subscription.sql

-- DOUBLE ACTIVE SUBSCRIPTIONS
 select
	s.subscription_id,
	o.order_id,
	s.next_billing_date as sub_billing_date,
	o.billing_date as order_billing_date,
	o.status as order_status,
	s.status as subscription_status,
	o.first_name,
	o.last_name
from
	subscriptions s
right join orders o on
	s.subscription_id = o.subscription_id
where
	s.subscription_id in (
	select
		double_active.subscription_id
	from
		(
		select
			s.subscription_id,
			count(*) as amount
		from
			orders o
		left join subscriptions s on
			o.subscription_id = s.subscription_id
		where
			o.status = 'Active'
			and s.subscription_id is not NULL
		group by
			s.subscription_id
		having
			amount > 1) as double_active)
order by s.subscription_id;

Get a list of out of sync order/subscriptions

select
	s.subscription_id,
	o.order_id,
	o.first_name,
	o.last_name,
	s.next_billing_date,
	o.billing_date,
	TIMESTAMPDIFF(MICROSECOND, o.billing_date, s.next_billing_date) as delta
from
	subscriptions s
right join orders o on
	s.subscription_id = o.subscription_id
where
	s.subscription_id is not NULL
	and o.status='Active'
having delta != 0;

    subscriptions s
SET
    s.next_billing_date =
    CASE
        -- Ontario, Nationwide is EST --
        WHEN s.zone_id IN ('1','4') 
            THEN IF(TIME(s.next_billing_date) < '18:00:00',
                TIMESTAMP(DATE(s.next_billing_date), TIME('10:00')),
                TIMESTAMP(DATE_ADD(DATE(s.next_billing_date), INTERVAL 1 DAY), TIME('10:00'))
                )
        -- Alberta is MST --
        WHEN s.zone_id IN ('2') 
            THEN IF(TIME(s.next_billing_date) < '20:00:00',
                TIMESTAMP(DATE(s.next_billing_date), TIME('10:00')),
                TIMESTAMP(DATE_ADD(DATE(s.next_billing_date), INTERVAL 1 DAY), TIME('10:00'))
                )
        -- BC is PST --
        WHEN s.zone_id IN ('3') 
            THEN IF(TIME(s.next_billing_date) < '21:00:00',
                TIMESTAMP(DATE(s.next_billing_date), TIME('10:00')),
                TIMESTAMP(DATE_ADD(DATE(s.next_billing_date), INTERVAL 1 DAY), TIME('10:00'))
                )
    END
WHERE s.status IN ('Active', 'Skipped');

Count all active/skipped subscriptions with an active order

select
	count(*)
from
	subscriptions s
where
	s.subscription_id in (
	select
		o.subscription_id
	from
		orders o
	where
		o.status = 'Active')
	and s.status in ('Active', 'Skipped');

count all subscriptions without an active order

select
	count(*)
from
	subscriptions s
where
	s.subscription_id not in (
	select
		o.subscription_id
	from
		orders o
	where
		o.status = 'Active')
	and s.status in ('Active', 'Skipped');