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

 Author Message nikhil_ambaye SSC-Enthusiastic Group: General Forum Members Points: 146 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/2009I 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 SSCarpal Tunnel Group: General Forum Members Points: 4698 Visits: 2614 Can you post what you have? You may have to create a function depending on the query. Ken Simmonshttp://twitter.com/KenSimmons nikhil_ambaye SSC-Enthusiastic Group: General Forum Members Points: 146 Visits: 28 Database - > LogBaseDatabase Columns - > CreationDate, TextMy 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 SSCarpal Tunnel Group: General Forum Members Points: 4698 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 smallintASBEGINDeclare @dte varchar(10)Declare @TestDate varchar(10)Declare @i smallintDeclare @iNumDays smallintSet @dte = Convert(varchar(10),@Date,101)Set @i = 1Set @iNumDays = 0While @i < 32BeginSet @TestDate = cast(month(@dte) as varchar(2)) + '/' + cast(@i as varchar(2)) + '/' + Cast(Year(@dte) as varchar(4))--print @TestDateIF isdate(@TestDate) = 1 BEGIN IF (DATENAME(dw, @TestDate) = DATENAME(dw, @dte)) BEGIN Set @iNumDays = @iNumDays + 1 END ENDSet @i = @i+1EndReturn @iNumDaysEND Ken Simmonshttp://twitter.com/KenSimmons Jeff Moden SSC Guru Group: General Forum Members Points: 504062 Visits: 44223 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 ModenRBAR 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 problemsHow to post performance problemsForum FAQs GSquared SSC Guru Group: General Forum Members Points: 138187 Visits: 9731 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))goinsert 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 0endfrom dbo.numbers -- A table of 10-thousand numbersgocreate index IDX_Calendar_MonthDay on dbo.Calendar(month, day)goupdate dbo.Calendarset workday = 0where month = 7 and day = 4or ... -- fill in other holidays heregoselect count(*)from dbo.Calendar where workday = 1and 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 Thanksgivingset workday = 0where 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, ETCProperty of The Thread"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon Stephen Yale SSC-Enthusiastic Group: General Forum Members Points: 100 Visits: 280 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))goinsert 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 0endfrom dbo.numbers -- A table of 10-thousand numbersgocreate index IDX_Calendar_MonthDay on dbo.Calendar(month, day)goupdate dbo.Calendarset workday = 0where month = 7 and day = 4or ... -- fill in other holidays heregoselect count(*)from dbo.Calendar where workday = 1and 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 Thanksgivingset workday = 0where 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#bm513318IF OBJECT_ID('Calendar', 'U') IS NOT NULL DROP TABLE Calendarcreate 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 intset @number =1while @number <10001begininsert into @Numbers (num_id) values (@number)set @number=@number+1endinsert 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 0end,case when datepart(weekday, dateadd(day, num_id, '12/31/1999')) between 2 and 6 then 'Work' else 'Weekend'endfrom @Numbers -- A table of 10-thousand numbersgocreate index IDX_Calendar_MonthDay on dbo.Calendar(month, day)goupdate dbo.Calendarset workday = 0, DayType='New Years Day'where month = 1 and day = 1 goupdate dbo.Calendarset workday = 0, DayType='Independance Day'where month = 7 and day = 4 goupdate dbo.Calendarset workday = 0, DayType='Christmas Day'where month = 12 and day = 25 goDECLARE @CalendarYear TABLE([Year] INT )DECLARE @year intINSERT INTO @CalendarYear select distinct year from calendar order by yearDECLARE cYear CURSORFOR SELECT year FROM @CalendarYearOPEN cYearfetch cYear into @yearwhile @@fetch_status=0beginupdate 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 @yearendclose cYear/*1st Jan New Year's DayLast Monday in May Memorial Day4th Jul Independence DayFirst Monday in September Labor DayFourth Thursday in November Thanksgiving Day25th Dec Christmas Day*/ Kevin Bernard SSC Journeyman Group: General Forum Members Points: 88 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 ActualCountFROM LogBaseWhere CreationDate between @fromDate and @toDate group by CreationDate, DATENAME(month, CreationDate),DATENAME(dw, CreationDate)) as agroup by MonthName, Dayname ChrisM@Work SSC Guru Group: General Forum Members Points: 96091 Visits: 20659 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 ShawFor 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 ModenExploring Recursive CTEs by Example Dwain Camps