Post reply

Create a daily record from date ranges

  • March 4, 2009 at 11:30 pm

    #953804

    Heh... must be budget planning time again. This should do it, John.... I'm using Master.dbo.spt_Values as if it were a Tally table...

    DECLARE @Description VARCHAR(20),

    @Value MONEY,

    @From DATETIME,

    @To DATETIME

    SELECT @Description = 'Spend 1',

    @Value = CONVERT(MONEY,'1000'),

    @From = '01/01/2008',

    @To = '10/01/2008'

    SELECT DATEADD(mm,t.Number,@From) AS [Date],

    @Description AS [Description],

    @Value/(DATEDIFF(mm,@From,@To)+1) AS [Value]

    FROM Master.dbo.spt_Values t

    WHERE t.Type = 'P'

    AND t.Number BETWEEN 0 AND DATEDIFF(mm,@From,@To)

    For more info on what a Tally table is and how it's used to replace many While Loops, please see the following article...

    http://www.sqlservercentral.com/articles/TSQL/62867/

    Oh yeah... almost forgot... the spt_Values table only has "P" values from 0 to 255. If you need more months than than, then you will need a Tally table and may need to make some minor mods to the code above to handle a Tally table that starts at 1. Of course, if you start it at 0, all you'll need to do is change the FROM clause and the word NUMBER to just N.

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

  • March 5, 2009 at 1:53 pm

    #954378

    Thanks for that Jeff - all working perfectly!

  • March 6, 2009 at 8:42 pm

    #955386

    ...and thank you for the feedback! 🙂

    --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 3 posts - 1 through 4 (of 4 total)

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