calculer une difference de temps avec des dates qui ne sont pas dans le même row

Question

bonjour,
Je dois trouver la différence de temps entre le moment ou un ticket est mis en Suspended (1 ligne) et le moment ou il change d'état (autre ligne) et quitte le statut de suspended 
j'ai une view comme ceci :
Newvalue	oldvalue      incidentnumber	              Date
Suspended	Open	       194094	             6/04/2012 9:12
Solved	        Suspended     195932	             6/01/2012 9:26
Suspended	Open	       197688	              15/06/2012 9:35
Solved	        Suspended	197688	             18/06/2012 3:21
Open	        Suspended	184952	             6/04/2012 8:46
Suspended	Open	       196449	             6/04/2012 11:12
Solved	        Suspended	196449	              6/04/2012 13:57
Suspended	Open	       197529	             6/11/2012 16:51
Open	        Suspended	197529	             6/12/2012 6:50
Suspended	Open	       197529	              6/12/2012 16:57
Solved	        Suspended	197529	              13/06/2012 11:26

Comment m'y prendre ? Sachant que le critère commun pour trouver l'ouverture du statut et sa fermeture est IncidentNumber, qu'il peut y avoir plusieurs ouvertures et fermetures sur un même ticket (Incidentnumber).
J'ai commencé une query, dans laquelle je n'ai pas encore calculé la différence de temps, mais simplement pour trouver la ligne correspondant à la fermeture du Statut par rapport à IncidentNumber. suis-je dans la bonne direction ou non ?
-------------------
Sous SQL Server 2008
-------------------
[i]DECLARE @IncidentNumber INT, @CreatedDate DATETIME, @CloseDate DATETIME
;

--SET @LastDateChange = GETDATE()

DECLARE myCursor CURSOR FOR

 SELECT IncidentNumber, CreatedDateTime
FROM dbo.v_Sel_Suspended
WHERE NewFieldValue = 'Suspended'

OPEN myCursor
FETCH NEXT FROM myCursor
INTO @IncidentNumber, @CreatedDate

WHILE @@FETCH_STATUS = 0
BEGIN

SET @CloseDate (SELECT Min(CreatedDateTime) FROM dbo.v_Sel_Suspended WHERE OldFieldValue 'Suspended' 
AND IncidentNumber = @IncidentNumber AND CreatedDateTime> @CreatedDate)

FETCH NEXT FROM myCursor
INTO @IncidentNumber, @CreatedDate

END
CLOSE myCursor
DEALLOCATE myCursor/i
--------------------------------------------------------------
Bien sur ce n'est qu'une ébauche. L'idée étant ensuite de calculer le nombre d'H de différence entre la createdDateTime et la variable @ClosedDate, et de mettre ensuite IncidentNumber et le nbre d'heure dans une table (en tenant compte de l'incrémebtation du nbre d'heures si le ticket revient plusieurs fois en statut Suspended)

Merci pour votre aide : Il y a probablement un moyen plus simple, mais je ne suis pas d'un niveau hyper avancé en SQL 
AngelJo

cgandco · Answer

Bonjour,

Si je comprend bien tu veux faire la somme des temps ou le ticket était  ou est encore suspendu ?

Bonne journée


Faites simple, aussi simple que possible, mais pas simpliste.
                                              A. Einstein.

yann_lo_san · Answer

Salut,

