/* Client Synopsis — client cash balances (DatawarehouseNCM) Crystal: ClientSynopsis.rpt (client-level, balance) Fact: dbo.FactClientSynopsis (SumType = 'B') Cash In / Cash Out / Net cash per client as of the snapshot (AsOfDate = populate run date). */ SELECT f.CorpCode, c.FormalName, c.ParentCompany, SUM(CASE WHEN f.InOut = 'I' THEN f.Amount ELSE 0 END) AS CashIn, SUM(CASE WHEN f.InOut = 'O' THEN f.Amount ELSE 0 END) AS CashOut, SUM(CASE WHEN f.InOut = 'I' THEN f.Amount ELSE 0 END) - SUM(CASE WHEN f.InOut = 'O' THEN f.Amount ELSE 0 END) AS NetCash, MAX(f.AsOfDate) AS AsOfDate FROM dbo.FactClientSynopsis f LEFT JOIN dbo.DimCorpClient c ON RTRIM(c.CorpCode) = RTRIM(f.CorpCode) WHERE f.SumType = 'B' AND f.CashType = 'CASH' GROUP BY f.CorpCode, c.FormalName, c.ParentCompany ORDER BY f.CorpCode;