September 17, 2008 at 11:48 am
Hello,
I'm trying to write a report to show end of day bank balances over a period, but the table only has data for days when there is a transaction. Does anyone have an idea on how to fill in the data for the blank days? I don't want to change the table - I would just like to generate this data for the purposes of this report and possibly others.
For example my database shows this:
TranDate,TransactionAmount,ClosingBalance
9/1/2008,+100,100
9/4/2008,-60,40
9/6/2008,+10,50
But I want to return this in a result set:
TranDate,TransactionAmount,ClosingBalance
9/1/2008,+100,100
9/2/2008,0,100
9/3/2008,0,100
9/4/2008,-60,40
9/5/2008,0,40
9/6/2008,+10,50
September 17, 2008 at 12:15 pm
Look on this site for Tally table and/or Calendar table. Either of those articles will show you how this can be accomplished.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
September 17, 2008 at 12:23 pm
Thanks for the tip. That should help quite a bit. I'll try to close the feedback loop when I get the solution.
December 15, 2008 at 7:22 am
Hi All,
I solved this problem a while ago with help from the group. I work with a relatively small data set for now, so this has worked perfectly fine for me, but I'm sure the way I'm filling in the blanks (with a subselect inside a coalesce()) could be improved. Do any of the SQL ninjas have a better strategy that will work with SQL 2000?
select t.[Date] as 'TranDate',
COALESCE(b.[TransactionAmount],0) as 'TransactionAmount',
COALESCE(b.[Balance], (select top 1 [Balance] from #myBalances b1 where
b1.[TranDate] < t.[Date] order by b1.[TranDate] desc)) as 'Balance'
from dbo.MyCalendarTally('12/1/2008','12/5/2008') t
LEFT OUTER JOIN #myBalances b on t.[Date] = b.[TranDate]
Order By t.[Date]
By the way, here's the code for my calendar tally and a sample data set.
create table #myBalances (
[TranDate] datetime,
[TransactionAmount] money,
[Balance] money
)
insert into #myBalances VALUES ('12/1/2008',100,100)
insert into #myBalances VALUES ('12/3/2008',-50,50)
insert into #myBalances VALUES ('12/4/2008',25,75)
CREATE FUNCTION [dbo].[MyCalendarTally] ( @StartDate datetime,@EndDate datetime)
RETURNS
@Calendar table
( [Date] datetime ) AS
BEGIN
while @StartDate <= @EndDate --loop
begin
insert into @Calendar ([Date]) VALUES (@StartDate) -- insert a date
set @StartDate = DATEADD(dd, 1, @StartDate) --add 1 day
end
return --return the table
END
December 15, 2008 at 8:58 am
If you have a tally table, then this works a treat:
[font="Courier New"]SET DATEFORMAT MDY
DROP TABLE #myBalances
CREATE TABLE #myBalances (
[TranDate] DATETIME,
[TransactionAmount] money,
[Balance] money)
INSERT INTO #myBalances VALUES ('12/1/2008',100,100)
INSERT INTO #myBalances VALUES ('12/3/2008',-60,40)
INSERT INTO #myBalances VALUES ('12/4/2008',10,50)
DECLARE @StartDate DATETIME, @EndDate DATETIME
SET @StartDate = '12/1/2008'
SET @EndDate = '12/6/2008'
SELECT DATEADD(DD, 0, n.number) AS TranDate, ISNULL(b.TransactionAmount, 0) AS TransactionAmount, x.Balance
FROM Numbers n
LEFT JOIN #myBalances b ON b.TranDate = DATEADD(DD, 0, n.number)
LEFT JOIN #myBalances x ON x.TranDate = (SELECT MAX(TranDate) AS TranDate FROM #myBalances WHERE TranDate <= DATEADD(DD, 0, n.number))
WHERE number BETWEEN DATEDIFF(DD, 0, @StartDate) AND DATEDIFF(DD, 0, @EndDate)
[/font]
Cheers
ChrisM
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
December 15, 2008 at 9:08 am
Thanks. The Select Max() in the join was the magic I was looking for.
I do actually have a tally table, but it's a calendar tally. I modified your example to work with my calendar tally which is implemented as a parameterized table-value function (code is posted above).
SELECT n.[Date] AS TranDate, ISNULL(b.TransactionAmount, 0) AS TransactionAmount, x.Balance
FROM dbo.MyCalendarTally('12/1/2008','12/30/2008') n
LEFT JOIN #myBalances b ON b.TranDate = n.[Date]
LEFT JOIN #myBalances x ON x.TranDate = (SELECT MAX(TranDate) AS TranDate FROM #myBalances WHERE TranDate <= n.[Date])
Much appreciated, Chris!!!
December 15, 2008 at 9:28 am
You're welcome. I ran a quick test of both methods with a date range of nearly 40,000 days
DECLARE @StartDate DATETIME, @EndDate DATETIME
SET @StartDate = '12/1/1900'
SET @EndDate = '12/5/2009'
and both methods ran in about a second, not much to choose between them - at least, with a very small balances table.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
December 15, 2008 at 9:34 am
At some point we're going to have to evaluate having a "permanent" tally table in the DB. For now, it's only used in reporting and since it can generate a great deal of days so quickly, I'm not super concerned about it if my weekly report takes an extra 0.1 seconds to run.
Thanks again, though. The two separate joins from the tally table to the table with the data will help me greatly in other places where I'm taking in more than one field from the table with "missing" days. I was 90% there when I solved that problem - but I wound up cheating and going to a temp table rather than just doing it in one query. :Whistling:
December 15, 2008 at 9:47 am
sognibene (12/15/2008)
At some point we're going to have to evaluate having a "permanent" tally table in the DB. For now, it's only used in reporting and since it can generate a great deal of days so quickly, I'm not super concerned about it if my weekly report takes an extra 0.1 seconds to run.Thanks again, though. The two separate joins from the tally table to the table with the data will help me greatly in other places where I'm taking in more than one field from the table with "missing" days. I was 90% there when I solved that problem - but I wound up cheating and going to a temp table rather than just doing it in one query. :Whistling:
It runs surprisingly quickly...but...keep an eye out for low-flying pork chops 🙂
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
December 15, 2008 at 2:26 pm
Chris Morris (12/15/2008)
sognibene (12/15/2008)
At some point we're going to have to evaluate having a "permanent" tally table in the DB. For now, it's only used in reporting and since it can generate a great deal of days so quickly, I'm not super concerned about it if my weekly report takes an extra 0.1 seconds to run.Thanks again, though. The two separate joins from the tally table to the table with the data will help me greatly in other places where I'm taking in more than one field from the table with "missing" days. I was 90% there when I solved that problem - but I wound up cheating and going to a temp table rather than just doing it in one query. :Whistling:
It runs surprisingly quickly...but...keep an eye out for low-flying pork chops 🙂
Heh...Yeaup! And here they come!
Writing a WHILE loop to do anything one row at a time, no matter how fast you think it is, is absolutely possitively the wrong thing to do. Think of it like you would practicing the piano... you wouldn't intentionally hit the wrong notes there! Why are you intentionally writing the wrong code that some poor fool may copy as an example for a much bigger application?
If you don't actually want to or aren't allow to build a Tally table, then incorporate the high speed methods of making one on the fly into your function. One of the fastest methods, for under 16 million rows is contained, in the following article...
[font="Arial Black"]The "Numbers" or "Tally" Table: What it is and how it replaces a loop.[/font][/url]
--Jeff Moden
Change is inevitable... Change for the better is not.
December 15, 2008 at 2:39 pm
Thanks, Jeff. I was wondering what I was going to have for dinner tonight.
December 15, 2008 at 6:20 pm
sognibene (12/15/2008)
Thanks, Jeff. I was wondering what I was going to have for dinner tonight.
Heh... mmmmmm... p-o-r-k c-h-o-p-s... gaaaahhhh... 😛
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply