0

For this month (Jan) the working days excluding bank holidays is 20. Excluding bank holidays would take this to 19. I'm happy with this.

NOTE: I've manually added new years day in this example, I will be using a table which specifically has all bank holidays stored

This becomes troublesome when a month has multiple bank holidays (May). Because this feeds a daily run report, the bank holiday can't be excluded until the date has been reached/passed. (The report runs each day showing the previous day - e.g runs on the 2nd showing the 1st)

Using May as the example:

BH = 8th,25th

Report runs on the 7th showing 4 Working days

Report runs on the 8th showing 5 Working days

Report runs on the 11th showing 5 Working days

Report runs on the 22nd showing 14 Working days

Report runs on the 25th showing 15 Working days (Excludes this and previous BH)

Report runs on the 26th showing 15 Working days

Current Query

CODE:

Declare @ReportedDays int
Declare @StartDate datetime
Declare @Enddate datetime
Declare @Days int
Declare @BankHoliday date
Declare @Working int

Set @ReportedDays = DAY(GETDATE())
Set @StartDate = DATEADD(DAY,(DATEPART(DAY,GETDATE())-1)*(-1),GETDATE())
Set @Enddate = GETDATE()
Set @BankHoliday = '2020-01-01'


set @Days = 
    datediff(dd, @StartDate, @EndDate) - (datediff(wk, @StartDate, @EndDate) * 2) -
    case when datepart(dw, @StartDate) = 1 then 1 else 0 end +
    case when datepart(dw, @EndDate) = 1 then 1 else 0 end

Set @Working = CASE when GETDATE() >= @BankHoliday then @Days - 1 Else @Days end

Select

@Working as 'Excluding Bank Holidays',
@Days as 'Excluding Weekends',
@ReportedDays 'Current(DAY)Date'

From Table1
M.tovey
  • 31
  • 3
  • Edit your question and post the code *as text* in the question. – Gordon Linoff Jan 29 '20 at 11:38
  • 2
    Would it be easier for you if you had another table with all the bank holidays in? It is a common technique, e.g. [Creating a date dimension or calendar table in SQL Server](https://www.mssqltips.com/sqlservertip/4054/creating-a-date-dimension-or-calendar-table-in-sql-server/). – Andrew Morton Jan 29 '20 at 11:54
  • A calendar table is exactly what you need here. – Thom A Jan 29 '20 at 11:55
  • another vote here for calendar table – SE1986 Jan 29 '20 at 12:08
  • Have a look at this: https://stackoverflow.com/questions/457176/how-do-i-determine-a-public-holiday-in-sql-server – Nicola Lepetit Jan 29 '20 at 13:13
  • @AndrewMorton I thought needing a calendar table would be the case. but the problem of having my Working variable minus a bank holiday only when it's passed, easy enough to do for one, not sure for 2 BHs – M.tovey Jan 29 '20 at 14:24
  • @M.tovey I imagine you could subtract the `COUNT` of bank holidays in the appropriate period. – Andrew Morton Jan 29 '20 at 14:40

0 Answers0