Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Stored Procedure help Expand / Collapse
Author
Message
Posted Friday, September 14, 2012 7:53 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, September 18, 2012 10:23 AM
Points: 4, Visits: 12
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

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:43 PM
Points: 13,872, Visits: 9,596
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
Post #1359306
Posted Friday, September 14, 2012 9:29 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 5:24 PM
Points: 3,771, Visits: 8,459
It also depends on @@DATEFIRST = 7.
Otherwise you might be counting Sundays or another day.



Luis C.
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?

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1359418
Posted Friday, September 14, 2012 10:18 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 4:17 PM
Points: 2,197, Visits: 3,308
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)

Carl Sagan said: "There is no such thing as a dumb question." Sagan obviously never watched a congressional hearing!
Post #1359486
Posted Monday, September 17, 2012 8:50 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, September 18, 2012 10:23 AM
Points: 4, Visits: 12
Thanks!
Post #1360254
Posted Monday, September 17, 2012 9:08 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 8:55 AM
Points: 2,873, Visits: 5,185
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!"
(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

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:43 PM
Points: 13,872, Visits: 9,596
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
Post #1360280
Posted Monday, September 17, 2012 9:34 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, September 18, 2012 10:23 AM
Points: 4, Visits: 12
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

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:43 PM
Points: 13,872, Visits: 9,596
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
Post #1360287
Posted Monday, September 17, 2012 10:31 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 4:17 PM
Points: 2,197, Visits: 3,308
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)

Carl Sagan said: "There is no such thing as a dumb question." Sagan obviously never watched a congressional hearing!
Post #1360316
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse