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»»

Find number of weekdays(Monday,Tuesday..) between two dates yearwise Expand / Collapse
Author
Message
Posted Monday, June 23, 2008 3:55 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, June 26, 2008 9:22 PM
Points: 4, Visits: 11
Hi All,

I need to find average week day count on a yearly basis. My table has the creation date as a field which logs when a query was fired.

Eg. start date - 1/15/2006; end date - 12/12/2008

I am able to calculate the actual count of queries fired on each Monday,Tuesday... for the above time range on a yearly basis.

I am stuck at a point where I need to find the actual no. of Mondays, Tuesdays .. per year for the given time range.
I guess i need a function which will return the actual no. of days in a particular year/timespan.

Any help is welcome.

Thanks,
Bhakti
Post #521615
Posted Monday, June 23, 2008 5:27 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, August 10, 2010 5:07 AM
Points: 2,661, Visits: 23,078
Here is an article on tally tables.

http://www.sqlservercentral.com/articles/TSQL/62867/

There is code to get a list of dates between two dates using the tally table in the article.
You will then just need to filter using DATEPART to exclude Saturdays ad Sundays.

Give it a try, post here again if you have any trouble.
Post #521660
Posted Tuesday, June 24, 2008 3:57 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, June 26, 2008 9:22 PM
Points: 4, Visits: 11
Thanks Michael,

But I am not getting exactly how to find the total no. of weekdays in that year using the 'Tally' table. Can you please explain some what more?
It would be better if i can have a function which will accept StartDate, EndDate and the ClientDate parameters so that if the day of 'ClientDate' was say 'Tuesday', we should get to know the total no. of Tuesdays in that year from the StartDate till end of the year.

Thanks,
Bhakti
Post #522410
Posted Tuesday, June 24, 2008 9:21 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 7:27 AM
Points: 35,769, Visits: 32,437
bpbhalerao (6/24/2008)
Thanks Michael,

But I am not getting exactly how to find the total no. of weekdays in that year using the 'Tally' table. Can you please explain some what more?
It would be better if i can have a function which will accept StartDate, EndDate and the ClientDate parameters so that if the day of 'ClientDate' was say 'Tuesday', we should get to know the total no. of Tuesdays in that year from the StartDate till end of the year.

Thanks,
Bhakti


You need to be a little bit more clear on what you're trying to do... are you trying to...
1) find things like the number of Tuesdays between two dates or are you trying to
2) find the number of ALL weekdays between two dates?


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #522998
Posted Wednesday, June 25, 2008 1:09 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, December 15, 2014 4:47 PM
Points: 40, Visits: 188
I think something like this is what you're looking for. I didn't do extensive testing as I wrote it at work. I hope it helps.

CREATE FUNCTION [dbo].[WeekdaysInInterval]
( @StartDate SMALLDATETIME, @EndDate SMALLDATETIME, @day_of_week VARCHAR(9) )
RETURNS INT
AS

BEGIN
DECLARE @found BIT, @num INT
SET @found = 0

--find first occurence of day of week in interval
WHILE @found = 0 AND (@StartDate < @EndDate)
BEGIN
IF DATENAME(weekday,@StartDate) = @day_of_week
BEGIN SET @found = 1 END
ELSE
SET @StartDate = DATEADD(day,1,@StartDate)
END

--find number of weeks with this day of week included
SET @num = (DATEDIFF(day,@StartDate, @EndDate) / 7) + 1

RETURN @num
END
Post #523653
Posted Wednesday, June 25, 2008 1:18 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, December 15, 2014 4:47 PM
Points: 40, Visits: 188
Slight change to my original submission. If the interval is less than a week and the day of week is not found in that interval the following adjustments will need to be made. (Obviously if the interval is less than a week you can count by hand how many specific days of the week are in the interval but I don't want to be sloppy with code.)

1) You will need to initialize the variable @num to zero.
2) Change the predicate in the first WHILE statement to:
WHILE @found = 0 AND (@StartDate <= @EndDate)
3) Change the final statement to the following:
IF @StartDate <= @EndDate
SET @num = (DATEDIFF(day,@StartDate, @EndDate) / 7) + 1

