June 23, 2008 at 3:55 am
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
June 23, 2008 at 5:27 am
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.
June 24, 2008 at 3:57 am
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
June 24, 2008 at 9:21 pm
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
Change is inevitable... Change for the better is not.
June 25, 2008 at 1:09 pm
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
June 25, 2008 at 1:18 pm
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.
June 25, 2008 at 9:26 pm
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.
June 25, 2008 at 10:17 pm
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?
June 25, 2008 at 10:21 pm
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
Change is inevitable... Change for the better is not.
June 25, 2008 at 10:36 pm
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-2 SMALLINT
IF CAST(MONTH(@StartDate) AS VARCHAR(15)) > 1 and @strYear = YEAR(@StartDate)
BEGIN
SET @j-2 = MONTH(@StartDate)
END
ELSE
BEGIN
SET @j-2 = 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-2 < @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 @i = 1
END
RETURN @iNumDays
END
June 26, 2008 at 6:09 am
bpbhalerao (6/25/2008)
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.
I absolutely don't understand how you ended up with 8/20/2009 as an enddate just because the ClientDate happens to be in 2009... what's the logic you're using behind that, please?
--Jeff Moden
Change is inevitable... Change for the better is not.
June 26, 2008 at 6:10 am
Jeff Moden (6/26/2008)
bpbhalerao (6/25/2008)
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.I absolutely don't understand how you ended up with 8/20/2009 as an enddate just because the ClientDate happens to be in 2009... what's the logic you're using behind that, please?
Never, mind... I've got it...
--Jeff Moden
Change is inevitable... Change for the better is not.
June 26, 2008 at 7:36 am
bpbhalerao (6/25/2008)
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-2 SMALLINT
IF CAST(MONTH(@StartDate) AS VARCHAR(15)) > 1 and @strYear = YEAR(@StartDate)
BEGIN
SET @j-2 = MONTH(@StartDate)
END
ELSE
BEGIN
SET @j-2 = 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-2 < @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 @i = 1
END
RETURN @iNumDays
END
Ok... this is the perfect place for a little setbased logic instead of all that RBAR... 😉
[font="Courier New"] CREATE FUNCTION dbo.GetNumberofWeekDaysYearly
(@StartDate DATETIME, @EndDate DATETIME, @ClientDate DATETIME)
RETURNS SMALLINT
AS
BEGIN
--===== Declare some local variables to control bounds of dates
DECLARE @YearStart DATETIME,
@YearEnd DATETIME,
@DowClient INT
--===== Calculate the correct bounds of dates
SELECT @YearStart = DATEADD(yy,DATEDIFF(yy,0,@ClientDate),0),
@YearEnd = DATEADD(yy,DATEDIFF(yy,-1,@ClientDate),-1),
@StartDate = CASE
WHEN @StartDate < @YearStart
THEN @YearStart
ELSE @StartDate
END,
@EndDate = CASE
WHEN @EndDate > @YearEnd
THEN @YearEnd
ELSE @EndDate
END,
@DowClient = DATEDIFF(dd,0,@ClientDate)%7
--===== Calculate and return the count for the bounds of dates
-- based on the day of the week for the Client Date
RETURN (SELECT COUNT(*)
--@StartDate+(t.N-1),DATENAME(dw,@StartDate+(t.N-1))
FROM dbo.Tally t
WHERE @StartDate+(t.N-1) BETWEEN @StartDate AND @EndDate
AND DATEDIFF(dd,0,@StartDate+(t.N-1))%7 = @DowClient)
END[/font]
If you don't know what a Tally table is, now's the time to make this incredibly useful tool. See the following article...
http://www.sqlservercentral.com/articles/TSQL/62867/
--Jeff Moden
Change is inevitable... Change for the better is not.
June 26, 2008 at 7:41 am
... and now you know why I wanted you to post your function 😉 There's a better way than RBAR... :hehe:
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply