Filling in blanks

  • 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

  • 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

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

  • 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

  • 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

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

  • 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

  • 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:

  • 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

  • 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)

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

  • 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)

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

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