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 Monday, September 17, 2012 10:33 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 11:38 AM
Points: 1,967, Visits: 2,906
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)
"In America, every man is innocent until proven broke!" Brant Parker
Post #1360319
Posted Monday, September 17, 2012 10:44 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 11:38 AM
Points: 1,967, Visits: 2,906

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)
"In America, every man is innocent until proven broke!" Brant Parker
Post #1360331
Posted Monday, September 17, 2012 10:55 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, July 4, 2014 3:55 AM
Points: 2,836, Visits: 5,062
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!"
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help
Post #1360342
Posted Monday, September 17, 2012 12:08 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:43 PM
Points: 15,444, Visits: 9,596
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
Post #1360385
Posted Monday, September 17, 2012 12:09 PM
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
Looks like everyone is benefiting from this one way or another.

Super Thanks!
Post #1360386
Posted Monday, September 17, 2012 1:06 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 11:38 AM
Points: 1,967, Visits: 2,906
GSquared (9/17/2012)
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:

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.



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 ALL

I'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.


SQL DBA,SQL Server MVP('07, '08, '09)
"In America, every man is innocent until proven broke!" Brant Parker
Post #1360415
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse