SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Find number of weekdays(Monday,Tuesday..) between two dates yearwise


Find number of weekdays(Monday,Tuesday..) between two dates yearwise

Author
Message
bpbhalerao
bpbhalerao
Grasshopper
Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)

Group: General Forum Members
Points: 18 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
Michael Earl-395764
Michael Earl-395764
SSChampion
SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)

Group: General Forum Members
Points: 13535 Visits: 23078
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.
bpbhalerao
bpbhalerao
Grasshopper
Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)

Group: General Forum Members
Points: 18 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
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (219K reputation)SSC Guru (219K reputation)SSC Guru (219K reputation)SSC Guru (219K reputation)SSC Guru (219K reputation)SSC Guru (219K reputation)SSC Guru (219K reputation)SSC Guru (219K reputation)

Group: General Forum Members
Points: 219248 Visits: 42002
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
JeriHatTrick
JeriHatTrick
Valued Member
Valued Member (74 reputation)Valued Member (74 reputation)Valued Member (74 reputation)Valued Member (74 reputation)Valued Member (74 reputation)Valued Member (74 reputation)Valued Member (74 reputation)Valued Member (74 reputation)

Group: General Forum Members
Points: 74 Visits: 214
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
JeriHatTrick
JeriHatTrick
Valued Member
Valued Member (74 reputation)Valued Member (74 reputation)Valued Member (74 reputation)Valued Member (74 reputation)Valued Member (74 reputation)Valued Member (74 reputation)Valued Member (74 reputation)Valued Member (74 reputation)

Group: General Forum Members
Points: 74 Visits: 214
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.
bpbhalerao
bpbhalerao
Grasshopper
Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)

Group: General Forum Members
Points: 18 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.
Anam Verma
Anam Verma
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1383 Visits: 1318
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?
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (219K reputation)SSC Guru (219K reputation)SSC Guru (219K reputation)SSC Guru (219K reputation)SSC Guru (219K reputation)SSC Guru (219K reputation)SSC Guru (219K reputation)SSC Guru (219K reputation)

Group: General Forum Members
Points: 219248 Visits: 42002
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
bpbhalerao
bpbhalerao
Grasshopper
Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)

Group: General Forum Members
Points: 18 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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search