SQL Count sickness days

Question

I have a SQL Server table which contains the list of all staff and their sickness.

I need to be able to calculate how many days they have had sick in the current quarter

The issue is, some people may have been sick for a year so, E.G the FROMDATE could be 2013-12-31 and the UNTILDATE could be 2014-12-31 (1 year sickness leave). However it should only count the days from that sickness that occur in the current quarter. So it should be around 90 days of sickness rather than count the entire year.

Current SQL

select SUM(a.WORKDAYS) as Total
from ABSENCE a
where a.FROMDATE < GETDATE() and 

a.UNTILDATE > DATEADD(MONTH, -3, GETDATE())
and
a.ABS_REASON='SICK'

So at the moment, it takes from any fromdate which is correct as I need to account for people who were already sick before the quarter started but still sick going into the current quarter but should only count the number of days from when the quarter started until the end of the quarter.

Any help would be greatly appreciated.

Answer 1

With a table of dates, you could easily find the count of dates where the date is between your two dates of interest, and where there exists a leave period that surrounds it. You could also filter your dates to exclude non-business days and public holidays.

There are lots of ways to generate such a table of dates, and plenty described both on stackoverflow and dba.stackexchange.