Working days and calendar table

building reports desktopintelligence
13

Don’t get me started LOL :mrgreen:

Have you seen this:-

https://bobj-board.org/t/104238

That will give you working days, but not public holidays - Desktop solution.

To be honest this is a very difficult / impossible thing to do in SQL.

What we did was we used quite complex SQL in an object, that did the number of working days calc between the two dates, then subtracted a DB level function that tested between those same dates for number of public holidays.

This is the SQL for the number of working days between minus the holidays function:-

Case When Datepart( dw,dbo.Start.StartDate) = 7 Then
    Case When Datepart( dw, dbo.End.EndDate) = 7 Then 
             Case When
                 (7 - Datepart( dw, dbo.Start.StartDate) ) + ((Datediff( week, dbo.Start.StartDate, dbo.End.EndDate) - 1) * 5 ) +(Datepart( dw,dbo.End.EndDate)-2)-dbo.GetHolidayDays(dbo.Start.StartDate,dbo.End.EndDate)< 0 
                       Then 0
              Else
                   (7 - Datepart( dw, dbo.Start.StartDate) ) + ((Datediff( week, dbo.Start.StartDate, dbo.End.EndDate) - 1) * 5 ) + (Datepart( dw,dbo.End.EndDate)-2)-dbo.GetHolidayDays(dbo.Start.StartDate,dbo.End.EndDate) 
               End
     Else
          Case When
                    (7 - Datepart( dw, dbo.Start.StartDate) ) + ((Datediff( week, dbo.Start.StartDate, dbo.End.EndDate) - 1) * 5 ) + (Datepart( dw,dbo.End.EndDate) - 1)-dbo.GetHolidayDays(dbo.Start.StartDate,dbo.End.EndDate) < 0 
           Then 0 
           Else
               (7 - Datepart(dw, dbo.Start.StartDate) ) + ((Datediff( week, dbo.Start.StartDate, dbo.End.EndDate) - 1) * 5 ) + (Datepart( dw,dbo.End.EndDate) - 1)-dbo.GetHolidayDays(dbo.Start.StartDate,dbo.End.EndDate) 
          End   
    End
Else
     Case When Datepart(dw, dbo.End.EndDate) = 7 
          Then
          Case When (7 - Datepart( dw, dbo.Start.StartDate)-1 ) + ((Datediff( week, dbo.Start.StartDate, dbo.End.EndDate) - 1) * 5 ) + (Datepart(dw, dbo.End.EndDate) - 2)-dbo.GetHolidayDays(dbo.Start.StartDate,dbo.End.EndDate)<0 
                      Then 0 
                         Else (7 - Datepart( dw, dbo.Start.StartDate)-1 ) + ((Datediff( week, dbo.Start.StartDate, dbo.End.EndDate) - 1) * 5 ) + (Datepart(dw, dbo.End.EndDate) - 2)-dbo.GetHolidayDays(dbo.Start.StartDate,dbo.End.EndDate) 
                          End
       Else
       Case When
                 (7 - Datepart(dw, dbo.Start.StartDate) -1) + ((Datediff( week, dbo.Start.StartDate, dbo.End.EndDate) - 1) * 5 ) + (Datepart( dw,dbo.End.EndDate) - 1)-dbo.GetHolidayDays(dbo.Start.StartDate,dbo.End.EndDate)<0 
                 Then 0 
                     Else (7 - datepart(dw, dbo.Start.StartDate) -1) + ((datediff( week, dbo.Start.StartDate, dbo.End.EndDate) - 1) * 5 ) + (Datepart(dw,dbo.End.EndDate) - 1)-dbo.GetHolidayDays(dbo.Start.StartDate,dbo.End.EndDate) 
                  End 
        End
End

dbo.GetHolidayDays is the subtracted function, unfortunately I have lost that somewhere, ask your DBA for help!

Mak 1 :uk: (BOB member since 2005-01-06)