Generating Dates at Runtime

  • I am trying to generate dates for a roster dynamically in a stored procedure. If my start date is '01/01/2015', end date is '12/31/2015', I should generate dates from '01/01/2015' adding 7 days.

    The output comes like this:

    01/01/2015

    01/08/2015

    01/15/2015

    .

    .

    .

    until end date

    I do it through loop but just posting it here to know, if there is any other efficient way using CTEs or some sort of queries which can avoid loop in SP.

    Thanks

  • sarath.tata (5/29/2015)


    I am trying to generate dates for a roster dynamically in a stored procedure. If my start date is '01/01/2015', end date is '12/31/2015', I should generate dates from '01/01/2015' adding 7 days.

    The output comes like this:

    01/01/2015

    01/08/2015

    01/15/2015

    .

    .

    .

    until end date

    I do it through loop but just posting it here to know, if there is any other efficient way using CTEs or some sort of queries which can avoid loop in SP.

    Thanks

    Kudos for realizing a loop is inefficient and wanting to find a better way!!! Here is an example of using a tally table for this.

    declare @StartDate date = '2015-01-01', @EndDate date = '2015-12-31';

    WITH

    E1(N) AS (select 1 from (values (1),(1),(1),(1),(1),(1),(1),(1),(1),(1))dt(n)),

    E2(N) AS (SELECT 1 FROM E1 a, E1 b), --10E+2 or 100 rows

    E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10E+4 or 10,000 rows max

    cteTally(N) AS

    (

    SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4

    )

    select *

    from cteTally

    where N <= DATEDIFF(DAY, @StartDate, @EndDate) + 1

    AND (N - 1) % 7 = 0

    For more details about this technique check out this article from Jeff Moden. http://www.sqlservercentral.com/articles/T-SQL/62867/[/url]

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • The following will easily give you what you want, without resorting to a loop. It basically just applies row_number() across a resultset to get a sequential set of numbers, multiplying by 7, and adding that to a base date. There is nothing special about the master.sys.sysobjects table; anything containing sufficient rows and is guaranteed to exist will do. The WHERE clause can be used to limit the result by date range, exclude specific dates like holidays, etc.

    select * from

    (

    select dateadd(day

    ,(row_number() over (order by id)-1)*7

    ,cast('2015/01/01' as date))RosterDate

    from master.sys.sysobjects

    ) as x

    where RosterDate <= '2015/12/31';

    RosterDate

    ----------

    2015-01-01

    2015-01-08

    2015-01-15

    ...

    2015-12-17

    2015-12-24

    2015-12-31

    (53 row(s) affected)

    "Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

  • I have found a solution. Not so sure, if it is the best solution!!

    DECLARE @StartDate DATE = '01/01/2015'

    , @EndDate DATE = '01/08/2015'

    SET @StartDate = DATEADD(DAY, -7, @StartDate)

    ;WITH result(tempDate) AS

    (

    SELECT DATEADD(DAY, nbr, @StartDate)

    FROM ( SELECT 7 * ROW_NUMBER() OVER ( ORDER BY c.object_id ) AS Nbr

    FROM sys.all_objects c

    ) nbrs

    WHERE nbr - 1 <= DATEDIFF(DAY, @StartDate, @EndDate)

    )

    SELECT * FROM result r

    LEFT OUTER JOIN [tblDutyRosterAvailability] d ON r.tempDate >= @StartDate

    ORDER BY r.tempDate, d.DutyRosterCategoryID, d.SortOrder

  • Eric M Russell (5/29/2015)


    The following will easily give you what you want, without resorting to a loop. It basically just applies row_number() across a resultset to get a sequential set of numbers, multiplying by 7, and adding that to a base date. There is nothing special about the master.sys.sysobjects table; anything containing sufficient rows and is guaranteed to exist will do. The WHERE clause can be used to limit the result by date range, exclude specific dates like holidays, etc.

    select * from

    (

    select dateadd(day

    ,(row_number() over (order by id)-1)*7

    ,cast('2015/01/01' as date))RosterDate

    from master.sys.sysobjects

    ) as x

    where RosterDate <= '2015/12/31';

    RosterDate

    ----------

    2015-01-01

    2015-01-08

    2015-01-15

    ...

    2015-12-17

    2015-12-24

    2015-12-31

    (53 row(s) affected)

    Thanks, I like this!! Looks better than mine!! This is what my final query

    ;WITH result(RosterDate) AS

    (

    SELECT DATEADD(DAY, (ROW_NUMBER() OVER (ORDER BY object_id)) * 7, @StartDate)

    FROM sys.all_objects

    )

    SELECT * FROM result r

    LEFT OUTER JOIN [tblDutyRosterAvailability] d ON r.RosterDate >= @StartDate

    WHERE RosterDate <= @EndDate

    ORDER BY r.RosterDate, d.DutyRosterCategoryID, d.SortOrder

  • On my system I have the cteTally as a view so I can generate up to 10,000 rows with zero reads. It is crazy fast and super flexible.

    The issue with my original post is I forgot the last step. :blush:

    declare @StartDate date = '2015-01-01', @EndDate date = '2015-12-31';

    WITH

    E1(N) AS (select 1 from (values (1),(1),(1),(1),(1),(1),(1),(1),(1),(1))dt(n)),

    E2(N) AS (SELECT 1 FROM E1 a, E1 b), --10E+2 or 100 rows

    E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10E+4 or 10,000 rows max

    cteTally(N) AS

    (

    SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4

    )

    select DATEADD(DAY, N - 1, @StartDate)

    from cteTally

    where N <= DATEDIFF(DAY, @StartDate, @EndDate)

    AND (N - 1) % 7 = 0

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • Just in case somebody wants

    DECLARE @StartDate DATE

    DECLARE @EndDate DATE

    DECLARE @RotationDays INT

    SET @StartDate = '01/01/2015'

    SET @EndDate = '12/31/2015'

    SET @RotationDays = 4

    ;WITH result AS

    (

    SELECT DATEADD(DAY, (ROW_NUMBER() OVER (ORDER BY object_id) - 1) * @RotationDays, @StartDate) StartDate,

    DATEADD(DAY, ((ROW_NUMBER() OVER (ORDER BY object_id) - 1) * @RotationDays) + @RotationDays - 1, @StartDate) EndDate

    FROM sys.all_objects

    )

    --INSERT INTO TABLE here, if you need to

    SELECT StartDate, EndDate

    FROM result

    --LEFT OUTER JOIN table, if you need to join people here

    WHERE EndDate <= @EndDate

  • sarath.tata (5/29/2015)


    Just in case somebody wants

    DECLARE @StartDate DATE

    DECLARE @EndDate DATE

    DECLARE @RotationDays INT

    SET @StartDate = '01/01/2015'

    SET @EndDate = '12/31/2015'

    SET @RotationDays = 4

    ;WITH result AS

    (

    SELECT DATEADD(DAY, (ROW_NUMBER() OVER (ORDER BY object_id) - 1) * @RotationDays, @StartDate) StartDate,

    DATEADD(DAY, ((ROW_NUMBER() OVER (ORDER BY object_id) - 1) * @RotationDays) + @RotationDays - 1, @StartDate) EndDate

    FROM sys.all_objects

    )

    --INSERT INTO TABLE here, if you need to

    SELECT StartDate, EndDate

    FROM result

    --LEFT OUTER JOIN table, if you need to join people here

    WHERE EndDate <= @EndDate

    That's actually going to be a bit slow and you do stand the chance of running out of rows because sys.all_objects isn't that big.

    The reason why it will be slow is because you're calculating dates for every row there is in sys.all_objects and you probably won't usually need all those rows.

    Here's a comparison against the code above and the code that limits the number of rows being generated by the CTE. Look at the actual execution plan and play with SET STATISTICS to see what I mean in the comments.

    DECLARE @StartDate DATE

    DECLARE @EndDate DATE

    DECLARE @RotationDays INT

    SET @StartDate = '01/01/2015'

    SET @EndDate = '12/31/2015'

    SET @RotationDays = 4

    --===== Original method generates more than 2000 dates (and 33 reads) rather than just the 91 that are needed.

    -- Also has an extra ROW_NUMBER() calculation that's just not needed.

    -- In theory, could also run out of "rows" from sys.all_objects.

    ;WITH result AS

    (

    SELECT DATEADD(DAY, (ROW_NUMBER() OVER (ORDER BY object_id) - 1) * @RotationDays, @StartDate) StartDate,

    DATEADD(DAY, ((ROW_NUMBER() OVER (ORDER BY object_id) - 1) * @RotationDays) + @RotationDays - 1, @StartDate) EndDate

    FROM sys.all_objects

    )

    --INSERT INTO TABLE here, if you need to

    SELECT StartDate, EndDate

    FROM result

    --LEFT OUTER JOIN table, if you need to join people here

    WHERE EndDate <= @EndDate

    ;

    ------------------------------------------------------------------------------------------------------

    --===== Faster becausse this only generates the 91 rows (and only 5 reads) that are needed

    -- and only does one ROW_NUMBER() calcualation.

    -- Since the cross join is guaranteed to gen over 16 MILLION rows when needed,

    -- there's virtually no chance of ever hitting the proverbial wall.

    -- Using a properly formed Tally cte would take the reads down to almost nothing.

    WITH

    cteStartDates AS

    (

    SELECT TOP ((DATEDIFF(dd,@StartDate,@EndDate)+1)/@RotationDays)

    StartDate = DATEADD(dd,(ROW_NUMBER()OVER(ORDER BY (SELECT NULL))-1)*@RotationDays,@StartDate)

    FROM sys.all_columns ac1

    CROSS JOIN sys.all_columns ac2

    )

    SELECT StartDate

    ,EndDate = DATEADD(dd,@RotationDays-1,StartDate)

    FROM cteStartDates

    WHERE DATEADD(dd,@RotationDays-1,StartDate) <= @EndDate

    ;

    Does such a "small" increase in performance and decrease in resource usage actually make a difference? If you only run it once or twice a day, then not so as anyone would notice. If you run it 40,000 times a day, then the cumulative resource usage starts to be noticed, a lot. I always plan on the larger scale. Like Granny used to say, "Mind the pennies and the dollars will take care of themselves".

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

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