Generate Daily Balance From Transaction Table

[databanker]

I developed a query that creates a running balance from a transaction table. My issue is that I would like to establish an end of day balance for all 7 days of the week even if there are not transactions on a particular day.

What is the best strategy to go from a running balance to an end of day balance?

I am using SQL Server 2000 so I cannot tap all the cool stuff in 05.

Thanks!

 

[ESquared]

You have to get a list of days and left join to them, so that the sum for missing days comes out to 0.

Standard technique is to create a Numbers table (Num int identity(1,1) PRIMARY KEY CLUSTERED) with enough rows in it to cover every eventuality you need. Use this to create the days of the range you want. Then you're set.

In order to answer you more specifically, some sample data is in order.

[COLOR=black #d0d0d0]When I walk, I sometimes bump into things. I am closing my eyes so that the room will be empty.[/color]

 

[databanker]

This is the data I have:

Account Date Balance
1 1/1/2007 9
1 1/6/2007 17
1 1/11/2007 25
1 1/16/2007 0
1 1/21/2007 8
1 1/26/2007 16
1 1/31/2007 24


This is what I need:
Account Date Balance
1 1/1/2007 9
1 1/2/2007 9
1 1/3/2007 9
1 1/4/2007 9
1 1/5/2007 9
1 1/6/2007 17
1 1/7/2007 17
1 1/8/2007 17
1 1/9/2007 17
1 1/10/2007 17
1 1/11/2007 25
1 1/12/2007 25
1 1/13/2007 25
1 1/14/2007 25
1 1/15/2007 25
1 1/16/2007 0
1 1/17/2007 0
1 1/18/2007 0
1 1/19/2007 0
1 1/20/2007 0
1 1/21/2007 8
1 1/22/2007 8
1 1/23/2007 8
1 1/24/2007 8
1 1/25/2007 8
1 1/26/2007 16
1 1/27/2007 16
1 1/28/2007 16
1 1/29/2007 16
1 1/30/2007 16
1 1/31/2007 24


The left join on a date table works but the trick is that days w/o transactions would not be zero - they would be the balance forward from the last transaction date.

 

[rac2]

The only way to get a daily balance on days without transactions is through the use of a "calendar" table which has a row for every day that might have a transaction. This is joined to the transaction table, or better, to the summary table, with an outer join. The amount is obtained with an expression using ISNULL, COALESCE, or CASE to show 0.00 for those days which have no transactions.


Suppose you have a reporting table or a view with the date and the end-of-day balance. Call it DailyBalances. And a calendar table with the business dates, call it BusinessDates.

SELECT bd.date_of_business,
              COALESCE(dbal.amount_end_of_day, 0) AS "AmountEndOfDay"
FROM BusinessDates bd
LEFT JOIN DailyBalances dbal ON dbal.date_of_business = bd.date_of_business

You must prefill BusinessDates with the dates you wish to see in the results. This might be an annual task inserting rows for the following year, or you might have a stored procedure that brings it up-to-date whenever the reporting query is run, or insert a row daily.

DailyBalances would be prepared once a day after the close of business if it is a reporting table. Or it could be a view, in which case the whole set of daily balances in the report would be calculated every time the report is run.

Or you could simply add a row to a DailyBalances reporting table every day, sometimes the amount would be zero or the same as the previous day, but there would always be a row since you would perform the task daily. In this case there would not be any need for a calendar table.

The daily tasks would be scheduled jobs for SQL Server Agent.

 

[databanker]

rac2

"The amount is obtained with an expression using ISNULL, COALESCE, or CASE to show 0.00 for those days which have no transactions."

Days w/o transactions are not necessarily 0.00 -> they are the current balance. Example: If you did not have a transaction on your bank account in a particular day that would not mean that your account balance is zero.

 

[rluciano]

Hey databanker,

Are you going to store this data in a table like DAILY_BALANCES or just run the query everyday to report on it?

How about this, create a DAILY_BALANCES table with all the fields from the sproc. Use the query you wrote, make a view from it and insert the data into the DAILY_BALANCES table.

INSERT INTO DAILY_BALANCES (ACCOUNT, DATE, BALANCE)
SELECT ACCOUNT,
GETDATE(),
CASE WHEN BALANCE IS NULL THEN 0 END AS BALANCE
FROM CURRENT_BALANCE_VIEW

Using the case statement will take care of the situation where the account has no transactions posted yet. Once an account has a balance and nothing else changes it wil always be that balance.

 

[rluciano]

I reread your original post and in doing the insert your view should show the current balance and current date for every account, not just for one account. querying the DAILY_BALANCES table ordered by account then date should give you the results you want.

 

[ESquared]

CREATE TABLE Numbers (Num int identity(1,1) PRIMARY KEY CLUSTERED)
INSERT Numbers DEFAULT VALUES
WHILE Scope_Identity() < 1000 INSERT Numbers DEFAULT VALUES

SELECT
   A.AccountID,
   BalDate = IsNull(D.AllDate, T.TranDate),
   T.Balance
INTO
   #Temp
FROM
   Accounts A
   CROSS JOIN (
      SELECT AllDate = DateDiff(dd, 0, GetDate()) - Num + 1
      FROM Numbers N
      WHERE Num <= DateDiff(dd, '1/1/2007', GetDate()) + 1
        -- 1/1/2007 = first date to begin inserting numbers
   ) D
   FULL JOIN Transactions T ON T.TranDate = D.AllDate AND T.AccountID = A.AccountID

WHILE @@RowCount > 0
   UPDATE T1
   FROM
      #Temp T1
      INNER JOIN #Temp T2 ON T1.AccountID = T2.AccountID AND T1.BalDate = T2.BalDate + 1
   WHERE
      T1.Balance IS NULL
      AND T2.Balance IS NOT NULL

SELECT * FROM #Temp

DROP TABLE #Temp

Note that you'll have to do special handling for balances before the first date for each AccountID in the Transactions for your date range... perhaps a final update to set balances, where still null (which should only be these items), to the value on the maximum date before the date range (0 if nonexistent).

You might be able to cook something up with Min and Max stuff that's more efficient than one update per count of maximum gap between transactions, but I can't spend the time on this now.

[COLOR=black #d0d0d0]When I walk, I sometimes bump into things. I am closing my eyes so that the room will be empty.[/color]