SELECT DISTINCT
"Accounts"."Id" AS "Account ID",
"Accounts"."Account Name" AS "Account Name",
"Promoter"."Id" AS "Promoter ID",
REPLACE("Promoter"."Promoter Name", ',', '') AS "Promoter Name",
if(SUM(CASE
WHEN DATE_FORMAT("Promoters Vs Invoices"."Invoice Date", '%Y-%m') = DATE_FORMAT(today(), '%Y-%m') THEN "Promoters Vs Invoices"."Total"
ELSE 0
END) > 0, 'Invoiced', if(SUM("Check Transactions"."Previous Month Contribution") > 0, 'Invoice Missing', 'No Contributions')) AS "Current Month Billing Status",
SUM(CASE
WHEN DATE_FORMAT("Promoters Vs Invoices"."Invoice Date", '%Y-%m') = DATE_FORMAT(today(), '%Y-%m') THEN "Promoters Vs Invoices"."Total"
ELSE 0
END) AS "Invoiced Current Month",
SUM(CASE
WHEN DATE_FORMAT("Promoters Vs Invoices"."Invoice Date", '%Y-%m') = DATE_FORMAT(today() -INTERVAL 1 MONTH, '%Y-%m') THEN "Promoters Vs Invoices"."Total"
ELSE 0
END) AS "Invoiced Last Month",
SUM(CASE
WHEN DATE_FORMAT("Promoters Vs Invoices"."Invoice Date", '%Y-%m') = DATE_FORMAT(today() -INTERVAL 2 MONTH, '%Y-%m') THEN "Promoters Vs Invoices"."Total"
ELSE 0
END) AS "Invoiced Two Months Ago"
FROM "Accounts"
JOIN "Promoter" ON "Promoter"."Account" = "Accounts"."Id"
LEFT JOIN "Promoters Vs Invoices" ON "Promoters Vs Invoices"."Promoter ID" = "Promoter"."Id"
AND "Promoters Vs Invoices"."Invoice Date" >= DATE_SUB(start_day(month, today()), INTERVAL 3 MONTH)
LEFT JOIN( SELECT
"Promoter Id",
SUM("Contribution Amount") as "Overall Contributions",
SUM(CASE
WHEN DATE_FORMAT("Transaction Date", '%Y-%m') = DATE_FORMAT(today() -INTERVAL 1 MONTH, '%Y-%m') THEN "Contribution Amount"
ELSE 0
END) AS "Previous Month Contribution"
FROM "AWS Transactions - Final (3 Hour Sync)"
GROUP BY "Promoter Id"
) AS "Check Transactions" ON "Check Transactions"."Promoter Id" = "Promoter"."Id"
WHERE "Accounts"."Billing Cycle" != ''
AND "Accounts"."Auto Invoice" = 'Yes'
AND ("Promoter"."Promoter Status" = 'Live'
OR ("Promoter"."Promoter Status" = 'Cancelled'
AND "Promoter"."Cancellation/Pause Date" is not null))
AND "Promoter"."Bill To" is not null
AND "Check Transactions"."Overall Contributions" > 0
GROUP BY "Accounts"."Id",
"Accounts"."Account Name",
"Promoter"."Id",
"Promoter"."Promoter Name"
ORDER BY "Accounts"."Account Name",
replace("Promoter"."Promoter Name", ',', '')
