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