Stored Procedure help

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

  • 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

  • It also depends on @@DATEFIRST = 7.

    Otherwise you might be counting Sundays or another day.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • 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.

  • Thanks!

  • 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

    _____________________________________________
    "The only true wisdom is in knowing you know nothing"
    "O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
    (So many miracle inventions provided by MS to us...)

    How to post your question to get the best and quick help[/url]

  • 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

  • sorry last request... if Sunday were to be added on there? So both Saturday and Sunday.

  • 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

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

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

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

  • 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

    */

    _____________________________________________
    "The only true wisdom is in knowing you know nothing"
    "O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
    (So many miracle inventions provided by MS to us...)

    How to post your question to get the best and quick help[/url]

  • 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

  • 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