Death Days SQL Count

Question

I have a SQL Server table that contains a list of all employees and their illness.

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

The problem is that some people may have been sick for a year, so EG FROMDATE may be 2013-12-31, and UNTILDATE may be 2014-12-31 (1 year sick leave). However, he should only consider days from this disease that occur in the current quarter. Thus, it should be about 90 days of illness, and not count all 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 is required from any of the dates that is correct, since I need to take into account people who were already sick before the quarter began, but who is still sick, coming into the current quarter, but should only take into account the number of days from which The quarter began before the end of the quarter.

Any help would be greatly appreciated.

Answer 1

With the date table, you can easily find the number of dates in which the date is between your two dates of interest, and where there is a vacation period that surrounds it. You can also filter your dates to exclude non-working days and holidays.

There are many ways to create such a date table, and many are described in both stackoverflow and dba.stackexchange.

Answer 2

 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 Concrete

 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' 

Answer 3

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

Try it,

  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' 

Answer 4

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

I will summarize your question a bit. Instead of looking only at the current quarter, let you have two parameters that determine the range of dates of interest to you:

 DECLARE @ParamStartDate date; DECLARE @ParamEndDate date; 

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 

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

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

The intersection period cannot be greater than the specified date range (from @ParamStartDate to @ParamEndDate ).

The intersection period cannot be longer than the duration of the disease ( FromDate to UntilDate ).

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

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

Finally, the duration of the crossing in days

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

But, only if it is positive. If it is negative, it means that the period of the illness ended before the quarter began (or the illness began after the end of the quarter).

There are no built-in MIN, MAX functions that take two parameters as needed, so I use CROSS APPLY to calculate them. In addition, I calculate the number of days in a given quarter, just for completeness. The final request 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 the duration of one day if the start and end dates of the period are the same.