Requête SQL pour la présence de time pour un mois

C'est un problème assez difficile à résoudre avec SQL en raison de la nécessité de find des transitions et des plages dans datatables, ce qui n'est pas sortingvial. J'ai brisé le problème dans une série d'étapes de cte successives qui se construisent l'une sur l'autre et mènent à une solution de travail finale:

Tout d'abord, j'ajoute un index de ligne aux données pour fournir un PK simple pour identifier une ligne unique:

 with NumberedAtt as ( select row_number() over (partition by [user] order by date, time, reader) as ix, att.[user], att.[department], att.[date] + att.[time] as dt, att.[reader] from att ) 

Ensuite, je prends la première et la dernière valeur d'index par user qui sera utilisée pour les limites extrêmes de chaque plage d'input / sortie:

 , MinMax as ( select [user], min(ix) ixMin, max(ix) ixMax from NumberedAtt N group by [user] ) 

Ensuite, je les associe pour générer une list de toutes les plages de sortie / input, qui sont les points où la valeur de Reader change de 2 à 1 . Ce sont les points spécifiques qui s'identifient exactement lorsqu'une plage de time précédente se termine et la prochaine plage de time commence (et gère proprement les lectures d'input / sortie en double successives). En combinant ceci avec la première input et le dernier time de sortie pour chaque user, une list de toutes les transitions d'input / sortie est générée:

 , Transitions as ( select N.[User], 0 as exitIx, M.ixMin as entryIx from NumberedAtt N join MinMax M on N.[User] = M.[User] where N.ix = M.ixMin union select N.[User], M.ixMax as exitIx, 0 as entryIx from NumberedAtt N join MinMax M on N.[User] = M.[User] where N.ix = M.ixMax union select A1.[User], A1.ix as exitIx, A2.ix as entryIx from NumberedAtt A1 join NumberedAtt A2 on A1.ix + 1 = A2.ix and A1.[user] = A2.[user] where A1.[reader] = 2 and A2.[reader] = 1 ) 

Voici la sortie à ce stade:

 | USER | EXITIX | ENTRYIX | |------|--------|---------| | A1 | 0 | 1 | | A1 | 2 | 3 | | A1 | 4 | 0 | | B1 | 0 | 1 | | B1 | 3 | 0 | 0 | | USER | EXITIX | ENTRYIX | |------|--------|---------| | A1 | 0 | 1 | | A1 | 2 | 3 | | A1 | 4 | 0 | | B1 | 0 | 1 | | B1 | 3 | 0 | 1 | | USER | EXITIX | ENTRYIX | |------|--------|---------| | A1 | 0 | 1 | | A1 | 2 | 3 | | A1 | 4 | 0 | | B1 | 0 | 1 | | B1 | 3 | 0 | 2 | | USER | EXITIX | ENTRYIX | |------|--------|---------| | A1 | 0 | 1 | | A1 | 2 | 3 | | A1 | 4 | 0 | | B1 | 0 | 1 | | B1 | 3 | 0 | 3 | | USER | EXITIX | ENTRYIX | |------|--------|---------| | A1 | 0 | 1 | | A1 | 2 | 3 | | A1 | 4 | 0 | | B1 | 0 | 1 | | B1 | 3 | 0 | 4 | | USER | EXITIX | ENTRYIX | |------|--------|---------| | A1 | 0 | 1 | | A1 | 2 | 3 | | A1 | 4 | 0 | | B1 | 0 | 1 | | B1 | 3 | 0 | 0 | | USER | EXITIX | ENTRYIX | |------|--------|---------| | A1 | 0 | 1 | | A1 | 2 | 3 | | A1 | 4 | 0 | | B1 | 0 | 1 | | B1 | 3 | 0 | 0 | | USER | EXITIX | ENTRYIX | |------|--------|---------| | A1 | 0 | 1 | | A1 | 2 | 3 | | A1 | 4 | 0 | | B1 | 0 | 1 | | B1 | 3 | 0 | 1 | | USER | EXITIX | ENTRYIX | |------|--------|---------| | A1 | 0 | 1 | | A1 | 2 | 3 | | A1 | 4 | 0 | | B1 | 0 | 1 | | B1 | 3 | 0 | 

Notez que nous avons soigneusement capturé tous les index des lignes où une gamme de time commence et se termine. Cependant, ils sont décalés par un – c'est-à-dire que le time d'input dans une rangée correspond au time de sortie dans la rangée suivante. Nous avons donc besoin d'une transformation supplémentaire pour rapprocher les gammes en ajoutant un index de ligne à cette table et en joignant chaque ligne avec la ligne suivante:

 , NumberedTransitions as ( select row_number() over (partition by [User] order by exitIx) tix, T.* from Transitions T ), EntryExit as ( select aEntry.ix as ixEntry, aExit.ix as ixExit, aEntry.[user], aEntry.[department], aEntry.[dt] as entryDT, aExit.[dt] as exitDT from NumberedTransitions tEntry join NumberedAtt aEntry on tEntry.entryIx = aEntry.ix and tEntry.[user] = aEntry.[user] join NumberedTransitions tExit on tEntry.tix + 1 = tExit.tix and tEntry.[user] = tExit.[user] join NumberedAtt aExit on tExit.exitIx = aExit.ix and tExit.[user] = aExit.[user] ) 

Après avoir rejoint les gammes successives set, je retrouve datatables détaillées d'origine, car je n'ai travaillé qu'avec les valeurs de l'indice de ligne jusqu'à présent. À la fin de cette sous-requête, nous avons identifié toutes les plages d'input / sortie par user et "englouti" toutes les lectures multiples:

 | IXENTRY | IXEXIT | USER | DEPARTMENT | ENTRYDT | EXITDT | |---------|--------|------|------------|------------------------------|------------------------------| | 1 | 2 | A1 | IT | March, 01 2014 11:12:00+0000 | March, 01 2014 13:12:00+0000 | | 3 | 4 | A1 | IT | March, 02 2014 07:42:15+0000 | March, 02 2014 16:16:15+0000 | | 1 | 3 | B1 | IT | March, 01 2014 12:28:06+0000 | March, 01 2014 13:28:06+0000 | 1 | | IXENTRY | IXEXIT | USER | DEPARTMENT | ENTRYDT | EXITDT | |---------|--------|------|------------|------------------------------|------------------------------| | 1 | 2 | A1 | IT | March, 01 2014 11:12:00+0000 | March, 01 2014 13:12:00+0000 | | 3 | 4 | A1 | IT | March, 02 2014 07:42:15+0000 | March, 02 2014 16:16:15+0000 | | 1 | 3 | B1 | IT | March, 01 2014 12:28:06+0000 | March, 01 2014 13:28:06+0000 | 2 | | IXENTRY | IXEXIT | USER | DEPARTMENT | ENTRYDT | EXITDT | |---------|--------|------|------------|------------------------------|------------------------------| | 1 | 2 | A1 | IT | March, 01 2014 11:12:00+0000 | March, 01 2014 13:12:00+0000 | | 3 | 4 | A1 | IT | March, 02 2014 07:42:15+0000 | March, 02 2014 16:16:15+0000 | | 1 | 3 | B1 | IT | March, 01 2014 12:28:06+0000 | March, 01 2014 13:28:06+0000 | 3 | | IXENTRY | IXEXIT | USER | DEPARTMENT | ENTRYDT | EXITDT | |---------|--------|------|------------|------------------------------|------------------------------| | 1 | 2 | A1 | IT | March, 01 2014 11:12:00+0000 | March, 01 2014 13:12:00+0000 | | 3 | 4 | A1 | IT | March, 02 2014 07:42:15+0000 | March, 02 2014 16:16:15+0000 | | 1 | 3 | B1 | IT | March, 01 2014 12:28:06+0000 | March, 01 2014 13:28:06+0000 | 4 | | IXENTRY | IXEXIT | USER | DEPARTMENT | ENTRYDT | EXITDT | |---------|--------|------|------------|------------------------------|------------------------------| | 1 | 2 | A1 | IT | March, 01 2014 11:12:00+0000 | March, 01 2014 13:12:00+0000 | | 3 | 4 | A1 | IT | March, 02 2014 07:42:15+0000 | March, 02 2014 16:16:15+0000 | | 1 | 3 | B1 | IT | March, 01 2014 12:28:06+0000 | March, 01 2014 13:28:06+0000 | 

Maintenant, il ne rest plus qu'à regrouper datatables set pour indiquer le nombre total d'heures par user, par mois. Il est un peu difficile de calculer le nombre total d'heures, mais cela peut se faire en prenant la sum de minutes entre les plages puis en convertissant le résultat en une valeur de time:

 , Hours as ( select [User], [Department], Year(EntryDT) Year, Month(EntryDT) Month, RIGHT('0' + CAST(SUM(DATEDIFF(Minute, EntryDT, ExitDT)) / 60 as varchar(10)), 2) + ':' + RIGHT('0' + CAST(SUM(DATEDIFF(Minute, EntryDT, ExitDT)) % 60 as varchar(2)), 2) as TotalHours from EntryExit EE group by [User], [Department], Year(EntryDT), Month(EntryDT) ) 

Cela donne un résultat final très proche du résultat souhaité:

 | USER | DEPARTMENT | YEAR | MONTH | TOTALHOURS | |------|------------|------|-------|------------| | A1 | IT | 2014 | 3 | 10:34:00 | | B1 | IT | 2014 | 3 | 01:00:00 | ANNÉE | | USER | DEPARTMENT | YEAR | MONTH | TOTALHOURS | |------|------------|------|-------|------------| | A1 | IT | 2014 | 3 | 10:34:00 | | B1 | IT | 2014 | 3 | 01:00:00 | MOIS | | USER | DEPARTMENT | YEAR | MONTH | TOTALHOURS | |------|------------|------|-------|------------| | A1 | IT | 2014 | 3 | 10:34:00 | | B1 | IT | 2014 | 3 | 01:00:00 | 3 | | USER | DEPARTMENT | YEAR | MONTH | TOTALHOURS | |------|------------|------|-------|------------| | A1 | IT | 2014 | 3 | 10:34:00 | | B1 | IT | 2014 | 3 | 01:00:00 | 

Quelques réglages pourraient être apportés au formatting tel que désiré, mais cela devrait être facile à build sur ce cadre.

Voici une démonstration de travail: http://www.sqlfiddle.com/#!3/f3f37/7