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.

You do need all absences that started before the end of the quarter and finished after the start of the quarter (or are `NULL` if you don't know when they're coming back yet), so your `WHERE` clause is correct (unless you do need to cater for `NULL` `UNTILDATE`). Then you need to count only the days within the current quarter and the absence range, and for that you need to know what dates are work days, as suggested by Rob Farley. — Mark Hurd, Dec 16 '14 at 11:29

score 1 · Answer 1 · answered Dec 16 '14 at 10:49

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.

Matt · Answer 2 · 2014-12-16T11:30:03.583

0

SELECT SUM(a.WORKDAYS) as Total
FROM ABSENCE a
WHERE (a.FROMDATE >= DATEADD(MONTH, -3, GETDATE()) OR a.UNTILDATE >= DATEADD(MONTH, -3, GETDATE()))
AND a.ABS_REASON = 'SICK'

Quarter Specific

SELECT SUM(a.WORKDAYS) as Total
FROM ABSENCE a
WHERE (a.FROMDATE >= DATEADD(quarter, -1, GETDATE()) OR a.UNTILDATE >= DATEADD(quarter, -1, GETDATE()))
AND a.ABS_REASON = 'SICK'

edited Dec 16 '14 at 11:30

answered Dec 16 '14 at 10:49

Matt

14,906
27
99
149

1

This wouldnt work as the from date could have been over a year ago if someone is long term sick. It should just count the number of days sick in the range in the current quarter – David Hayward Dec 16 '14 at 11:00
1

The logic is to only count the sick days from the last 3 months? – Matt Dec 16 '14 at 11:05

score 0 · Answer 3 · answered Dec 16 '14 at 11:20

Not sure about your columns.you should only provide sql that gives records between 2013-12-31 and 2014-12-31 and then ask your problem .

Try this,

 select SUM(Case when datepart(MM, a.FROMDATE) IN (10,11,12) Then a.WORKDAYS Else  End) 
as Total
   from ABSENCE a
   where a.FROMDATE >= '2013-12-31' and 
   a.UNTILDATE <= '2014-12-31'
   and
   a.ABS_REASON='SICK'

score 0 · Answer 4 · answered Dec 16 '14 at 12:47

Having a Calendar table with the list of all possible dates is handy, but in this case we can do without it.

I'll generalize your question a bit. Instead of looking just at the current quarter let's have two parameters that define the range of dates that you are interested in:

DECLARE @ParamStartDate date;
DECLARE @ParamEndDate date;

At first we need to get all rows from Absence that have a range from FromDate to UntilDate that intersects with the given period.

SELECT
    ...
FROM
    Absence
WHERE
    ABS_REASON='SICK'
    -- all absence periods, which overlap with the given period
    AND FromDate <= @ParamEndDate
    AND UntilDate >= @ParamStartDate

Two periods A and B overlap when (StartA <= EndB) and (EndA >= StartB).

Then we need to calculate how many days are in the intersection of the two periods.

The intersection period can't be larger than the given range of dates (@ParamStartDate to @ParamEndDate).

The intersection period can't be larger than the duration of the sickness (FromDate to UntilDate).

So, the beginning of the intersection is the latest of FromDate and @ParamStartDate, i.e. MAX(FromDate, @ParamStartDate)

The ending of the intersection is the earliest of UntilDate and @ParamEndDate, i.e. MIN(UntilDate, @ParamEndDate)

Finally, the duration of the intersection in days is

DATEDIFF(day, MAX(FromDate, @ParamStartDate), MIN(UntilDate, @ParamEndDate))

But, only if it is positive. If it is negative, it means that sickness period ended before the given quarter started (or sickness started after the given quarter ended).

There is no built-in MIN, MAX functions that take two parameters as I need, so I use CROSS APPLY to calculate them. Also, I calculate the number of days in the given quarter, just for completeness. The final query looks like this:

SELECT
    1+DATEDIFF(day, @ParamStartDate, @ParamEndDate) AS QuarterDays
    ,CASE WHEN 1+DATEDIFF(day, CTE_MaxStartDate.AbsenceStartDate, CTE_MinEndDate.AbsenceEndDate) > 0
        THEN 1+DATEDIFF(day, CTE_MaxStartDate.AbsenceStartDate, CTE_MinEndDate.AbsenceEndDate)
        ELSE 0 END AS AbsenceDays
FROM
    Absence
    CROSS APPLY
    (
        SELECT CASE WHEN UntilDate < @ParamEndDate THEN UntilDate ELSE @ParamEndDate END AS AbsenceEndDate
    ) AS CTE_MinEndDate
    CROSS APPLY
    (
        SELECT CASE WHEN FromDate > @ParamStartDate THEN FromDate ELSE @ParamStartDate END AS AbsenceStartDate
    ) AS CTE_MaxStartDate
WHERE
    ABS_REASON='SICK'
    -- all absence periods, which overlap with the given period
    AND FromDate <= @ParamEndDate
    AND UntilDate >= @ParamStartDate

I add 1 to DATEDIFF to get a duration of one day if start and end dates of the period are the same.

@DavidHayward, How did you eventually solve the problem? Were any of the provided answers useful for you? If yes, you can upvote them and accept one answer that was most useful. — Vladimir Baranov, Feb 15 '15 at 09:30

SQL Count sickness days

4 Answers4