/* Client Synopsis — daily activity (DatawarehouseNCM) Crystal: ClientSynopsis.rpt (Pm-ShowDaily / chkProcessDaily) Fact: dbo.FactClientSynopsis (SumType = 'D') One day's cash movement (= AsOfDate) per client. Empty unless the populate loaded daily rows. */ SELECT f.CorpCode, c.FormalName, SUM(CASE WHEN f.InOut = 'I' THEN f.Amount ELSE 0 END) AS CashInToday, SUM(CASE WHEN f.InOut = 'O' THEN f.Amount ELSE 0 END) AS CashOutToday, 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 NetCashToday, MAX(f.AsOfDate) AS AsOfDate FROM dbo.FactClientSynopsis f LEFT JOIN dbo.DimCorpClient c ON RTRIM(c.CorpCode) = RTRIM(f.CorpCode) WHERE f.SumType = 'D' AND f.CashType = 'CASH' GROUP BY f.CorpCode, c.FormalName ORDER BY f.CorpCode;