DynamicDateRangeGenerator

Question

DynamicDateRangeGenerator

mitch 14804

SSC Veteran

Points: 298
More actions
August 4, 2016 at 3:44 pm

#315952

Comments posted to this topic are about the item DynamicDateRangeGenerator

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

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

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

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

mitch 14804 SSC Veteran Points: 298 More actions · Answer 2

@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

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

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

Alan Burstein SSC Guru Points: 61141 More actions · Answer 4

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

quentin.harris SSC Eights! Points: 954 More actions · Answer 5

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

quentin.harris SSC Eights! Points: 954 More actions · Answer 6

Hi,