Obtenir le nombre total d'heures de travail à partir de la table SQL

Une approche de plus que je pense est simple et efficace.

• Il ne nécessite pas de fonctions modernes comme LEAD
• il fonctionne correctement si la même personne entre et sort plusieurs fois au cours de la même journée
• cela fonctionne correctement si la personne rest plus de minuit ou même plusieurs jours de suite
• cela fonctionne correctement si la période où la personne est "in" chevauche la date de début ou de fin.
• il suppose que datatables sont correctes , c'est-à-dire que chaque "in" est associé à "out", sauf éventuellement le dernier.

Voici une illustration d'une ligne de time. Notez que l'heure de start se produit quand une personne était "dans" et l'heure de end arrive aussi quand une personne était encore "dedans":

Tout ce que nous avons besoin de le faire calculer une sum simple des différences de time entre chaque événement (à la fois in et out ) et l'heure de start , puis faire la même chose pour l'heure de end . Si l'événement est in , la durée ajoutée devrait avoir un signe positif, si l'événement est out , la durée ajoutée devrait avoir un signe négatif. Le résultat final est une différence entre la sum pour l'heure de fin et la sum pour l'heure de début.

 summing for start: |---| + |----------| - |-----------------| + |--------------------------| - |-------------------------------| + --|====|--------|======|------|===|=====|---|==|---|===|====|----|=====|--- time in out in out in start out in out in end out in out summing for end: |---| + |-------| - |----------| + |--------------| - |------------------------| + |-------------------------------| - |--------------------------------------| + |-----------------------------------------------| - |----------------------------------------------------| + 

Je recommand de calculer les durées en minutes, puis split le résultat par 60 pour get des heures, mais cela dépend vraiment de vos besoins. Soit dit en passant, c'est une mauvaise idée de stocker des dates comme NVARCHAR .

 DECLARE @StartDate datetime = '2015-08-01 00:00:00'; DECLARE @EndDate datetime = '2015-09-01 00:00:00'; DECLARE @EmpID nvarchar(6) = NULL; WITH CTE_Start AS ( SELECT EmpID ,SUM(DATEDIFF(minute, (CAST(att.[date] AS datetime) + att.[Time]), @StartDate) * CASE WHEN Funckey = 'EMPIN' THEN +1 ELSE -1 END) AS SumStart FROM PERS_Attendance AS att WHERE (EmpID = @EmpID OR @EmpID IS NULL) AND att.[date] < @StartDate GROUP BY EmpID ) ,CTE_End AS ( SELECT EmpID ,SUM(DATEDIFF(minute, (CAST(att.[date] AS datetime) + att.[Time]), @StartDate) * CASE WHEN Funckey = 'EMPIN' THEN +1 ELSE -1 END) AS SumEnd FROM PERS_Attendance AS att WHERE (EmpID = @EmpID OR @EmpID IS NULL) AND att.[date] < @EndDate GROUP BY EmpID ) SELECT CTE_End.EmpID ,(SumEnd - ISNULL(SumStart, 0)) / 60.0 AS SumHours FROM CTE_End LEFT JOIN CTE_Start ON CTE_Start.EmpID = CTE_End.EmpID OPTION(RECOMPILE); 

Il y a LEFT JOIN entre les sums pour les heures de début et de fin, car il peut y avoir EmpID qui n'a aucun logging avant l'heure de début.

OPTION(RECOMPILE) est utile lorsque vous utilisez des conditions de search dynamic dans T-SQL . Si @EmpID est NULL , vous obtiendrez des résultats pour tous les users, si ce n'est pas NULL , vous obtiendrez des résultats pour une seule personne.

Si vous n'avez besoin que d'un seul nombre (un total général) pour toutes les personnes, enveloppez le calcul dans le dernier SELECT dans SUM() . Si vous voulez toujours un total général pour tous les users, supprimez @EmpID paramètre @EmpID .

Ce serait une bonne idée d'avoir un index sur (EmpID,date) .

Mon approche serait la suivante:

 CREATE FUNCTION [dbo].[MonthlyHoursByEmpID] ( @StartDate Date, @EndDate Date, @Employee NVARCHAR(6) ) RETURNS FLOAT AS BEGIN DECLARE @TotalHours FLOAT DECLARE @In TABLE ([Date] Date, [Time] Time) DECLARE @Out TABLE ([Date] Date, [Time] Time) INSERT INTO @In([Date], [Time]) SELECT [Date], [Time] FROM PERS_Attendance WHERE [EmpID] = @Employee AND [Funckey] = 'EMPIN' AND ([Date] > @StartDate AND [Date] < @EndDate) INSERT INTO @Out([Date], [Time]) SELECT [Date], [Time] FROM PERS_Attendance WHERE [EmpID] = @Employee AND [Funckey] = 'EMPOUT' AND ([Date] > @StartDate AND [Date] < @EndDate) SET @TotalHours = (SELECT SUM(CONVERT([float],datediff(minute,I.[Time], O.[Time]))/(60)) FROM @in I INNER JOIN @Out O ON I.[Date] = O.[Date]) RETURN @TotalHours END 

En supposant que les inputs sont correctement appariées ( in -> out -> in -> out -> in etc).

SQL Server 2012 et versions ultérieures:

 DECLARE @Year int = 2015 DECLARE @Month int = 8 ;WITH cte AS ( SELECT EmpID, InDate = LAG([Date], 1) OVER (PARTITION BY EmpID ORDER BY [Date]), OutDate = [Date], HoursWorked = DATEDIFF(hour, LAG([Date], 1) OVER (PARTITION BY EmpID ORDER BY [Date]), [Date]), Funckey FROM PERS_Attendance ) SELECT EmpID, TotalHours = SUM(HoursWorked) FROM cte WHERE Funckey = 'EMPOUT' AND YEAR(InDate) = @Year AND MONTH(InDate) = @Month GROUP BY EmpID 

SQL Server 2005 et versions ultérieures:

 ;WITH cte1 AS ( SELECT *, rn = ROW_NUMBER() OVER (PARTITION BY EmpID ORDER BY [Date]) FROM PERS_Attendance ), cte2 AS ( SELECT a.EmpID, b.[Date] As InDate, a.[Date] AS OutDate, HoursWorked = DATEDIFF(hour, b.[Date], a.[Date]) FROM cte1 a LEFT JOIN cte1 b ON a.EmpID = b.EmpID and a.rn = b.rn + 1 WHERE a.Funckey = 'EMPOUT' ) SELECT EmpID, TotalHours = SUM(HoursWorked) FROM cte2 WHERE YEAR(InDate) = @Year AND MONTH(InDate) = @Month GROUP BY EmpID