Quelle sera la meilleure façon de find la différence de date?

J'ai une suspicion furtive que la fonction DateDiff () est la fonction que vous cherchez

http://www.w3schools.com/sql/func_datediff.asp

Il y a un moyen facile de faire ce que je suppose que vous voulez faire avec l'application externe, comme ceci:

 select tmin.*, t.oa_CreateDate oa_CreateDate_20 , datediff(minute, tmin.oa_CreateDate, t.oa_CreateDate) DiffInMinutes from testtable t cross apply (select top 1 * from testtable tmin where tmin.oa_CreateDate < t.oa_CreateDate and tmin.oa_OperatorId = t.oa_OperatorId order by tmin.oa_CreateDate asc) tmin where t.ActivityStatus = 20 and t.oa_CreateDate < (select min(oa_CreateDate) from testtable where ActivityStatus = 10 and oa_OperatorId = 1960) and t.oa_OperatorId = 1960 union all select t.* , coalesce(a.oa_CreateDate,ma.MaxDate) oa_CreateDate_20 , datediff(minute, t.oa_CreateDate, coalesce(a.oa_CreateDate,ma.MaxDate)) DiffInMinutes from testtable t outer apply (select top 1 a.oa_CreateDate from testtable a where a.oa_OperatorId = t.oa_OperatorId and a.ActivityStatus = 20 and t.oa_CreateDate < a.oa_CreateDate order by a.oa_CreateDate asc) a outer apply (select max(a2.oa_CreateDate) maxDate from testtable a2 where a2.oa_OperatorId = t.oa_OperatorId and t.oa_CreateDate < a2.oa_CreateDate) ma where oa_OperatorId = 1960 and ActivityStatus = 10 order by oa_CreateDate asc, oa_CreateDate_20 asc 

Vous pouvez voir le violon ici .

Mais bien sûr, vous devez nous donner le format / accurracy pour la comparaison de datediff. Et cela suppose que vous aurez toujours les deux statuts 10 et 20, et que leurs plages d'horodatage ne se chevauchent jamais.

EDIT: Mise à jour de la réponse en fonction de votre commentaire, vérifiez le nouveau script et le violon. Maintenant, le remplissage de script trouve tous les datiffs Status 10 – 20, et dans le cas où aucun statut 20 n'existe après les 10 derniers, alors le dernier horodatage existant après ce Status 10 sera utilisé à la place.

EDIT 2: Mise à jour avec votre commentaire ci-dessous. Mais à ce stade, le script devient plutôt laid. Malheureusement, je n'ai pas le time de le nettoyer, alors je request que la prochaine fois que vous posez une question, veuillez la rendre aussi claire et nette que possible, car il y a beaucoup less d'efforts pour répondre à une question au lieu de la modifier 3 variations différentes le long du trajet. 🙂

Cela devrait fonctionner de toute façon, la nouvelle section avant l'UNION ALL dans le script ne returnnera les résultats que s'il y a des Status 20 sans les 10 précédents. Sinon, il ne returnnera rien et passera à la partie principale du script comme avant. Le violon a été mis à jour aussi.

C'est une façon de le faire.

La première OUTER APPLY récupérera la ligne suivante avec un statut de 20 après le datetime créé en cours.

La deuxième OUTER APPLY récupérera la ligne suivante après le datetime créé en cours où il n'y a pas d'état 20.

 SELECT o.* , COALESCE(NextStatus.oa_CreateDate, NextStatusIsNull.oa_CreateDate) AS NextTimestamp , COALESCE(NextStatus.ActivityStatus, NextStatusIsNull.ActivityStatus) AS NextStatus , DATEDIFF(MINUTE, o.oa_CreateDate, COALESCE(NextStatus.oa_CreateDate, NextStatusIsNull.oa_CreateDate)) AS DifferenceInMinutes FROM operators AS o OUTER APPLY ( SELECT TOP 1 oa_CreateDate , ActivityStatus FROM operators WHERE ActivityStatus = 20 AND oa_CreateDate > o.oa_CreateDate ORDER BY oa_CreateDate ) AS NextStatus OUTER APPLY ( SELECT TOP 1 oa_CreateDate , ActivityStatus FROM operators WHERE NextStatus.oa_CreateDate IS NULL AND oa_CreateDate > o.oa_CreateDate ORDER BY oa_CreateDate ) AS NextStatusIsNull WHERE ActivityStatus = 10 

J'ai utilisé des données de test différentes parce que vous avez utilisé une image dont je n'ai pas pu couper et coller. Cela devrait être facile à convertir à votre table:

Notez que cela devrait également fonctionner avec les dates de début et de fin inexistantes,

Notez également que cela a été fait sans jointures pour optimiser les performances.

Table de test et données:

 DECLARE @t table(ActivityStatus int, oa_createdate datetime, oa_operatorid int) INSERT @t values (30, '2015-07-23 08:20', 1960),(20, '2015-07-23 08:24', 1960), (10, '2015-07-23 08:30', 1960),(20, '2015-07-23 08:40', 1960), (10, '2015-07-23 08:50', 1960),(50, '2015-07-23 09:40', 1960) 

Question:

 ;WITH cte as ( SELECT ActivityStatus, oa_createdate, oa_operatorid FROM @t WHERE ActivityStatus in (10,20) UNION ALL SELECT 20, max(oa_createdate), oa_operatorid FROM @t GROUP BY oa_operatorid HAVING max(case when ActivityStatus = 20 then oa_createdate end) < max(case when ActivityStatus = 10 then oa_createdate end) UNION ALL SELECT 10, min(oa_createdate), oa_operatorid FROM @t GROUP BY oa_operatorid HAVING min(case when ActivityStatus = 20 then oa_createdate end) < min(case when ActivityStatus = 10 then oa_createdate else '2999-01-01' end) ) SELECT cast(cast(sum(case when activitystatus = 10 then -1 else 1 end * cast(oa_createdate as float)) as datetime) as time(0)) as difference_in_time, oa_operatorid FROM cte GROUP BY oa_operatorid 

Résultat:

 difference_in_time oa_operatorid 01:04:00 1960 

Les données

 create table #Table2 (oa_id int, oa_OperatorId int, ActivityStatus int, oa_CreateDate datetime) insert into #Table2 values (1, 1960,10,'2015-08-10 10:55:12.317') ,(2, 1960,20,'2015-08-10 11:55:12.317') ,(3, 1960,30,'2015-08-10 14:55:12.317') ,(4, 1960,50,'2015-08-10 14:58:12.317') ,(5, 1960,10,'2015-08-10 15:55:12.317') ,(6, 1960,20,'2015-08-10 16:20:12.317') ,(7, 1960,10,'2015-08-10 16:30:12.317') ,(8, 1960,50,'2015-08-10 17:20:12.317') 

Remplir la table cible avec les lignes qui nous intéressent

 select oa_id, oa_operatorid, ActivityStatus, oa_createDate, rn = row_number() over (order by oa_id desc) into #Table from #Table2 where ActivityStatus in (10, 20) insert #Table select top 1 oa_id, oa_operatorid, ActivityStatus, oa_createDate, 0 from #Table2 order by oa_id desc select * into #Table10 from #Table where ActivityStatus = 10 select * into #Table20 from #Table where ActivityStatus = 20 union select * from #Table where rn = 0 /*add the last record*/ except select * from #Table where rn = (select max(rn) from #Table) /**discard the first "20" record*/ /*free time info*/ select datediff(second, t10.oa_createDate, t20.oa_createDate) secondssincelast10, t20.* from #Table10 t10 join #Table20 t20 on t10.rn = t20.rn + 1 and t10.oa_OperatorId = t20.oa_OperatorId /*Summarized info per operator*/ select sum(datediff(second, t10.oa_createDate, t20.oa_createDate)) totalbusytime, t20.oa_OperatorId from #Table10 t10 join #Table20 t20 on t10.rn = t20.rn + 1 and t10.oa_OperatorId = t20.oa_OperatorId group by t20.oa_OperatorId 

Meilleur moyen

 DATEDIFF(expr1,expr2) 

Exemple:

  CREATE TABLE pins (`id` int, `time` datetime) ; INSERT INTO pins (`id`, `time`) VALUES (1, '2013-11-15 05:25:25') ; SELECT DATEDIFF(CURDATE(), `time`) FROM `pins`