Ci-dessous un test avec les données que tu donnes, une requete avec GROUP BY suffit à récupérer la différence de temps entre les "Suspended" de tous les tickets et leurs "précédents états" :
(le DECLARE table et les INSERTs sont pour tester en direct avec tes données, qui d'ailleurs sont invalident pour certaines lignes)

 DECLARE @v_Sel_Suspended table (
Newvalue varchar(50),
oldvalue varchar(50),
incidentnumber int,
Date datetime
);

INSERT INTO @v_Sel_Suspended VALUES('Suspended', 'Open', 194094, '06/04/2012 09:12');
INSERT INTO @v_Sel_Suspended VALUES('Solved', 'Suspended', 195932, '06/01/2012 09:26'); 
INSERT INTO @v_Sel_Suspended VALUES('Suspended', 'Open', 197688, '15/06/2012 09:35');
INSERT INTO @v_Sel_Suspended VALUES('Solved', 'Suspended', 197688, '18/06/2012 03:21'); 
INSERT INTO @v_Sel_Suspended VALUES('Open', 'Suspended', 184952, '06/04/2012 08:46');
INSERT INTO @v_Sel_Suspended VALUES('Suspended', 'Open', 196449, '06/04/2012 11:12');
INSERT INTO @v_Sel_Suspended VALUES('Solved', 'Suspended', 196449, '06/04/2012 13:57'); 
INSERT INTO @v_Sel_Suspended VALUES('Suspended', 'Open', 197529, '06/11/2012 16:51');
INSERT INTO @v_Sel_Suspended VALUES('Open', 'Suspended', 197529, '06/12/2012 06:50');
INSERT INTO @v_Sel_Suspended VALUES('Suspended', 'Open', 197529, '06/12/2012 16:57');
INSERT INTO @v_Sel_Suspended VALUES('Solved', 'Suspended', 197529, '13/06/2012 11:26'); 

SELECT
ID = new.incidentnumber,
SUSPENDED = new.Newvalue,
DATE_SUSPENDED = new.Date,
ANCIEN_ETAT = isnull(old.Newvalue,'TICKET INVALIDE'),
DTE_DIFF = 
case when old.Date is null 
then 'Le premier état était Suspended !!!'
else cast((datediff(minute,old.Date,new.Date) / 60 / 24) as varchar) + ' jour(s) ' +  
cast(((datediff(minute,old.Date,new.Date) / 60) % 24) as varchar) + ' Heure(s) et ' +
cast(datediff(minute,old.Date,new.Date)-(((datediff(minute,old.Date,new.Date)/60)/24)*24*60)-((datediff(minute,old.Date,new.Date)/60) % 24*60) as varchar) + ' Minute(s)'
end
FROM 
@v_Sel_Suspended new
left outer join @v_Sel_Suspended old on old.incidentnumber = new.incidentnumber
and old.Date<new.Date and old.Newvalue != 'Suspended'
WHERE 
new.Newvalue = 'Suspended' 
--and new.incidentnumber = 197529 /*Si on veut un seul ticket*/
GROUP BY 
new.Newvalue,
new.incidentnumber,
new.Date,
old.Newvalue,
case when old.Date is null 
then 'Le premier état était Suspended !!!'
else cast((datediff(minute,old.Date,new.Date) / 60 / 24) as varchar) + ' jour(s) ' +  
cast(((datediff(minute,old.Date,new.Date) / 60) % 24) as varchar) + ' Heure(s) et ' +
cast(datediff(minute,old.Date,new.Date)-(((datediff(minute,old.Date,new.Date)/60)/24)*24*60)-((datediff(minute,old.Date,new.Date)/60) % 24*60) as varchar) + ' Minute(s)'
end
ORDER bY
new.incidentnumber, new.Date

bye...

yann_lo_san · Answer

Requete coupée !

SELECT
ID = new.incidentnumber,
SUSPENDED = new.Newvalue,
DATE_SUSPENDED = new.Date,
ANCIEN_ETAT = isnull(old.Newvalue,'TICKET INVALIDE'),
DTE_DIFF = 
case when old.Date is null 
then 'Le premier état était Suspended !!!'
else cast((datediff(minute,old.Date,new.Date) / 60 / 24) as varchar) + ' jour(s) ' +  
cast(((datediff(minute,old.Date,new.Date) / 60) % 24) as varchar) + ' Heure(s) et ' +
cast(datediff(minute,old.Date,new.Date)-(((datediff(minute,old.Date,new.Date)/60)/24)*24*60)-((datediff(minute,old.Date,new.Date)/60) % 24*60) as varchar) + ' Minute(s)'
end
FROM 
@v_Sel_Suspended new
left outer join @v_Sel_Suspended old on old.incidentnumber = new.incidentnumber
and old.Date<new.Date and old.Newvalue != 'Suspended'
WHERE 
new.Newvalue = 'Suspended' 
--and new.incidentnumber = 197529 /*Si on veut un seul ticket*/
GROUP BY 
new.Newvalue,
new.incidentnumber,
new.Date,
old.Newvalue,
case when old.Date is null 
then 'Le premier état était Suspended !!!'
else cast((datediff(minute,old.Date,new.Date) / 60 / 24) as varchar) + ' jour(s) ' +  
cast(((datediff(minute,old.Date,new.Date) / 60) % 24) as varchar) + ' Heure(s) et ' +
cast(datediff(minute,old.Date,new.Date)-(((datediff(minute,old.Date,new.Date)/60)/24)*24*60)-((datediff(minute,old.Date,new.Date)/60) % 24*60) as varchar) + ' Minute(s)'
end
ORDER bY
new.incidentnumber, new.Date

bye...

yann_lo_san · Answer

SELECT
ID = new.incidentnumber,
SUSPENDED = new.Newvalue,
DATE_SUSPENDED = new.Date,
ANCIEN_ETAT = isnull(old.Newvalue,'TICKET INVALIDE'),
DTE_DIFF = 
case when old.Date is null 
then 'Le premier état était Suspended !!!'
else cast((datediff(minute,old.Date,new.Date) / 60 / 24) as varchar) + ' jour(s) ' +  
cast(((datediff(minute,old.Date,new.Date) / 60) % 24) as varchar) + ' Heure(s) et ' +
cast(datediff(minute,old.Date,new.Date)-(((datediff(minute,old.Date,new.Date)/60)/24)*24*60)-((datediff(minute,old.Date,new.Date)/60) % 24*60) as varchar) + ' Minute(s)'
end
FROM 
@v_Sel_Suspended new
left outer join @v_Sel_Suspended old on old.incidentnumber = new.incidentnumber
and old.Date<new.Date and old.Newvalue != 'Suspended'
WHERE 
new.Newvalue = 'Suspended' 
--and new.incidentnumber = 197529 /*Si on veut un seul ticket*/
GROUP BY 
new.Newvalue,
new.incidentnumber,
new.Date,
old.Newvalue,
case when old.Date is null 
then 'Le premier état était Suspended !!!'
else cast((datediff(minute,old.Date,new.Date) / 60 / 24) as varchar) + ' jour(s) ' +  
cast(((datediff(minute,old.Date,new.Date) / 60) % 24) as varchar) + ' Heure(s) et ' +
cast(datediff(minute,old.Date,new.Date)-(((datediff(minute,old.Date,new.Date)/60)/24)*24*60)-((datediff(minute,old.Date,new.Date)/60) % 24*60) as varchar) + ' Minute(s)'
end
ORDER bY
new.incidentnumber, new.Date

Calculer une difference de temps avec des dates qui ne sont pas dans le même row

4 réponses

Votre réponse

Discussions similaires