InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSat, 25 May 2013 21:17:51 GMT20http://www.sqlservercentral.com/Forums/Topic1359289-391-1.aspx[quote][b]GSquared (9/17/2012)[/b]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[/code]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 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:[code="sql"]SELECT MonthName, count(*)FROM Common.dbo.CalendarWHERE Year = 2012AND WeekDay IN (7, 1)ORDER BY MonthNumeric;[/code]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.[/quote]Interesting. I figured naming the derived table "months_of_the_year" and showing lines of input values with 01, 02, 03, ..., 12 in them would make it reasonably self-evident that this was generating, hmm, the months of a year :-).I only CAST() the first value to a datetime to attempt to have the code self-document what it was doing. You can remove it you prefer, and make the first month the same as the others:SELECT CAST(@year * 10000 + 0101 AS char(8)) AS month_start UNION ALLI'd avoid a calendar tables for the q as stated because it's way more overhead than necessary; i.e., I don't need any I/O to calculate the # of Saturdays in the months of a year.Mon, 17 Sep 2012 13:06:27 GMTScottPletcherhttp://www.sqlservercentral.com/Forums/Topic1359289-391-1.aspxLooks like everyone is benefiting from this one way or another.Super Thanks!Mon, 17 Sep 2012 12:09:46 GMTaskmewhathttp://www.sqlservercentral.com/Forums/Topic1359289-391-1.aspx[quote][b]ScottPletcher (9/17/2012)[/b][hr][quote][b]GSquared (9/17/2012)[/b][hr][quote][b]ScottPletcher (9/14/2012)[/b][hr]Code below has fewer calcs and does not depend on any SQL date settings.[code="sql"]DECLARE @year intSET @year = 2012 --<<-- chg as neededSELECT month_start, DATEDIFF(DAY, '19000106', DATEADD(DAY, -1, DATEADD(MONTH, 1, month_start))) / 7 - DATEDIFF(DAY, '19000106', DATEADD(DAY, -1, month_start)) / 7FROM ( 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_yearORDER BY month_start[/code][/quote]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:[code="sql"]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);[/code]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. [b][i][u]Unsurprisingly, I find mine more readable.[/u][/i][/b] Easier to maintain. But since I wrote it, that's about as unfair a test as is possible. :-)[/quote]And unsuprisingly, I find my version more readable.I mean, seriously, [u]three levels of CTEs with a GROUP BY[/u] "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.[/quote]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:[code="sql"]SELECT MonthName, count(*)FROM Common.dbo.CalendarWHERE Year = 2012AND WeekDay IN (7, 1)ORDER BY MonthNumeric;[/code]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.Mon, 17 Sep 2012 12:08:00 GMTGSquaredhttp://www.sqlservercentral.com/Forums/Topic1359289-391-1.aspx[quote][b]askmewhat (9/17/2012)[/b][hr]sorry last request... if Sunday were to be added on there? So both Saturday and Sunday.[/quote]He is one for you, it can count any number of selected week days separately and summarised: [code="sql"]DECLARE @year intDECLARE @DaysToCount VARCHAR(7) -- any combination of 1234567 as per Monday (1) to Sunday (7)SET @year = 2015SET @DaysToCount = '357' -- to count Wednesdays, Fridays and Sundays;WITH ymAS( 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)), dcAS( 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 dcGROUP 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 dcORDER BY mfd , wd */[/code]Mon, 17 Sep 2012 10:55:34 GMTEugene Elutinhttp://www.sqlservercentral.com/Forums/Topic1359289-391-1.aspx[quote]sorry last request... if Sunday were to be added on there? So both Saturday and Sunday. [/quote]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.[code="sql"]DECLARE @year intSET @year = 2012 --<<-- chg as neededSELECT 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_SundaysFROM ( 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_yearORDER BY month_start[/code]Mon, 17 Sep 2012 10:44:42 GMTScottPletcherhttp://www.sqlservercentral.com/Forums/Topic1359289-391-1.aspx[code="sql"]DECLARE @year intSET @year = 2012 --<<-- chg as neededSELECT month_start,[b] --'19000106' is a Saturday, so DATEDIFFs below count Saturdays: -- to count any other day, just change the base date to that day.[/b] DATEDIFF(DAY, '19000106', DATEADD(DAY, -1, DATEADD(MONTH, 1, month_start))) / 7 - DATEDIFF(DAY, '19000106', DATEADD(DAY, -1, month_start)) / 7FROM ( 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_yearORDER BY month_start[/code]Mon, 17 Sep 2012 10:33:49 GMTScottPletcherhttp://www.sqlservercentral.com/Forums/Topic1359289-391-1.aspx[quote][b]GSquared (9/17/2012)[/b][hr][quote][b]ScottPletcher (9/14/2012)[/b][hr]Code below has fewer calcs and does not depend on any SQL date settings.[code="sql"]DECLARE @year intSET @year = 2012 --<<-- chg as neededSELECT month_start, DATEDIFF(DAY, '19000106', DATEADD(DAY, -1, DATEADD(MONTH, 1, month_start))) / 7 - DATEDIFF(DAY, '19000106', DATEADD(DAY, -1, month_start)) / 7FROM ( 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_yearORDER BY month_start[/code][/quote]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:[code="sql"]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);[/code]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. [b][i][u]Unsurprisingly, I find mine more readable.[/u][/i][/b] Easier to maintain. But since I wrote it, that's about as unfair a test as is possible. :-)[/quote]And unsuprisingly, I find my version more readable.I mean, seriously, [u]three levels of CTEs with a GROUP BY[/u] "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.Mon, 17 Sep 2012 10:31:50 GMTScottPletcherhttp://www.sqlservercentral.com/Forums/Topic1359289-391-1.aspx[quote][b]Eugene Elutin (9/17/2012)[/b][hr]a bit shorter version...[code="sql"]DECLARE @year intSET @year = 2015SELECT 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 NoOfSaturdaysFROM ( 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 [/code][/quote]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.[code="sql"]DECLARE @Year CHAR(4) = '2012'; -- input parameter if procDECLARE @StartDate DATE = @Year + '0101';SELECT DATEADD(MONTH, [month], @StartDate) AS MonthStartFROM (VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11)) AS TVC([month])[/code]Mon, 17 Sep 2012 09:39:50 GMTGSquaredhttp://www.sqlservercentral.com/Forums/Topic1359289-391-1.aspxsorry last request... if Sunday were to be added on there? So both Saturday and Sunday.Mon, 17 Sep 2012 09:34:53 GMTaskmewhathttp://www.sqlservercentral.com/Forums/Topic1359289-391-1.aspx[quote][b]ScottPletcher (9/14/2012)[/b][hr]Code below has fewer calcs and does not depend on any SQL date settings.[code="sql"]DECLARE @year intSET @year = 2012 --<<-- chg as neededSELECT month_start, DATEDIFF(DAY, '19000106', DATEADD(DAY, -1, DATEADD(MONTH, 1, month_start))) / 7 - DATEDIFF(DAY, '19000106', DATEADD(DAY, -1, month_start)) / 7FROM ( 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_yearORDER BY month_start[/code][/quote]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:[code="sql"]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);[/code]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. :-)Mon, 17 Sep 2012 09:28:56 GMTGSquaredhttp://www.sqlservercentral.com/Forums/Topic1359289-391-1.aspxa bit shorter version...[code="sql"]DECLARE @year intSET @year = 2015SELECT 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 NoOfSaturdaysFROM ( 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 [/code]Mon, 17 Sep 2012 09:08:46 GMTEugene Elutinhttp://www.sqlservercentral.com/Forums/Topic1359289-391-1.aspxThanks!Mon, 17 Sep 2012 08:50:32 GMTaskmewhathttp://www.sqlservercentral.com/Forums/Topic1359289-391-1.aspxCode below has fewer calcs and does not depend on any SQL date settings.[code="sql"]DECLARE @year intSET @year = 2012 --<<-- chg as neededSELECT month_start, DATEDIFF(DAY, '19000106', DATEADD(DAY, -1, DATEADD(MONTH, 1, month_start))) / 7 - DATEDIFF(DAY, '19000106', DATEADD(DAY, -1, month_start)) / 7FROM ( 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_yearORDER BY month_start[/code]Fri, 14 Sep 2012 10:18:41 GMTScottPletcherhttp://www.sqlservercentral.com/Forums/Topic1359289-391-1.aspxIt also depends on @@DATEFIRST = 7.Otherwise you might be counting Sundays or another day.Fri, 14 Sep 2012 09:29:06 GMTLuis Cazareshttp://www.sqlservercentral.com/Forums/Topic1359289-391-1.aspxLike this:[code="sql"]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);[/code]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.Fri, 14 Sep 2012 08:07:41 GMTGSquaredhttp://www.sqlservercentral.com/Forums/Topic1359289-391-1.aspxHi, 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.Fri, 14 Sep 2012 07:53:57 GMTaskmewhat