DynamicDateRangeGenerator

  • Comments posted to this topic are about the item DynamicDateRangeGenerator

  • Hi Mitch, thanks for posting. It looks a little over-engineered for what it's supposed to be doing. This does the same much faster and with less code:

    DECLARE @ExtraYearsEitherSide TINYINT = 7

    SELECT TOP (@ExtraYearsEitherSide*2+1)

    RangeYear = YEAR(GETDATE()) + ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) - (@ExtraYearsEitherSide+1)

    FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) rc (n) -- Good for up to 7 years either side

    Unless I'm missing something, like coffee 😉

    “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

  • @ChrisM@Work

    Hi Chris,

    Thanks for the feedback. The reasoning behind my long process is so I could put it in a view. Your script looks like it would work just fine also.. Thanks for sharing.

    mitch

  • mitch 14804 (8/11/2016)


    @ChrisM@Work

    Hi Chris,

    Thanks for the feedback. The reasoning behind my long process is so I could put it in a view not a procedure. It now allows me to connect to other tables by join.

    mitch

    -- This represents your table containing date values & the number of years you want either side

    ;WITH ParentTable AS (SELECT * FROM (VALUES

    (CAST('20150101' AS DATE),1),

    ('20160101',2),

    ('20170101',3)) d ([Year], ExtraYearsEitherSide))

    -- this is how to use the code snippet in a query with the table source above.

    -- You could convert it into an iTVF and pass in ExtraYearsEitherSide as a parameter

    -- to create a parameterised view, very user-friendly

    SELECT p.*, x.*

    FROM ParentTable p

    CROSS APPLY (

    SELECT TOP (p.ExtraYearsEitherSide*2+1)

    RangeYear = YEAR(p.[Year]) + ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) - (p.ExtraYearsEitherSide+1)

    FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) rc (n)

    ) x

    “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

  • ... and if we're talking about always doing 3 years behind/ahead of the current year you could even do this:

    WITH currentYear AS (SELECT yr = YEAR(getdate()))

    SELECT yr = yr + N

    FROM currentYear, (VALUES (-3),(-2),(-1),(0),(1),(2),(3)) t(N)

    "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

    -- Itzik Ben-Gan 2001

  • Hi,

    Can this be used, as implied by original post to deal with the following:

    Select

    sum(case when dd.BK_DepartmentTypeCode = '01' and dates.CalendarYearNumber = '2016' then 1 else 0 end) [2016Type1Att],

    sum(case when dates.CalendarYearNumber = '2016' then 1 else 0 end) [2016AllTypeAtt]

    from sometable

    Two years are always present; this financial year (Running 01-04-yyyy to 31-03-yyyy) and prior financial year.

    It would great to not have hard coded values in for the year number.

    Many thanks,

    Q

  • Hi,

    Can this be used, as implied by original post to deal with the following:

    Select

    sum(case when dd.BK_DepartmentTypeCode = '01' and dates.CalendarYearNumber = '2016' then 1 else 0 end) [2016Type1Att],

    sum(case when dates.CalendarYearNumber = '2016' then 1 else 0 end) [2016AllTypeAtt]

    from sometable

    Two years are always present; this financial year (Running 01-04-yyyy to 31-03-yyyy) and prior financial year.

    It would great to not have hard coded values in for the year number.

    Many thanks,

    Q

Viewing 7 posts - 1 through 6 (of 6 total)

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