Calcul des absences consécutives en SQL

J'ai besoin de calculer tous les employés qui ont X nombre d'absences consécutives dans une plage de dates en SQL.

Nous avons un tableau des absences avec un logging pour chaque jour d'absence d'un employé et un tableau de calendar avec les jours de travail pour l'année.

tblAbsences EmployeeID int AbsenceDate datetime tblCalendar WorkDay datetime 

Est-ce que quelqu'un a des idées sur la façon de calculer les absences consécutives? Exemple: Tous les employés qui ont 3 absences consécutives entre le 1/1/2009 et le 3/1/2009.

Cela devrait fonctionner pour vous. GROUP BY sur ConsecDates pour find qui était absent plus de X nombre de fois.

 select a.*, ( select min(b.absenceDate) from tblAbsences b where a.employeeId = b.employeeId and b.absenceDate >= a.absenceDate and not exists ( select 1 from tblabsences c where c.employeeId = b.employeeId and dateadd( dd, 1, b.absenceDate) = c.absenceDate ) ) ConsecDates from dbo.tblAbsences a order by a.AbsenceDate asc 

Testé dans PostgreSQL; SQL utilise des exemples de valeurs de publication.

Aucune key primaire n'est définie dans les tables fournies et le code ci-dessous en tient count. Mieux serait d'append des keys primaires et d'optimiser le code ci-dessous pour les exploiter: une meilleure qualité des données, une meilleure performance, un code plus propre, des gens plus heureux.

La suppression du préfixe tbl offre plus de flexibilité dans l'implémentation de la database. La table, la vue et le synonyme peuvent être utilisés de manière interchangeable sans impact sur le code qui fait reference à l'object de database ou à la convention de dénomination.

 /* period length as the desired number of consecutive days */ /* time window as the period to be analyzed */ SELECT DISTINCT /* Consolidate employees with multiple periods */ a.employeeid FROM (SELECT /* Generate all possible periods */ pk_c.workday begin_date, /* End date for given period length; less one for closed boundaries */ LEAD(pk_c.workday,3-1,NULL) OVER (ORDER BY pk_c.workday) end_date FROM (SELECT DISTINCT /* No calendar PK, remove dupes; if PK, pull in-line view up */ c.workday FROM sandbox.calendar c) pk_c ) p INNER JOIN sandbox.absences a ON /* Match absences with periods */ (a.absencedate BETWEEN p.begin_date AND p.end_date) WHERE /* In time window desired; exclude periods extending beyond boundaries */ (p.begin_date BETWEEN '2009-01-01' AND '2009-03-01' AND /* NOT NULL exclusion implied for periods beyond calendar boundaries */ p.end_date BETWEEN '2009-01-01' AND '2009-03-01') GROUP BY a.employeeid, /* Also group period, display only employee */ p.begin_date HAVING /* Number of absence days to match to the period length */ /* DISTINCT due to missing absences PK; if PK, drop DISTINCT */ COUNT(DISTINCT a.absencedate) = 3 ; 

Prendre plaisir. Version dépouillée ci-dessous:

 SELECT DISTINCT a.employeeid FROM (SELECT pk_c.workday begin_date, LEAD(pk_c.workday,3-1,NULL) OVER (ORDER BY pk_c.workday) end_date FROM (SELECT DISTINCT c.workday FROM sandbox.calendar c) pk_c) p INNER JOIN sandbox.absences a ON (a.absencedate BETWEEN p.begin_date AND p.end_date) WHERE (p.begin_date BETWEEN '2009-01-01' AND '2009-03-01' AND p.end_date BETWEEN '2009-01-01' AND '2009-03-01') GROUP BY a.employeeid, p.begin_date HAVING COUNT(DISTINCT a.absencedate) = 3 ;