SQL - Count working days with an exception

1
votes

I am using this sql code to count the working days without weekends and holidays. How can I subtract the birthday date if it's between the sDate and eDate for every event type different from 'B' for birthday? 

SELECT
   evt.[Name],  
   evt.[type],
   evt.sDate,
   evt.eDate,
   DATEDIFF(DD, evt.sDate, evt.eDate) + 1
   - (DATEDIFF(WK, evt.sDate, evt.eDate) * 2) 
   - CASE WHEN DATEPART(DW, evt.sDate) = 1 THEN 1 ELSE 0 END 
   + CASE WHEN DATEPART(DW, evt.eDate) = 1 THEN 1 ELSE 0 END 
   - (SELECT COUNT(*) FROM tblHolidays  AS h WHERE h.Data BETWEEN evt.sDate AND evt.eDate) as date_diff
   -- subtract birthday date if between sDate and eDate
   FROM tblEvents AS evt
   WHERE YEAR(sDate) = YEAR(getdate())
   AND MONTH(sDate) = 12
   ORDER BY evt.[Name]

Table of Events: The column Birthday shows if the value should be subtracted from date_diff

enter image description here Regards, Elio Fernandes

2
sql-server
Tag the dbms you're using. That query is product specific. – jarlh
What is the format of the desired output? – kchason
I just replaced the table to add the column 'date_diff_final'. The columns names required are the light blue ones but the values of date_diff (actual) should be equal to date_diff_final (without the birthday). I hope I am being clear! – Elio Fernandes

2 Answers

0
votes

using a left join and a case expression:

select
   evt.[Name],  
   evt.[type],
   evt.sDate,
   evt.eDate,
   datediff(day, evt.sDate, evt.eDate) + 1
   - (datediff(week, evt.sDate, evt.eDate) * 2) 
   - case when datepart(weekday, evt.sDate) = 1 then 1 else 0 end 
   + case when datepart(weekday, evt.eDate) = 1 then 1 else 0 end 
   - (select count(*) from tblHolidays  as h where h.Data between evt.sDate and evt.eDate) as date_diff
   -- subtract birthday date if between sDate and eDate
   - case when b.sDate is not null then 1 else 0 end
from tblEvents as evt
  left join tblEvents as b
    on evt.Name = b.Name  -- Hopefully there is a better identifier than Name, like an EmpId
   and evt.Type != 'B'
   and b.Type = 'B'
   and b.sDate >= evt.sDate
   and b.sDate <= evt.eDate
where --year(evt.sDate) = year(getdate())
  --and month(evt.sDate) = 12
  /* instead of using functions, use an appropriate date range */
  evt.SDate >= '20171201'
  and evt.SDate < '20180101'
order by evt.[Name]
0
votes

Use a CASE statement in place of your "subtract birthday" comment to return a 1 or a 0 to be subtracted as part of your date_diff column calculation.

It's not clear to me if you have a birthdate column available or not, but the conditions you want to check aren't the key to the solution, so substitute whatever conditions you need to check inside the WHEN block to get to a 1 or a 0 as needed for your calculation. If you have a birthdate column available, it might look something like this:

SELECT CASE 
WHEN evt.Type != 'B' 
AND CAST(CONCAT(month(birthdate), '/', day(birthdate), '/', year(getdate())) AS date) BETWEEN evt.sDate AND evt.eDate 
THEN 1 ELSE 0 END

That will need parens around it when embedded in your SELECT clause.

Referencing just the evt.Type column, it gets simpler:

SELECT
   evt.[Name],  
   evt.[type],
   evt.sDate,
   evt.eDate,
   DATEDIFF(DD, evt.sDate, evt.eDate) + 1
   - (DATEDIFF(WK, evt.sDate, evt.eDate) * 2) 
   - CASE WHEN DATEPART(DW, evt.sDate) = 1 THEN 1 ELSE 0 END 
   + CASE WHEN DATEPART(DW, evt.eDate) = 1 THEN 1 ELSE 0 END 
   - (SELECT COUNT(*) FROM tblHolidays  AS h WHERE h.Data BETWEEN evt.sDate AND evt.eDate) 
   - CASE WHEN evt.Type != 'B' THEN 1 ELSE 0 END as date_diff
   FROM tblEvents AS evt
   WHERE YEAR(sDate) = YEAR(getdate())
   AND MONTH(sDate) = 12
   ORDER BY evt.[Name]