Filling in blanks

Question

Filling in blanks

nycdotnet

Right there with Babe

Points: 799
More actions
September 17, 2008 at 11:48 am

#193022

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

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 1

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

nycdotnet Right there with Babe Points: 799 More actions · Answer 2

Thanks for the tip. That should help quite a bit. I'll try to close the feedback loop when I get the solution.

nycdotnet Right there with Babe Points: 799 More actions · Answer 3

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

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 4

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

See Tally tables here[/url].

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

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

nycdotnet Right there with Babe Points: 799 More actions · Answer 5

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!!!

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 6

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.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

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

nycdotnet Right there with Babe Points: 799 More actions · Answer 7

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:

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 8

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 🙂

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

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

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 9

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

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

nycdotnet Right there with Babe Points: 799 More actions · Answer 10

Thanks, Jeff. I was wondering what I was going to have for dinner tonight.

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 11

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

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)