sql - multiple - right join dplyr

SQL Count sickness days

Question

Tengo una tabla de SQL Server que contiene la lista de todo el personal y su enfermedad.

Necesito poder calcular cuántos días han estado enfermos en el trimestre actual

El problema es que algunas personas pueden haber estado enfermas durante un año, por lo tanto, EG FROMDATE podría ser 2013-12-31 y UNTILDATE podría ser 2014-12-31 (1 año de baja por enfermedad). Sin embargo, solo debería contar los días desde esa enfermedad que ocurren en el trimestre actual. Por lo tanto, debería ser de alrededor de 90 días de enfermedad en lugar de contar todo el año.

SQL actual

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''

Así que en este momento, se necesita una fecha correcta, ya que necesito contabilizar a las personas que ya estaban enfermas antes del trimestre pero todavía enfermas en el trimestre actual, pero solo debe contar el número de días desde que comenzó el trimestre hasta al final del trimestre.

Cualquier ayuda sería muy apreciada.

Answer 1

Con una tabla de fechas, puede encontrar fácilmente el recuento de fechas donde la fecha se encuentra entre sus dos fechas de interés y donde existe un período de permiso que la rodea. También puede filtrar sus fechas para excluir días no laborables y días festivos.

Hay muchas maneras de generar una tabla de fechas así, y muchas descripciones en stackoverflow y dba.stackexchange.

Answer 2

No estoy seguro acerca de sus columnas. Solo debe proporcionar SQL que proporcione registros entre 2013-12-31 y 2014-12-31 y luego pregunte su problema.

Prueba esto,

 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 3

Tener una tabla de Calendario con la lista de todas las fechas posibles es útil, pero en este caso podemos prescindir de ella.

Voy a generalizar tu pregunta un poco. En lugar de mirar solo el trimestre actual, tengamos dos parámetros que definan el rango de fechas que le interesan:

DECLARE @ParamStartDate date;
DECLARE @ParamEndDate date;

Al principio necesitamos obtener todas las filas de Absence que tengan un rango de FromDate a UntilDate que se cruza con el período dado.

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

Dos períodos A y B se superponen cuando (StartA <= EndB) y (EndA >= StartB) .

Entonces tenemos que calcular cuántos días hay en la intersección de los dos períodos.

El período de intersección no puede ser mayor que el rango de fechas dado ( @ParamStartDate to @ParamEndDate ).

El período de intersección no puede ser mayor que la duración de la enfermedad ( FromDate to UntilDate ).

Entonces, el comienzo de la intersección es el último de FromDate y @ParamStartDate , es decir, MAX(FromDate, @ParamStartDate)

El final de la intersección es el más temprano de UntilDate y @ParamEndDate , es decir, MIN(UntilDate, @ParamEndDate)

Finalmente, la duración de la intersección en días es

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

Pero, solo si es positivo. Si es negativo, significa que el período de enfermedad finalizó antes de que comenzara el trimestre dado (o la enfermedad comenzó después de que finalizó el trimestre dado).

No hay funciones integradas de MIN, MAX que tomen dos parámetros cuando lo necesito, entonces uso CROSS APPLY para calcularlos. Además, calculo el número de días en el trimestre dado, solo para completarlo. La consulta final se ve así:

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

Agrego 1 a DATEDIFF para obtener una duración de un día si las fechas de inicio y finalización del período son las mismas.

Answer 4

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''