Stored Procedure help

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);

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

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

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);

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])

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