/* Imprest Account — daily activity + beginning/ending balance (DatawarehouseNCM) Facts: dbo.FactImprestAccount, dbo.FactImprestAccountBalance Ending balance matches BO CalculateTotal: BegBalance (latest posted BalanceDate <= @StartDate) + SUM(FactImprestAccount.Amount) for TrxDate in [@StartDate,@EndDate] */ DECLARE @CorpCode varchar(15) = 'KPHC'; -- required for balance math DECLARE @StartDate datetime = '2026-01-01'; DECLARE @EndDate datetime = '2026-01-31'; DECLARE @DivisionName varchar(30) = NULL; DECLARE @BegBalance money = ( SELECT TOP (1) b.Balance FROM dbo.FactImprestAccountBalance b WHERE b.CorpCode = @CorpCode AND b.BalanceDate <= @StartDate ORDER BY b.BalanceDate DESC ); SELECT ISNULL(@BegBalance, 0) AS BeginningBalance; -- Daily activity SELECT CAST(f.TrxDate AS date) AS TrxDate, 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(f.Amount) AS NetAmount, COUNT(*) AS LineCount FROM dbo.FactImprestAccount f WHERE f.CorpCode = @CorpCode AND f.TrxDate BETWEEN @StartDate AND @EndDate AND ( @DivisionName IS NULL OR @DivisionName = '' OR @DivisionName = '(All Divisions)' OR f.DivisionName = @DivisionName ) GROUP BY CAST(f.TrxDate AS date) ORDER BY TrxDate; -- Ending balance SELECT ISNULL(@BegBalance, 0) AS BeginningBalance, ISNULL(SUM(f.Amount), 0) AS PeriodNetAmount, ISNULL(@BegBalance, 0) + ISNULL(SUM(f.Amount), 0) AS EndingBalance FROM dbo.FactImprestAccount f WHERE f.CorpCode = @CorpCode AND f.TrxDate BETWEEN @StartDate AND @EndDate AND ( @DivisionName IS NULL OR @DivisionName = '' OR @DivisionName = '(All Divisions)' OR f.DivisionName = @DivisionName ); -- Posted balance history SELECT b.CorpCode, b.BalanceDate, b.Balance, b.PONumber FROM dbo.FactImprestAccountBalance b WHERE b.CorpCode = @CorpCode ORDER BY b.BalanceDate DESC;