# How do I find the number of business days in the month to date?

### Question

• Hello,

SQL 2008 R2

I need a query that gives me the number of trading days that have been since the start of the current month.

I have a 'month - to - date' report that gives me a total \$ figure. Now I want an average per day from that, which means an average across the days that data has come from.

Can anyone guide me?

Cheers.

Tuesday, August 13, 2013 8:21 PM

• You could get the number of weekdays with a function like this:

```-- =============================================
-- Author:		David At Work (Live ID)
-- Create date: 	8-13-2013
-- Description:		Gets the number of weekdays between two dates
-- =============================================
Create FUNCTION GetWeekdays
(
-- Add the parameters for the function here
@startDate date,
@endDate date
)
RETURNS int
AS
BEGIN

declare @full_weeks int,@remaining_days int,@work_days_in_full_weeks int,@startingWeekday int, @work_days_in_partial_week int

set @full_weeks = (datediff(d,@startDate,@endDate)+1)/7
set @remaining_days = (datediff(d,@startDate,@endDate)+1)%7
set @work_days_in_full_weeks = @full_weeks*5
set @startingWeekday = datepart(dw,@startDate)
set @work_days_in_partial_week = @remaining_days - case when @startingWeekday = 1 then 1 when @startingWeekday = 7 then 2 when @remaining_days+@startingWeekday-1=7 then 1 when @remaining_days+@startingWeekday-1>=8 then 2 else 0 end

set @work_days_in_partial_week=case when @work_days_in_partial_week<0 then 0 else @work_days_in_partial_week end

return @work_days_in_full_weeks+@work_days_in_partial_week

END
GO

select dbo.GetWeekdays('8/1/2013','8/31/2013') --expect 22
select dbo.GetWeekdays('8/2/2013','8/31/2013') --expect 21
select dbo.GetWeekdays('8/3/2013','8/31/2013') --expect 20
select dbo.GetWeekdays('8/4/2013','8/31/2013') --expect 20
select dbo.GetWeekdays('8/5/2013','8/31/2013') --expect 20
select dbo.GetWeekdays('8/6/2013','8/31/2013') --expect 19
select dbo.GetWeekdays('8/5/2013','8/14/2013') --expect 8```

There are probably more elegant solutions than this (ones that don't require all the case statements I used) but at least this works.

This will give you the weekdays between two dates but to get trading days you will have to do something like create a table of all weekdays that are not trading days (all vacation days) and subtract the count of those that exist between the dates.

Tuesday, August 13, 2013 11:54 PM

### All replies

• You could get the number of weekdays with a function like this:

```-- =============================================
-- Author:		David At Work (Live ID)
-- Create date: 	8-13-2013
-- Description:		Gets the number of weekdays between two dates
-- =============================================
Create FUNCTION GetWeekdays
(
-- Add the parameters for the function here
@startDate date,
@endDate date
)
RETURNS int
AS
BEGIN

declare @full_weeks int,@remaining_days int,@work_days_in_full_weeks int,@startingWeekday int, @work_days_in_partial_week int

set @full_weeks = (datediff(d,@startDate,@endDate)+1)/7
set @remaining_days = (datediff(d,@startDate,@endDate)+1)%7
set @work_days_in_full_weeks = @full_weeks*5
set @startingWeekday = datepart(dw,@startDate)
set @work_days_in_partial_week = @remaining_days - case when @startingWeekday = 1 then 1 when @startingWeekday = 7 then 2 when @remaining_days+@startingWeekday-1=7 then 1 when @remaining_days+@startingWeekday-1>=8 then 2 else 0 end

set @work_days_in_partial_week=case when @work_days_in_partial_week<0 then 0 else @work_days_in_partial_week end

return @work_days_in_full_weeks+@work_days_in_partial_week

END
GO

select dbo.GetWeekdays('8/1/2013','8/31/2013') --expect 22
select dbo.GetWeekdays('8/2/2013','8/31/2013') --expect 21
select dbo.GetWeekdays('8/3/2013','8/31/2013') --expect 20
select dbo.GetWeekdays('8/4/2013','8/31/2013') --expect 20
select dbo.GetWeekdays('8/5/2013','8/31/2013') --expect 20
select dbo.GetWeekdays('8/6/2013','8/31/2013') --expect 19
select dbo.GetWeekdays('8/5/2013','8/14/2013') --expect 8```

There are probably more elegant solutions than this (ones that don't require all the case statements I used) but at least this works.

This will give you the weekdays between two dates but to get trading days you will have to do something like create a table of all weekdays that are not trading days (all vacation days) and subtract the count of those that exist between the dates.

Tuesday, August 13, 2013 11:54 PM
• I should have tried typing your question into google before taking the time to write that function.  It looks like someone else posted that more elegant solution I referred to on stackoverflow a few years ago (this forum says it won't let me post links because my account is not authorized for links).

Wednesday, August 14, 2013 12:09 AM