Weekly + Monthly Query for Payment Providers

############## Monthly Query for Payment Provider

set @startdate1 = '2019-01-01';
set @startdate2 = '2019-12-31';
Select
month(A.start_Date) as month,
year(A.start_Date) as year,
Count(Distinct A.rid) as Total_attempts,
count(distinct case when A.provider = 'mcp'  then A.rid end) as Total_MCP,
count(distinct case when A.provider = 'braintree'  then A.rid end) as Total_braintree,
count(distinct case when A.provider = 'grabpay'  then A.rid end) as Total_grabpay,
count(distinct case when A.provider = 'stripe'  then A.rid end) as Total_stripe,
count(distinct case when A.provider = 'mcp' and A.state not in ('fail')  then A.rid end) as MCP_Good,
count(distinct case when A.provider = 'braintree' and A.state not in ('fail')  then A.rid end) as braintree_Good,
count(distinct case when A.provider = 'grabpay' and A.state not in ('fail')  then A.rid end) as grabpay_Good,
count(distinct case when A.provider = 'stripe' and A.state not in ('fail')  then A.rid end) as stripe_Good,
count(distinct case when A.provider = 'mcp' and A.state in ('fail')  then A.rid end) as MCP_failed,
count(distinct case when A.provider = 'braintree' and A.state in ('fail')  then A.rid end) as braintree_failed,
count(distinct case when A.provider = 'grabpay' and A.state in ('fail')  then A.rid end) as grabpay_failed,
count(distinct case when A.provider = 'stripe' and A.state in ('fail')  then A.rid end) as stripe_failed
from
(select
distinct r.id as rid, r.member_id as mid, r.state as state, p.paid_type as type, pm.provider as provider,
Date(r.created_at + interval '8' hour) as start_Date, p.paygate as provider_2
from reservations r left join members m on r.member_id = m.id
LEFT JOIN payment_methods pm ON r.payment_method_id = pm.id
left join payments p on p.reservation_id = r.id
where r.created_at + interval 8 hour >= @startdate1
and r.created_at + interval 8 hour <= @startdate2
and r.member_id not in ('125', '127')
group by rid, type) A
group by 1
order by 1 desc;


######################## Weekly Query for Payment Provider

set @startdate1 = '2019-01-01';
set @startdate2 = '2019-12-31';
Select
weekofyear(A.start_Date) as month,
year(A.start_Date) as year,
Count(Distinct A.rid) as Total_attempts,
count(distinct case when A.provider = 'mcp'  then A.rid end) as Total_MCP,
count(distinct case when A.provider = 'braintree'  then A.rid end) as Total_braintree,
count(distinct case when A.provider = 'grabpay'  then A.rid end) as Total_grabpay,
count(distinct case when A.provider = 'stripe'  then A.rid end) as Total_stripe,
count(distinct case when A.provider = 'mcp' and A.state not in ('fail')  then A.rid end) as MCP_Good,
count(distinct case when A.provider = 'braintree' and A.state not in ('fail')  then A.rid end) as braintree_Good,
count(distinct case when A.provider = 'grabpay' and A.state not in ('fail')  then A.rid end) as grabpay_Good,
count(distinct case when A.provider = 'stripe' and A.state not in ('fail')  then A.rid end) as stripe_Good,
count(distinct case when A.provider = 'mcp' and A.state in ('fail')  then A.rid end) as MCP_failed,
count(distinct case when A.provider = 'braintree' and A.state in ('fail')  then A.rid end) as braintree_failed,
count(distinct case when A.provider = 'grabpay' and A.state in ('fail')  then A.rid end) as grabpay_failed,
count(distinct case when A.provider = 'stripe' and A.state in ('fail')  then A.rid end) as stripe_failed
from
(select
distinct r.id as rid, r.member_id as mid, r.state as state, p.paid_type as type, pm.provider as provider,
Date(r.created_at + interval '8' hour) as start_Date, p.paygate as provider_2
from reservations r left join members m on r.member_id = m.id
LEFT JOIN payment_methods pm ON r.payment_method_id = pm.id
left join payments p on p.reservation_id = r.id
where r.created_at + interval 8 hour >= @startdate1
and r.created_at + interval 8 hour <= @startdate2
and r.member_id not in ('125', '127')
group by rid, type) A
group by 1
order by 1 desc