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