none
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

Answers

  • 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