September 14, 2012 at 7:53 am
Hi, I need help in a stored procedure that, counts the number of Saturdays in a month, returns 12 rows, each row containing number of Saturdays for each month of the year using a single parameter as the specification for the year.
September 14, 2012 at 8:07 am
Like this:
DECLARE @StartDate DATE = '20120101';
WITH Seeds(Seed)
AS (SELECT *
FROM ( VALUES ( 1), ( 1), ( 1), ( 1), ( 1), ( 1), ( 1), ( 1), ( 1), ( 1), ( 1), ( 1), ( 1), ( 1),
( 1), ( 1), ( 1), ( 1), ( 1), ( 1) ) AS V (C)),
Numbers(Number)
AS (SELECT ROW_NUMBER() OVER (ORDER BY S1.Seed) - 1
FROM Seeds AS S1
CROSS JOIN Seeds AS S2),
Calendar(Date)
AS (SELECT DATEADD(DAY, Number, @StartDate)
FROM Numbers
WHERE DATEADD(DAY, Number, @StartDate) < DATEADD(YEAR, 1, @StartDate))
SELECT DATEPART(MONTH, Date),
COUNT(*)
FROM Calendar
WHERE DATEPART(weekday, Date) = 7
GROUP BY DATEPART(MONTH, Date)
ORDER BY DATEPART(MONTH, Date);
I use the Seed and Numbers CTEs to build a table of numbers from 0 - 400, then use the Calendar CTE to build a table of all dates in a year, from the Numbers CTE. It can work even better if you have a persisted Calendar table (those have a lot of good uses). Assuming you don't have one, this will work.
This solution depends on features from SQL 2008 and later. Based on the forum the question was posted in, that should be okay. If you're actually using a prior version of SQL Server (2005 or earlier), you'll need to change the Seeds CTE so that it uses Union All statements instead of a Table Value Constructor.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
September 14, 2012 at 9:29 am
It also depends on @@DATEFIRST = 7.
Otherwise you might be counting Sundays or another day.
September 14, 2012 at 10:18 am
Code below has fewer calcs and does not depend on any SQL date settings.
DECLARE @year int
SET @year = 2012 --<<-- chg as needed
SELECT
month_start,
DATEDIFF(DAY, '19000106', DATEADD(DAY, -1, DATEADD(MONTH, 1, month_start))) / 7 -
DATEDIFF(DAY, '19000106', DATEADD(DAY, -1, month_start)) / 7
FROM (
SELECT CAST(CAST(@year * 10000 + 0101 AS char(8)) AS datetime) AS month_start UNION ALL
SELECT CAST(@year * 10000 + 0201 AS char(8)) UNION ALL
SELECT CAST(@year * 10000 + 0301 AS char(8)) UNION ALL
SELECT CAST(@year * 10000 + 0401 AS char(8)) UNION ALL
SELECT CAST(@year * 10000 + 0501 AS char(8)) UNION ALL
SELECT CAST(@year * 10000 + 0601 AS char(8)) UNION ALL
SELECT CAST(@year * 10000 + 0701 AS char(8)) UNION ALL
SELECT CAST(@year * 10000 + 0801 AS char(8)) UNION ALL
SELECT CAST(@year * 10000 + 0901 AS char(8)) UNION ALL
SELECT CAST(@year * 10000 + 1001 AS char(8)) UNION ALL
SELECT CAST(@year * 10000 + 1101 AS char(8)) UNION ALL
SELECT CAST(@year * 10000 + 1201 AS char(8))
) AS months_of_the_year
ORDER BY
month_start
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
September 17, 2012 at 8:50 am
Thanks!
September 17, 2012 at 9:08 am
a bit shorter version...
DECLARE @year int
SET @year = 2015
SELECT
MONTH(mfd) AS MonthNo
,DATENAME(MONTH,mfd) AS MonthName
,DATEDIFF(DAY, '19000106', DATEADD(DAY, -1, DATEADD(MONTH, 1, mfd))) / 7 -
DATEDIFF(DAY, '19000106', DATEADD(DAY, -1, mfd)) / 7 AS NoOfSaturdays
FROM (
SELECT CAST(CAST(@year * 100 + m.m AS CHAR(6)) + '01' AS datetime) mfd
FROM (VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12)) m(m)
) q
ORDER BY q.mfd
September 17, 2012 at 9:28 am
ScottPletcher (9/14/2012)
Code below has fewer calcs and does not depend on any SQL date settings.
DECLARE @year int
SET @year = 2012 --<<-- chg as needed
SELECT
month_start,
DATEDIFF(DAY, '19000106', DATEADD(DAY, -1, DATEADD(MONTH, 1, month_start))) / 7 -
DATEDIFF(DAY, '19000106', DATEADD(DAY, -1, month_start)) / 7
FROM (
SELECT CAST(CAST(@year * 10000 + 0101 AS char(8)) AS datetime) AS month_start UNION ALL
SELECT CAST(@year * 10000 + 0201 AS char(8)) UNION ALL
SELECT CAST(@year * 10000 + 0301 AS char(8)) UNION ALL
SELECT CAST(@year * 10000 + 0401 AS char(8)) UNION ALL
SELECT CAST(@year * 10000 + 0501 AS char(8)) UNION ALL
SELECT CAST(@year * 10000 + 0601 AS char(8)) UNION ALL
SELECT CAST(@year * 10000 + 0701 AS char(8)) UNION ALL
SELECT CAST(@year * 10000 + 0801 AS char(8)) UNION ALL
SELECT CAST(@year * 10000 + 0901 AS char(8)) UNION ALL
SELECT CAST(@year * 10000 + 1001 AS char(8)) UNION ALL
SELECT CAST(@year * 10000 + 1101 AS char(8)) UNION ALL
SELECT CAST(@year * 10000 + 1201 AS char(8))
) AS months_of_the_year
ORDER BY
month_start
Looks like you're picking the start date to be the day of the week you want. Correct? 6 Jan 1900 as the seed because it's a Saturday, right?
If you're doing that calculation in the script or as a parameter, and concerned about @@DateFirst, you can modify mine like this:
DECLARE @StartDate DATE = '20120101', @SeedDate DATE = '19000106';
WITH Seeds(Seed)
AS (SELECT *
FROM ( VALUES ( 1), ( 1), ( 1), ( 1), ( 1), ( 1), ( 1), ( 1), ( 1), ( 1), ( 1), ( 1), ( 1), ( 1),
( 1), ( 1), ( 1), ( 1), ( 1), ( 1) ) AS V (C)),
Numbers(Number)
AS (SELECT ROW_NUMBER() OVER (ORDER BY S1.Seed) - 1
FROM Seeds AS S1
CROSS JOIN Seeds AS S2),
Calendar(Date)
AS (SELECT DATEADD(DAY, Number, @StartDate)
FROM Numbers
WHERE DATEADD(DAY, Number, @StartDate) < DATEADD(YEAR, 1, @StartDate))
SELECT DATEPART(MONTH, Date),
COUNT(*)
FROM Calendar
WHERE DATEPART(weekday, Date) = DATEPART(weekday, @SeedDate)
GROUP BY DATEPART(MONTH, Date)
ORDER BY DATEPART(MONTH, Date);
Not that it gives any execution-time advantage either way. Both run in 0 milliseconds on my test server.
Would come down to readability for each. Unsurprisingly, I find mine more readable. Easier to maintain. But since I wrote it, that's about as unfair a test as is possible. 🙂
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
September 17, 2012 at 9:34 am
sorry last request... if Sunday were to be added on there? So both Saturday and Sunday.
September 17, 2012 at 9:39 am
Eugene Elutin (9/17/2012)
a bit shorter version...
DECLARE @year int
SET @year = 2015
SELECT
MONTH(mfd) AS MonthNo
,DATENAME(MONTH,mfd) AS MonthName
,DATEDIFF(DAY, '19000106', DATEADD(DAY, -1, DATEADD(MONTH, 1, mfd))) / 7 -
DATEDIFF(DAY, '19000106', DATEADD(DAY, -1, mfd)) / 7 AS NoOfSaturdays
FROM (
SELECT CAST(CAST(@year * 100 + m.m AS CHAR(6)) + '01' AS datetime) mfd
FROM (VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12)) m(m)
) q
ORDER BY q.mfd
I like it.
I'd move the math for generating day-1 of the desired year into a variable at the top of the script, just for readability, but it works as-is.
DECLARE @Year CHAR(4) = '2012'; -- input parameter if proc
DECLARE @StartDate DATE = @Year + '0101';
SELECT DATEADD(MONTH, [month], @StartDate) AS MonthStart
FROM (VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11)) AS TVC([month])
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
September 17, 2012 at 10:31 am
GSquared (9/17/2012)
ScottPletcher (9/14/2012)
Code below has fewer calcs and does not depend on any SQL date settings.
DECLARE @year int
SET @year = 2012 --<<-- chg as needed
SELECT
month_start,
DATEDIFF(DAY, '19000106', DATEADD(DAY, -1, DATEADD(MONTH, 1, month_start))) / 7 -
DATEDIFF(DAY, '19000106', DATEADD(DAY, -1, month_start)) / 7
FROM (
SELECT CAST(CAST(@year * 10000 + 0101 AS char(8)) AS datetime) AS month_start UNION ALL
SELECT CAST(@year * 10000 + 0201 AS char(8)) UNION ALL
SELECT CAST(@year * 10000 + 0301 AS char(8)) UNION ALL
SELECT CAST(@year * 10000 + 0401 AS char(8)) UNION ALL
SELECT CAST(@year * 10000 + 0501 AS char(8)) UNION ALL
SELECT CAST(@year * 10000 + 0601 AS char(8)) UNION ALL
SELECT CAST(@year * 10000 + 0701 AS char(8)) UNION ALL
SELECT CAST(@year * 10000 + 0801 AS char(8)) UNION ALL
SELECT CAST(@year * 10000 + 0901 AS char(8)) UNION ALL
SELECT CAST(@year * 10000 + 1001 AS char(8)) UNION ALL
SELECT CAST(@year * 10000 + 1101 AS char(8)) UNION ALL
SELECT CAST(@year * 10000 + 1201 AS char(8))
) AS months_of_the_year
ORDER BY
month_start
Looks like you're picking the start date to be the day of the week you want. Correct? 6 Jan 1900 as the seed because it's a Saturday, right?
If you're doing that calculation in the script or as a parameter, and concerned about @@DateFirst, you can modify mine like this:
DECLARE @StartDate DATE = '20120101', @SeedDate DATE = '19000106';
WITH Seeds(Seed)
AS (SELECT *
FROM ( VALUES ( 1), ( 1), ( 1), ( 1), ( 1), ( 1), ( 1), ( 1), ( 1), ( 1), ( 1), ( 1), ( 1), ( 1),
( 1), ( 1), ( 1), ( 1), ( 1), ( 1) ) AS V (C)),
Numbers(Number)
AS (SELECT ROW_NUMBER() OVER (ORDER BY S1.Seed) - 1
FROM Seeds AS S1
CROSS JOIN Seeds AS S2),
Calendar(Date)
AS (SELECT DATEADD(DAY, Number, @StartDate)
FROM Numbers
WHERE DATEADD(DAY, Number, @StartDate) < DATEADD(YEAR, 1, @StartDate))
SELECT DATEPART(MONTH, Date),
COUNT(*)
FROM Calendar
WHERE DATEPART(weekday, Date) = DATEPART(weekday, @SeedDate)
GROUP BY DATEPART(MONTH, Date)
ORDER BY DATEPART(MONTH, Date);
Not that it gives any execution-time advantage either way. Both run in 0 milliseconds on my test server.
Would come down to readability for each. Unsurprisingly, I find mine more readable. Easier to maintain. But since I wrote it, that's about as unfair a test as is possible. 🙂
And unsuprisingly, I find my version more readable.
I mean, seriously, three levels of CTEs with a GROUP BY "more readable" than two DATEDIFF functions??
I should have added a comment about the date seed, just to be clear.
My code as originally written also works in earlier versions of SQL (I think it would even work in 7.0). SQL '08-specific features are great, when needed, but I don't use them just for the sake of using them. We still have '05 instances where I work, and I think some other people do too.
And, yes, the code for the month generation can be shortened, but I think it's clearer the longer way, and that bit won't affect execution time.
I have no doubt that the two DATEDIFFs will have much less overhead than the cross joins, etc., although it may not be signficant really, because SQL is so fast at doing cross joins.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
September 17, 2012 at 10:33 am
DECLARE @year int
SET @year = 2012 --<<-- chg as needed
SELECT
month_start,
--'19000106' is a Saturday, so DATEDIFFs below count Saturdays:
-- to count any other day, just change the base date to that day.
DATEDIFF(DAY, '19000106', DATEADD(DAY, -1, DATEADD(MONTH, 1, month_start))) / 7 -
DATEDIFF(DAY, '19000106', DATEADD(DAY, -1, month_start)) / 7
FROM (
SELECT CAST(CAST(@year * 10000 + 0101 AS char(8)) AS datetime) AS month_start UNION ALL
SELECT CAST(@year * 10000 + 0201 AS char(8)) UNION ALL
SELECT CAST(@year * 10000 + 0301 AS char(8)) UNION ALL
SELECT CAST(@year * 10000 + 0401 AS char(8)) UNION ALL
SELECT CAST(@year * 10000 + 0501 AS char(8)) UNION ALL
SELECT CAST(@year * 10000 + 0601 AS char(8)) UNION ALL
SELECT CAST(@year * 10000 + 0701 AS char(8)) UNION ALL
SELECT CAST(@year * 10000 + 0801 AS char(8)) UNION ALL
SELECT CAST(@year * 10000 + 0901 AS char(8)) UNION ALL
SELECT CAST(@year * 10000 + 1001 AS char(8)) UNION ALL
SELECT CAST(@year * 10000 + 1101 AS char(8)) UNION ALL
SELECT CAST(@year * 10000 + 1201 AS char(8))
) AS months_of_the_year
ORDER BY
month_start
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
September 17, 2012 at 10:44 am
sorry last request... if Sunday were to be added on there? So both Saturday and Sunday.
I'll assume you want to count them separately. If you want a total of both, just add the Counts instead of showing them as separate columns.
DECLARE @year int
SET @year = 2012 --<<-- chg as needed
SELECT
month_start,
DATEDIFF(DAY, '19000106', DATEADD(DAY, -1, DATEADD(MONTH, 1, month_start))) / 7 -
DATEDIFF(DAY, '19000106', DATEADD(DAY, -1, month_start)) / 7 AS Count_Of_Saturdays,
DATEDIFF(DAY, '19000107', DATEADD(DAY, -1, DATEADD(MONTH, 1, month_start))) / 7 -
DATEDIFF(DAY, '19000107', DATEADD(DAY, -1, month_start)) / 7 AS Count_Of_Sundays
FROM (
SELECT CAST(CAST(@year * 10000 + 0101 AS char(8)) AS datetime) AS month_start UNION ALL
SELECT CAST(@year * 10000 + 0201 AS char(8)) UNION ALL
SELECT CAST(@year * 10000 + 0301 AS char(8)) UNION ALL
SELECT CAST(@year * 10000 + 0401 AS char(8)) UNION ALL
SELECT CAST(@year * 10000 + 0501 AS char(8)) UNION ALL
SELECT CAST(@year * 10000 + 0601 AS char(8)) UNION ALL
SELECT CAST(@year * 10000 + 0701 AS char(8)) UNION ALL
SELECT CAST(@year * 10000 + 0801 AS char(8)) UNION ALL
SELECT CAST(@year * 10000 + 0901 AS char(8)) UNION ALL
SELECT CAST(@year * 10000 + 1001 AS char(8)) UNION ALL
SELECT CAST(@year * 10000 + 1101 AS char(8)) UNION ALL
SELECT CAST(@year * 10000 + 1201 AS char(8))
) AS months_of_the_year
ORDER BY
month_start
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
September 17, 2012 at 10:55 am
askmewhat (9/17/2012)
sorry last request... if Sunday were to be added on there? So both Saturday and Sunday.
He is one for you, it can count any number of selected week days separately and summarised:
DECLARE @year int
DECLARE @DaysToCount VARCHAR(7) -- any combination of 1234567 as per Monday (1) to Sunday (7)
SET @year = 2015
SET @DaysToCount = '357' -- to count Wednesdays, Fridays and Sundays
;WITH ym
AS
(
SELECT CAST(CAST(@year * 100 + m.m AS CHAR(6)) + '01' AS datetime) mfd
FROM (VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12)) m(m)
)
, dc
AS
(
SELECT CAST(d-1 AS DATETIME) AS wd
FROM (VALUES (1),(2),(3),(4),(5),(6),(7)) d(d)
WHERE CHARINDEX(CAST(d AS VARCHAR(1)), @DaysToCount,1) > 0
)
-- if you want just a SUM of selected week days per month USE THIS QUERY:
SELECT
MONTH(mfd) AS MonthNo
,DATENAME(MONTH,mfd) AS MonthName
,SUM(DATEDIFF(DAY, wd, DATEADD(DAY, -1, DATEADD(MONTH, 1, mfd))) / 7 -
DATEDIFF(DAY, wd, DATEADD(DAY, -1, mfd)) / 7 ) AS NoOfSelectedWeekDays
FROM ym
CROSS JOIN dc
GROUP BY mfd
ORDER BY mfd
-- if you want list of WeekDays per Month for each week day separately uncomment and
-- USE THIS QUERY instead of above one:
/*
SELECT
MONTH(mfd) AS MonthNo
,DATENAME(MONTH,mfd) AS MonthName
,DATENAME(WEEKDAY,wd) AS WeekDay
,DATEDIFF(DAY, wd, DATEADD(DAY, -1, DATEADD(MONTH, 1, mfd))) / 7 -
DATEDIFF(DAY, wd, DATEADD(DAY, -1, mfd)) / 7 AS NoOfWeekDays
FROM ym
CROSS JOIN dc
ORDER BY mfd , wd
*/
September 17, 2012 at 12:08 pm
ScottPletcher (9/17/2012)
GSquared (9/17/2012)
ScottPletcher (9/14/2012)
Code below has fewer calcs and does not depend on any SQL date settings.
DECLARE @year int
SET @year = 2012 --<<-- chg as needed
SELECT
month_start,
DATEDIFF(DAY, '19000106', DATEADD(DAY, -1, DATEADD(MONTH, 1, month_start))) / 7 -
DATEDIFF(DAY, '19000106', DATEADD(DAY, -1, month_start)) / 7
FROM (
SELECT CAST(CAST(@year * 10000 + 0101 AS char(8)) AS datetime) AS month_start UNION ALL
SELECT CAST(@year * 10000 + 0201 AS char(8)) UNION ALL
SELECT CAST(@year * 10000 + 0301 AS char(8)) UNION ALL
SELECT CAST(@year * 10000 + 0401 AS char(8)) UNION ALL
SELECT CAST(@year * 10000 + 0501 AS char(8)) UNION ALL
SELECT CAST(@year * 10000 + 0601 AS char(8)) UNION ALL
SELECT CAST(@year * 10000 + 0701 AS char(8)) UNION ALL
SELECT CAST(@year * 10000 + 0801 AS char(8)) UNION ALL
SELECT CAST(@year * 10000 + 0901 AS char(8)) UNION ALL
SELECT CAST(@year * 10000 + 1001 AS char(8)) UNION ALL
SELECT CAST(@year * 10000 + 1101 AS char(8)) UNION ALL
SELECT CAST(@year * 10000 + 1201 AS char(8))
) AS months_of_the_year
ORDER BY
month_start
Looks like you're picking the start date to be the day of the week you want. Correct? 6 Jan 1900 as the seed because it's a Saturday, right?
If you're doing that calculation in the script or as a parameter, and concerned about @@DateFirst, you can modify mine like this:
DECLARE @StartDate DATE = '20120101', @SeedDate DATE = '19000106';
WITH Seeds(Seed)
AS (SELECT *
FROM ( VALUES ( 1), ( 1), ( 1), ( 1), ( 1), ( 1), ( 1), ( 1), ( 1), ( 1), ( 1), ( 1), ( 1), ( 1),
( 1), ( 1), ( 1), ( 1), ( 1), ( 1) ) AS V (C)),
Numbers(Number)
AS (SELECT ROW_NUMBER() OVER (ORDER BY S1.Seed) - 1
FROM Seeds AS S1
CROSS JOIN Seeds AS S2),
Calendar(Date)
AS (SELECT DATEADD(DAY, Number, @StartDate)
FROM Numbers
WHERE DATEADD(DAY, Number, @StartDate) < DATEADD(YEAR, 1, @StartDate))
SELECT DATEPART(MONTH, Date),
COUNT(*)
FROM Calendar
WHERE DATEPART(weekday, Date) = DATEPART(weekday, @SeedDate)
GROUP BY DATEPART(MONTH, Date)
ORDER BY DATEPART(MONTH, Date);
Not that it gives any execution-time advantage either way. Both run in 0 milliseconds on my test server.
Would come down to readability for each. Unsurprisingly, I find mine more readable. Easier to maintain. But since I wrote it, that's about as unfair a test as is possible. 🙂
And unsuprisingly, I find my version more readable.
I mean, seriously, three levels of CTEs with a GROUP BY "more readable" than two DATEDIFF functions??
I should have added a comment about the date seed, just to be clear.
My code as originally written also works in earlier versions of SQL (I think it would even work in 7.0). SQL '08-specific features are great, when needed, but I don't use them just for the sake of using them. We still have '05 instances where I work, and I think some other people do too.
And, yes, the code for the month generation can be shortened, but I think it's clearer the longer way, and that bit won't affect execution time.
I have no doubt that the two DATEDIFFs will have much less overhead than the cross joins, etc., although it may not be signficant really, because SQL is so fast at doing cross joins.
The readability issue for me isn't so much about the DateDiff functions. Though those were only readily readable because I use similar functions to do things like get 1st-day-of-month, or (pre-SQL 2008) to strip time from DateTime values. What I had to stop my eyes and parse more slowly was the bits around the DateDiff, and this part "SELECT CAST(@year * 10000 + 0201 AS char(8))". The use of Union All data-type precendences to implicitly convert those strings to dates also took a moment of "what is that doing?" for me.
As for CTEs, I find them intuitive and highly readable. Again, that's subjective, and is almost certainly skewed because I've been looking at that particular construct (run-time Numbers tables and Calendar tables) frequently for 4 years, and a similar SQL 2005 version for over 7 years.
I agree the backwards compatibility is potentially an advantage yours has. I don't use newer just because it's newer. I use it when I think it's appropriate or makes for an easier solution, and won't break anything known.
Mine does "pick the day of week you want via a parameter" a bit more gracefully, but not enough so that it really matters. Would be easy enough to code a Case statement that could take an input of "Tuesday" and pick the right day in Jan 1900 for that. A table would be more flexible, but it's not like days-of-the-week in Jan 1900 need flexibility, and avoiding a table query would be worth it for performance reasons. So that largely negates any advantage that way for my version.
As per my original post, I'd probably really use a persisted Calendar table for this. After all, this is a simpler query than any of these:
SELECT MonthName, count(*)
FROM Common.dbo.Calendar
WHERE Year = 2012
AND WeekDay IN (7, 1)
ORDER BY MonthNumeric;
I have a table that already has all those columns pre-calculated. Isn't dependent on server settings, etc. Could modify the query with one more line in the Where clause and get holidays, too, if that's what this is for. Or invert it and get workdays per month with a simple Not In. And so on. That's backwards compatible and cross-database-engine compatible, with anything that'll run any reasonable semblance of ANSI SQL. Does have a dependency on having table dbo.Calendar in database Common, but I have that on every server I manage currently.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
September 17, 2012 at 12:09 pm
Looks like everyone is benefiting from this one way or another.
Super Thanks!
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply