--02 Open Receivables Client Summary.sql -- SE rewrite of BO Open Receivables client summary -- View: udv_transaction_detail_34 DECLARE @CorpCode varchar(15) = NULL; ;WITH OpenRows AS ( SELECT td.[Corp Code] AS CorpCode, td.[Org Name] AS FormalName, CASE WHEN ISNULL(td.[Invoiced Amount], td.[Transaction Amount]) IS NULL THEN 0 ELSE ISNULL(td.[Invoiced Amount], td.[Transaction Amount]) - ISNULL(td.[Received Amount], 0) END AS OpenAmount, td.[Aging Category] AS AgingBucket FROM se_reports.dbo.[udv_transaction_detail_34] td WHERE td.[Invoice Number] IS NOT NULL AND ( td.[Current or Aging] IS NOT NULL OR ISNULL(td.[Invoiced Amount], td.[Transaction Amount]) > ISNULL(td.[Received Amount], 0) ) AND (@CorpCode IS NULL OR @CorpCode = '' OR td.[Corp Code] = @CorpCode) ) SELECT CorpCode AS ClientKey, MAX(FormalName) AS ClientName, NULL AS ParentCompany, -- unable to find COUNT(*) AS OpenInvoiceCount, SUM(OpenAmount) AS OpenAmount, SUM(CASE WHEN AgingBucket IN ('Current', '0') THEN OpenAmount ELSE 0 END) AS CurrentAmt, SUM(CASE WHEN AgingBucket LIKE '%1%30%' OR AgingBucket = '1-30' THEN OpenAmount ELSE 0 END) AS Days1To30Amt, SUM(CASE WHEN AgingBucket LIKE '%31%60%' OR AgingBucket = '31-60' THEN OpenAmount ELSE 0 END) AS Days31To60Amt, SUM(CASE WHEN AgingBucket LIKE '%61%90%' OR AgingBucket = '61-90' THEN OpenAmount ELSE 0 END) AS Days61To90Amt, SUM(CASE WHEN AgingBucket LIKE '%90%' OR AgingBucket LIKE '%Over%' THEN OpenAmount ELSE 0 END) AS DaysOver90Amt, CAST(GETDATE() AS date) AS AsOfDate FROM OpenRows GROUP BY CorpCode ORDER BY ClientKey;