Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 Stored Procedure help Rate Topic Display Mode Topic Options
Author
 Message
 Posted Friday, September 14, 2012 7:53 AM
 Forum Newbie Group: General Forum Members Last Login: Tuesday, November 17, 2015 9:35 PM Points: 5, Visits: 49
 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.
Post #1359289
 Posted Friday, September 14, 2012 8:07 AM
 SSChampion Group: General Forum Members Last Login: Monday, August 29, 2016 1:09 PM Points: 13,999, Visits: 9,728
 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, ETCProperty of The Thread"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Post #1359306
 Posted Friday, September 14, 2012 9:29 AM
 SSCrazy Eights Group: General Forum Members Last Login: Today @ 7:01 PM Points: 8,205, Visits: 17,752
 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
Post #1359418
 Posted Friday, September 14, 2012 10:18 AM
 Hall of Fame Group: General Forum Members Last Login: Friday, December 2, 2016 3:56 PM Points: 3,852, Visits: 6,566
 Code below has fewer calcs and does not depend on any SQL date settings.`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` SQL DBA,SQL Server MVP('07, '08, '09)Prosecutor James Blackburn, in closing argument in the "Fatal Vision" murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."
Post #1359486
 Posted Monday, September 17, 2012 8:50 AM
 Forum Newbie Group: General Forum Members Last Login: Tuesday, November 17, 2015 9:35 PM Points: 5, Visits: 49
 Thanks!
Post #1360254
 Posted Monday, September 17, 2012 9:08 AM
 SSCrazy Group: General Forum Members Last Login: Monday, November 28, 2016 10:12 AM Points: 2,934, Visits: 5,475
 a bit shorter version...`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 ` _____________________________________________"The only true wisdom is in knowing you know nothing""O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!"(So many miracle inventions provided by MS to us...)How to post your question to get the best and quick help
Post #1360270
 Posted Monday, September 17, 2012 9:28 AM
 SSChampion Group: General Forum Members Last Login: Monday, August 29, 2016 1:09 PM Points: 13,999, Visits: 9,728
 ScottPletcher (9/14/2012)Code below has fewer calcs and does not depend on any SQL date settings.`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`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, ETCProperty of The Thread"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Post #1360280
 Posted Monday, September 17, 2012 9:34 AM
 Forum Newbie Group: General Forum Members Last Login: Tuesday, November 17, 2015 9:35 PM Points: 5, Visits: 49
 sorry last request... if Sunday were to be added on there? So both Saturday and Sunday.
Post #1360285
 Posted Monday, September 17, 2012 9:39 AM
 SSChampion Group: General Forum Members Last Login: Monday, August 29, 2016 1:09 PM Points: 13,999, Visits: 9,728
 Eugene Elutin (9/17/2012)a bit shorter version...`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 `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 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])` - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETCProperty of The Thread"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Post #1360287
 Posted Monday, September 17, 2012 10:31 AM
 Hall of Fame Group: General Forum Members Last Login: Friday, December 2, 2016 3:56 PM Points: 3,852, Visits: 6,566
 GSquared (9/17/2012)ScottPletcher (9/14/2012)Code below has fewer calcs and does not depend on any SQL date settings.`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`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)Prosecutor James Blackburn, in closing argument in the "Fatal Vision" murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."
Post #1360316

 Permissions