That should take care of the situation.
Post #523660
Posted Wednesday, June 25, 2008 9:26 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, June 26, 2008 9:22 PM
Points: 4, Visits: 11
Hi All,
thanks a lot for all your help. I created one function for this which will calculate the total no. of Mondays or Tuesdays... etc.
Post #523831
Posted Wednesday, June 25, 2008 10:17 PM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Wednesday, December 10, 2014 11:46 PM
Points: 528, Visits: 1,279
bpbhalerao (6/25/2008)
Hi All,
thanks a lot for all your help. I created one function for this which will calculate the total no. of Mondays or Tuesdays... etc.


Hi bpbhalerao
Can you please post your solution?
Post #523844
Posted Wednesday, June 25, 2008 10:21 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 7:27 AM
Points: 35,769, Visits: 32,437
bpbhalerao (6/25/2008)
Hi All,
thanks a lot for all your help. I created one function for this which will calculate the total no. of Mondays or Tuesdays... etc.


2 way street here... would you post your function please?


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #523846
Posted Wednesday, June 25, 2008 10:36 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, June 26, 2008 9:22 PM
Points: 4, Visits: 11
What i exactly wanted is:
If the StartDate is say '7/15/2007', EndDate is '8/20/2009' and the ClientDate is say '9/12/2007' (the day is Wednesday on 12 Sept) then i need to calculate the total no. of Wednesdays from 7/15/2007 to 12/31/2007.
If the ClientDate is anything in the year 2008 say 1/12/2008 (i.e. Saturday), then we need calculate all the Saturdays in 2008 i.e. from 1st Jan to 31st Dec,
similarly if the ClientDate is any in the year 2009 say it is 5/25/2009 (Monday), then we need to calculate the total no. of Mondays from 1 Jan 2009 upto 8/20/2009.
For that i have created following function:

CREATE FUNCTION [dbo].[GetNumberofWeekDaysYearly](@StartDate DATETIME, @EndDate DATETIME, @ClientDate DATETIME)
RETURNS SMALLINT
AS
BEGIN

DECLARE @strMonth VARCHAR(15)
DECLARE @strYear VARCHAR(15)
DECLARE @strwday VARCHAR(15)

SET @strMonth = CAST(MONTH(@ClientDate) AS VARCHAR(15))
SET @strYear = CAST(YEAR(@ClientDate) AS VARCHAR(15))
SET @strwday = DATENAME(dw,@ClientDate)

/* Initialize the variable with the start date */
DECLARE @i SMALLINT
IF @strMonth = CAST(MONTH(@StartDate) AS VARCHAR(15)) AND @strYear = YEAR(@StartDate)
BEGIN
SET @i = DAY(@StartDate)
END
ELSE
BEGIN
SET @i = 1
END

DECLARE @LastDate SMALLINT
SET @LastDate = 32

/* Initialize the variable to the start of month */
DECLARE @j SMALLINT
IF CAST(MONTH(@StartDate) AS VARCHAR(15)) > 1 and @strYear = YEAR(@StartDate)
BEGIN
SET @j = MONTH(@StartDate)
END
ELSE
BEGIN
SET @j = 1
END

/* Initialize the variable to End of month */
DECLARE @LastMonth SMALLINT
IF CAST(MONTH(@EndDate) as VARCHAR(15)) < 12 AND @strYear = YEAR(@EndDate)
BEGIN
SET @LastMonth = MONTH(@EndDate) + 1
END
ELSE
BEGIN
SET @LastMonth = 13
END


DECLARE @dte VARCHAR(10)
DECLARE @TestDate VARCHAR(10)

DECLARE @iNumDays SMALLINT

SET @iNumDays = 0
WHILE @j < @LastMonth
BEGIN
IF(@j = MONTH(@EndDate) AND @strYear = YEAR(@EndDate))
BEGIN
SET @LastDate = DAY(@EndDate) + 1
END
WHILE @i < @LastDate
BEGIN
SET @TestDate = CAST(@j AS VARCHAR(2)) + '/' + CAST(@i AS VARCHAR(2)) + '/' + @strYear

IF ISDATE(@TestDate) = 1
BEGIN
IF (DATENAME(dw, @TestDate) = @strwday)
BEGIN
SET @iNumDays = @iNumDays + 1
END
END
SET @i = @i+1
End
SET @j = @j+1
SET @i = 1
END
RETURN @iNumDays

END
Post #523853
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse