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


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


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

Author
Message
nikhil_ambaye
nikhil_ambaye
Grasshopper
Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)

Group: General Forum Members
Points: 24 Visits: 28
Hi All,

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

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

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

I am stuck at a point where I need to find the actual no. of Mondays, Tuesdays .. per month for the given time range.

Any help is welcome.

TIA,
Nikhil
KenSimmons
KenSimmons
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1628 Visits: 2614
Can you post what you have? You may have to create a function depending on the query.

Ken Simmons
http://twitter.com/KenSimmons
nikhil_ambaye
nikhil_ambaye
Grasshopper
Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)

Group: General Forum Members
Points: 24 Visits: 28
Database - > LogBase
Database Columns - > CreationDate, Text

My Query to to calculate the actual count of queries fired on each Monday,Tuesday... for the above time range on a monthly basis.

SELECT DATENAME(month, CreationDate) as DayName,DATENAME(dw, CreationDate) as DayName ,count(*) as ActualCount
FROM LogBase
Where CreationDate between @fromDate and @toDate and DATENAME(dw, CreationDate)
group by DATENAME(dw, CreationDate)

So here I am able to get number of queries/transaction done on each Monday,Tuesday,.. monthwise.

What I am stuck at is how to I calculate the actual no. of Mondays, Tuesdays .. per month for the given time range. (say range is from 3-june-2008 to 7-July-2008. So here no. of mondays in June would be 4 and no. of Mondays in July would be 1). I need this data so that I can calculate Average WeeKday Transactions on a monthly bias
KenSimmons
KenSimmons
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1628 Visits: 2614
--Create this function and call it from your query passing it
--dbo.udf_NumXWeekDaysinMonth(CreationDate)

CREATE Function dbo.udf_NumXWeekDaysinMonth(@Date datetime)
RETURNS smallint
AS
BEGIN
Declare @dte varchar(10)
Declare @TestDate varchar(10)



Declare @i smallint
Declare @iNumDays smallint

Set @dte = Convert(varchar(10),@Date,101)
Set @i = 1
Set @iNumDays = 0


While @i < 32
Begin
Set @TestDate = cast(month(@dte) as varchar(2)) + '/' + cast(@i as varchar(2)) + '/' + Cast(Year(@dte) as varchar(4))
--print @TestDate
IF isdate(@TestDate) = 1
BEGIN
IF (DATENAME(dw, @TestDate) = DATENAME(dw, @dte))
BEGIN
Set @iNumDays = @iNumDays + 1
END
END

Set @i = @i+1
End
Return @iNumDays
END

Ken Simmons
http://twitter.com/KenSimmons
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)

Group: General Forum Members
Points: 87202 Visits: 41113
The formula in the following article will calculate weekdays without a loop. There is, however, no consideration for holidays...

http://www.sqlservercentral.com/articles/Advanced+Querying/calculatingworkdays/1660/

--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
GSquared
GSquared
SSC-Insane
SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)

Group: General Forum Members
Points: 23755 Visits: 9730
For accurate counts of weekdays, I HIGHLY recommend a calendar table. That way, you can include holidays, etc.


create table Calendar (
Date datetime primary key,
constraint CK_Date_NoTime
check (Date = cast(cast(cast(date as float) as int) as datetime)),
Workday bit not null,
Year as datepart(year, date),
Month as datepart(month, date),
Day as datepart(day, date),
WeekDay as datepart(weekday, date))
go
insert into dbo.Calendar (Date, Workday)
select dateadd(day, number, '1/1/2000'),
case
when datepart(weekday, dateadd(day, number, '1/1/2000')) between 2 and 6 then 1
else 0
end
from dbo.numbers -- A table of 10-thousand numbers
go
create index IDX_Calendar_MonthDay on dbo.Calendar(month, day)
go
update dbo.Calendar
set workday = 0
where month = 7 and day = 4
or ... -- fill in other holidays here
go
select count(*)
from dbo.Calendar
where workday = 1
and date between '5/20/2008' and '6/9/2008'



With a table like that, you can easily have SQL do things like calculate the 1st Monday in September, or the 4th Thursday in November, record these things as holidays, and calculate workdays between dates.


;with Thanksgiving (Row, Date, Workday) as
(select row_number() over (order by date), date, workday
from dbo.Calendar
where Month = 11
and WeekDay = 5)
update Thanksgiving
set workday = 0
where row = 4



You can also add more indexes to it, if you so desire.

Using a 10-thousand day calendar starting from 1 Jan 2000, will give you a table that goes into 2027, which should be enough to start with. As needed, add more, or create a job that runs on the first day of each year and adds another year worth of days (if you do that, make sure it adds enough to account for leap years).

- 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
Stephen Yale
Stephen Yale
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: 271
GSquared (6/9/2008)
For accurate counts of weekdays, I HIGHLY recommend a calendar table. That way, you can include holidays, etc.


create table Calendar (
Date datetime primary key,
constraint CK_Date_NoTime
check (Date = cast(cast(cast(date as float) as int) as datetime)),
Workday bit not null,
Year as datepart(year, date),
Month as datepart(month, date),
Day as datepart(day, date),
WeekDay as datepart(weekday, date))
go
insert into dbo.Calendar (Date, Workday)
select dateadd(day, number, '1/1/2000'),
case
when datepart(weekday, dateadd(day, number, '1/1/2000')) between 2 and 6 then 1
else 0
end
from dbo.numbers -- A table of 10-thousand numbers
go
create index IDX_Calendar_MonthDay on dbo.Calendar(month, day)
go
update dbo.Calendar
set workday = 0
where month = 7 and day = 4
or ... -- fill in other holidays here
go
select count(*)
from dbo.Calendar
where workday = 1
and date between '5/20/2008' and '6/9/2008'



With a table like that, you can easily have SQL do things like calculate the 1st Monday in September, or the 4th Thursday in November, record these things as holidays, and calculate workdays between dates.


;with Thanksgiving (Row, Date, Workday) as
(select row_number() over (order by date), date, workday
from dbo.Calendar
where Month = 11
and WeekDay = 5)
update Thanksgiving
set workday = 0
where row = 4



You can also add more indexes to it, if you so desire.

Using a 10-thousand day calendar starting from 1 Jan 2000, will give you a table that goes into 2027, which should be enough to start with. As needed, add more, or create a job that runs on the first day of each year and adds another year worth of days (if you do that, make sure it adds enough to account for leap years).


Using the code above I came up with the code below for adding company holidays:

How do I do it without using a cursor?

--http://www.sqlservercentral.com/Forums/Topic513315-149-1.aspx#bm513318

IF OBJECT_ID('Calendar', 'U') IS NOT NULL
DROP TABLE Calendar

create table Calendar (
Date datetime primary key,
constraint CK_Date_NoTime
check (Date = cast(cast(cast(date as float) as int) as datetime)),
Workday bit not null,
Year as datepart(year, date),
Month as datepart(month, date),
Day as datepart(day, date),
WeekDay as datepart(weekday, date),
LongDay as datename(weekday, date),
DayType varchar(50))
go

--create table Numbers (
--Num_ID int)
DECLARE @Numbers TABLE
(
Num_ID INT
)

declare @number as int
set @number =1
while @number <10001
begin
insert into @Numbers (num_id) values (@number)
set @number=@number+1
end


insert into dbo.Calendar (Date, Workday,DayType)
select dateadd(day, num_id, '12/31/1999'),
case
when datepart(weekday, dateadd(day, num_id, '12/31/1999')) between 2 and 6 then 1
else 0
end,
case
when datepart(weekday, dateadd(day, num_id, '12/31/1999')) between 2 and 6 then 'Work'
else 'Weekend'
end
from @Numbers -- A table of 10-thousand numbers
go
create index IDX_Calendar_MonthDay on dbo.Calendar(month, day)
go

update dbo.Calendar
set workday = 0, DayType='New Years Day'
where month = 1 and day = 1
go
update dbo.Calendar
set workday = 0, DayType='Independance Day'
where month = 7 and day = 4
go
update dbo.Calendar
set workday = 0, DayType='Christmas Day'
where month = 12 and day = 25
go

DECLARE @CalendarYear TABLE
(
[Year] INT
)

DECLARE @year int

INSERT INTO @CalendarYear
select distinct year from calendar order by year

DECLARE cYear CURSOR
FOR SELECT year FROM @CalendarYear

OPEN cYear

fetch cYear into @year
while @@fetch_status=0
begin
update dbo.Calendar set workday = 0, DayType='Memorial Day' where date=(select max(date) from calendar where month =5 and year=@year and weekday=2)
update dbo.Calendar set workday = 0, DayType='Labor Day' where date=(select min(date) from calendar where month =9 and year=@year and weekday=2)
update dbo.Calendar set workday = 0, DayType='Thanksgiving Day' where date=(select max(date)-1 from calendar where month =11 and year=@year and weekday=6)
update dbo.Calendar set workday = 0, DayType='Day after Thanksgiving Day' where date=(select max(date) from calendar where month =11 and year=@year and weekday=6)

fetch cYear into @year

end
close cYear

/*

1st Jan New Year's Day
Last Monday in May Memorial Day
4th Jul Independence Day
First Monday in September Labor Day
Fourth Thursday in November Thanksgiving Day
25th Dec Christmas Day

*/
Kevin Bernard
Kevin Bernard
Grasshopper
Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)

Group: General Forum Members
Points: 14 Visits: 104
I would just wrap your code in () and use it as a type of derived table and average the counts like this...

select MonthName, Dayname, Average(actualCount)
from
(
SELECT CreationDate,DATENAME(month, CreationDate) as MonthName,DATENAME(dw, CreationDate) as DayName ,count(*) as ActualCount
FROM LogBase
Where CreationDate between @fromDate and @toDate
group by CreationDate, DATENAME(month, CreationDate),DATENAME(dw, CreationDate)
) as a
group by MonthName, Dayname
ChrisM@Work
ChrisM@Work
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16438 Visits: 19554
Stephen Yale (6/18/2008)
.....
How do I do it without using a cursor?
.....


Here's one way:

CREATE FUNCTION [dbo].[IF_Calendar] 
(
@StartDate DATE,
@EndDate DATE,
@FirstWeekDay VARCHAR(10)
)
RETURNS TABLE WITH SCHEMABINDING AS
RETURN
(
-- inline tally table
WITH E1(N) AS (
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
), --10E+1 or 10 rows
E2(N) AS (SELECT 1 FROM E1 a CROSS JOIN E1 b), --10E+2 or 100 rows
E3(N) AS (SELECT 1 FROM E2 a CROSS JOIN E2 b), --10E+4 or 10,000 rows max

iTally AS ( -- generate sufficient rows to cover startdate to enddate inclusive
SELECT TOP(1+DATEDIFF(DAY,@StartDate,@EndDate))
rn = ROW_NUMBER() OVER(ORDER BY (SELECT NULL))-1
FROM E3
)

-- Do some date arithmetic
SELECT
a.DateRange,
c.[Year],
c.[Month],
c.[DayOfMonth],
c.AbsWeekno,
c.[DayName],
d.Holiday
FROM iTally
CROSS APPLY (SELECT DateRange = DATEADD(day,rn,@StartDate)) a
CROSS APPLY (VALUES ('Tuesday',1),('Wednesday',2),('Thursday',3),('Friday',4),('Saturday',5),('Sunday',6),('Monday',7)
) b (FirstWeekDay, FirstWeekdayOffset)
CROSS APPLY (
SELECT
[Year] = YEAR(a.DateRange),
[Month] = MONTH(a.DateRange),
[DayOfMonth] = DAY(a.DateRange),
AbsWeekno = DATEDIFF(day,FirstWeekdayOffset,a.DateRange)/7,
[DayName] = DATENAME(weekday,a.DateRange)
) c
CROSS APPLY (
SELECT Holiday = CASE
WHEN [Month] = 1 AND [DayOfMonth] = 1 THEN 'New Year'
WHEN [Month] = 5 AND [DayOfMonth] >= 25 AND [DayName] = 'Monday' THEN 'Memorial Day'
WHEN [Month] = 7 AND [DayOfMonth] = 4 THEN 'Independence Day'
WHEN [Month] = 9 AND [DayOfMonth] <= 7 AND [DayName] = 'Monday' THEN 'Labor Day'
WHEN [Month] = 11 AND [DayOfMonth] BETWEEN 22 AND 28 AND [DayName] = 'Thursday' THEN 'Thanksgiving Day'
WHEN [Month] = 12 AND [DayOfMonth] = 25 THEN 'Christmas Day'
ELSE NULL END
) d
WHERE b.FirstWeekDay = @FirstWeekDay

)



“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
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