Daily Balance Query

[IanWaterman]

We have a Balance table which various balances, however, balance is only updated when there is a movement. Previous balance filed has its enddateid updated and a new entry is created with an end date set 100 years in future. Following query finds balance on a specific date. I need to produce a balance query showing balance on each date, including where currently no date exists. Query below works for all dates simply carrying balance forward from when balance last entered.

SELECT AgreementID,
SUM(CASE WHEN fb.AccountTypeBaseCode = '1010' AND fb.AccountTypeStandardCode = '0100' THEN COALESCE(BalanceSAC,0) END) AS SalesLedgerOpenSAC,
SUM(CASE WHEN fb.AccountTypeBaseCode = '3020' AND fb.AccountTypeStandardCode = '0300' THEN COALESCE(BalanceSAC,0) END) AS CurrentAccountOpenSAC,
SUM(CASE WHEN fb.AccountTypeBaseCode = '3010' AND fb.AccountTypeStandardCode = '0300' THEN COALESCE(BalanceSAC,0) END) AS ClientOpeningBalTodaySAC
FROM F_Balance fb
WHERE BalanceStartDateID < @OpeningAccountDateIDVar AND BalanceEndDateID >= @OpeningAccountDateIDVar
AND ( (fb.AccountTypeBaseCode = '1010' AND fb.AccountTypeStandardCode = '0100')
OR (fb.AccountTypeBaseCode = '3020' AND fb.AccountTypeStandardCode = '0300')
OR (fb.AccountTypeBaseCode = '3010' AND fb.AccountTypeStandardCode = '0300') )
GROUP BY AgreementID

I have a date table which links on DateId (D_DatePeriod) and holds all dates.
Fields of interest are Id and ActualDate

I could achieve result I want by unioning data 31 times for each date in a potential month and then filtering using the Dateperiod table
Is there an easier more efficient way by some how cross joining on my date table

Thank you
Ian

 

[IanWaterman]

Made some progress with code below, appears to run but does not stop. It appears to ignore the date range restriction and keeps run, as a result the final select Statement does not run as temp table just keeps growing.
The two queries to populate date range work fine, and the cursor query produces a list of 31 IDs corresponding to July 2017.
Any suggestions as to where I am going wrong

Thank you
Ian

DECLARE @ServiceCompanyID BIGINT = 267
DECLARE @AccountingDate DATETIME = '2017-08-01'
DECLARE @OpeningDate DATETIME
DECLARE @ClosingDate DATETIME
DECLARE @OpeningAccountDateIDVar BIGINT
DECLARE @ClosingAccountDateIDVar BIGINT
DECLARE @ClosingAccountDateID BIGINT

DECLARE @ActualDateIDALL BIGINT

DECLARE @SQLVersion NVARCHAR(6)

SET @OpeningDate =  DATEADD(mm, DATEDIFF(mm,0,@AccountingDate-1), 0)
SET @OpeningAccountDateIDVar = (SELECT ID FROM dbo.D_DatePeriod WHERE ActualDate = @OpeningDate)
--select @OpeningDate

SET @ClosingDate = @AccountingDate-1
SET @ClosingAccountDateID = (SELECT ID FROM dbo.D_DatePeriod WHERE ActualDate = @ClosingDate)
--select @ClosingDate

;
DECLARE @Balances TABLE (
						AgreementID INT,
						BalanceDateID BIGINT,
						SalesLedgerOpenSAC DECIMAL(19,2),
						CurrentAccountOpenSAC DECIMAL(19,2),
						ClientClosingBalTodaySAC DECIMAL(19,2)
						)
DECLARE c CURSOR FOR
	SELECT ID FROM D_DatePeriod
	WHERE ID >= @OpeningAccountDateIDVar
	and ID <= @ClosingAccountDateID
	ORDER BY ID
OPEN c
FETCH NEXT FROM c INTO @ActualDateIDALL
WHILE @@FETCH_STATUS = 0
BEGIN
DELETE FROM @Balances
INSERT INTO @Balances
	SELECT AgreementID, @ActualDateIDALL  BalanceDateID,
					SUM(CASE WHEN fb.AccountTypeBaseCode	= '1010' AND fb.AccountTypeStandardCode	= '0100' THEN COALESCE(BalanceSAC,0) END) AS SalesLedgerOpenSAC,
					SUM(CASE WHEN fb.AccountTypeBaseCode	= '3020' AND fb.AccountTypeStandardCode	= '0300' THEN COALESCE(BalanceSAC,0) END) AS CurrentAccountOpenSAC,
					SUM(CASE WHEN fb.AccountTypeBaseCode	= '3010' AND fb.AccountTypeStandardCode	= '0300' THEN COALESCE(BalanceSAC,0) END) AS ClientOpeningBalTodaySAC
			FROM F_Balance fb
			INNER JOIN D_AgreementDetails AD on FB.AgreementID = AD.ID and AD.ObjectClass = 'ServiceAgreement' and AD.ServiceCompanyId =@ServiceCompanyID and AD.ID = 787
