September 18, 2014 at 1:49 pm
Hello,
I need to find all of the weekdays between a date range. For example, if I pass in 09/18/2012 and 09/18/2014 I need all weekdays returned between that date range, not a count, but just the dates. I need to dump those dates into a temp table and the loop through them.
I am using SQL 2005, unfortunately.
Any help would be appreciated
Everyone has a plan until they get punched in the mouth. --Mike Tyson
September 18, 2014 at 2:17 pm
Something like this could do the trick and you can convert it into an inline table valued function which would be as fast as the normal query.
DECLARE @Start date = '20120918',
@End date = '20140918';
WITH E1(N) AS (
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
), --10E+1 or 10 rows
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 (--==== This provides the "base" CTE and limits the number of rows right up front
-- for both a performance gain and prevention of accidental "overruns"
SELECT TOP (DATEDIFF(DD, @Start, @End) + 1)
DATEADD( DD, ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) - 1, @Start)
FROM E4
)
SELECT N
FROM cteTally l
WHERE DATENAME(DW, N) NOT IN( 'Saturday', 'Sunday');
September 18, 2014 at 2:30 pm
Nice Luis! Fast as hell too!
Much appreciated 😀
Everyone has a plan until they get punched in the mouth. --Mike Tyson
September 18, 2014 at 2:37 pm
It's easy when you have the code to generate the Tally Table available.
Do you know what is a tally table? How is it built here?
To know more about it, read the following articles:
The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url]
And an applied example: http://www.sqlservercentral.com/articles/Tally+Table/72993/
September 18, 2014 at 4:20 pm
I recommend avoiding unnecessary string conversions/usage, and dependencies on language and/or other SQL settings.
Not sure what the h "N" means, but I still suggest this style of code instead:
WHERE DATEDIFF(DAY, 0, N) % 7 IN (5, 6)
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
September 18, 2014 at 4:30 pm
N stands for "Nothing in particular" 😀
I support your code if it's accompanied by the proper comments describing what it does. I'm not trying to be pedantic, this is more of a suggestion for anyone that uses it on production.
September 18, 2014 at 4:40 pm
Luis Cazares (9/18/2014)
N stands for "Nothing in particular" 😀I support your code if it's accompanied by the proper comments describing what it does. I'm not trying to be pedantic, this is more of a suggestion for anyone that uses it on production.
Good point on comments, since I'm used to that and others are not, although presumably knowing that the code was written to exclude weekends would help decipher the code.
Still, either of these should do then:
WHERE DATEDIFF(DAY, 0, N) % 7 IN (5, 6) --5=Saturday;6=Sunday.
-- OR
FROM ...
CROSS JOIN (
SELECT 5 AS Saturday, 6 AS Sunday
) AS dayofweek_values_for_mod_calc
WHERE DATEDIFF(DAY, 0, N) % 7 IN (Saturday, Sunday)
[DATEDIFF, % and date 0 are all explained in Books Online, natch.]
Btw, why not "day_number" instead of the lazy "N"? I prefer code to be self-documenting where possible. [Thus, for production code, I would generally prefer either named variables (@Saturday = 5, @Sunday = 6) or the CROSS JOIN, although for something this straightforward I could live with the comment.]
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
September 18, 2014 at 4:47 pm
The "Lazy N" was really a mistake on my part. I was going to change it into something descriptive, but I forgot to do it before copying the code.:blush:
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply