SQL sub query

PHOTO EMBED

Fri Aug 23 2024 15:08:13 GMT+0000 (Coordinated Universal Time)

Saved by @RehmatAli2024 #deluge 

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", ',', '')
content_copyCOPY