--			WHERE BalanceStartDateID < @OpeningAccountDateIDVar AND BalanceEndDateID >= @OpeningAccountDateIDVar
			WHERE BalanceStartDateID <= @ActualDateIDALL AND BalanceEndDateID > @ActualDateIDALL
					AND (  (fb.AccountTypeBaseCode	= '1010' AND fb.AccountTypeStandardCode	= '0100') 
						OR (fb.AccountTypeBaseCode	= '3020' AND fb.AccountTypeStandardCode	= '0300')
						OR (fb.AccountTypeBaseCode	= '3010' AND fb.AccountTypeStandardCode	= '0300')  
						)
			GROUP BY AgreementID;
	END;

	SELECT * FROM @Balances

 

[IanWaterman]

Finally cracked problem

DECLARE @ServiceCompanyID BIGINT = 267
DECLARE @AccountingDate DATETIME = '2017-08-01'
DECLARE @OpeningDate DATETIME
DECLARE @ClosingDate DATETIME
DECLARE @OpeningAccountDateIDVar BIGINT
DECLARE @ClosingAccountDateIDVar BIGINT
DECLARE @ClosingAccountDateID BIGINT

DECLARE @ActualDateIDALL BIGINT

DECLARE @SQLVersion NVARCHAR(6)

SET @OpeningDate =  DATEADD(mm, DATEDIFF(mm,0,@AccountingDate-1), 0)
SET @OpeningAccountDateIDVar = (SELECT ID FROM dbo.D_DatePeriod WHERE ActualDate = @OpeningDate)
--select @OpeningDate

SET @ClosingDate = @AccountingDate-1
SET @ClosingAccountDateID = (SELECT ID FROM dbo.D_DatePeriod WHERE ActualDate = @ClosingDate)
--select @ClosingDate

;
DECLARE @Balances TABLE (
                                         AgreementID INT,
                                         BalanceDateID BIGINT,
                                         SalesLedgerOpenSAC DECIMAL(19,2),
                                         CurrentAccountOpenSAC DECIMAL(19,2),
                                         ClientClosingBalTodaySAC DECIMAL(19,2)
                                         )
DECLARE c CURSOR FOR
       SELECT ID FROM D_DatePeriod
       WHERE ID >= @OpeningAccountDateIDVar
       and ID <= @ClosingAccountDateID
       ORDER BY ID
OPEN c
FETCH NEXT FROM c INTO @ActualDateIDALL
WHILE @@FETCH_STATUS = 0
BEGIN

       INSERT INTO @Balances
       SELECT AgreementID, @ActualDateIDALL  BalanceDateID,
                                  SUM(CASE WHEN fb.AccountTypeBaseCode     = '1010' AND fb.AccountTypeStandardCode = '0100' THEN COALESCE(BalanceSAC,0) END) AS SalesLedgerOpenSAC,
                                  SUM(CASE WHEN fb.AccountTypeBaseCode     = '3020' AND fb.AccountTypeStandardCode = '0300' THEN COALESCE(BalanceSAC,0) END) AS CurrentAccountOpenSAC,
                                  SUM(CASE WHEN fb.AccountTypeBaseCode     = '3010' AND fb.AccountTypeStandardCode = '0300' THEN COALESCE(BalanceSAC,0) END) AS ClientOpeningBalTodaySAC
                     FROM F_Balance fb
                     INNER JOIN D_AgreementDetails AD on FB.AgreementID = AD.ID and AD.ObjectClass = 'ServiceAgreement' and AD.ServiceCompanyId =@ServiceCompanyID and AD.ID = 787
--                   WHERE BalanceStartDateID < @OpeningAccountDateIDVar AND BalanceEndDateID >= @OpeningAccountDateIDVar
                     WHERE BalanceStartDateID <= @ActualDateIDALL AND BalanceEndDateID > @ActualDateIDALL
                                  AND (  (fb.AccountTypeBaseCode    = '1010' AND fb.AccountTypeStandardCode = '0100') 
                                         OR (fb.AccountTypeBaseCode = '3020' AND fb.AccountTypeStandardCode = '0300')
                                         OR (fb.AccountTypeBaseCode = '3010' AND fb.AccountTypeStandardCode = '0300')  
                                         )
                     GROUP BY AgreementID;
       FETCH NEXT FROM c INTO @ActualDateIDALL
       
       END;
       DEALLOCATE c
       SELECT * FROM @Balances