[2005]Trouver un intervalle à partir des timestamp

Version imprimable

15/08/2009, 00h57
bilelx2002

[2005]Trouver un intervalle à partir des timestamp

Bonjour,

Je suis à la recherche d'une procédure qui permet de trouver l'intervalle (date de debut et une date de fin) à partir d'une table d'enregistrements avec un timestamp.

J'ai une table A et je veux alimenter la table B.

Table A:
Table de supervision
----------- --------
event nvarchar(10)
detection TIMESTAMP

Table B:
Table d'historique
----------- --------
event nvarchar(10)
debut_detection DATETIME
fin_detection DATETIME

Voici un extrait de la table A
SQL> SELECT * FROM A;
Event_A 01/01/2009 00:00:00
Event_A 02/01/2009 00:00:00
Event_A 03/01/2009 00:00:00
Event_B 04/01/2009 00:00:00
Event_B 05/01/2009 00:00:00
Event_A 06/01/2009 00:00:00
Event_A 07/01/2009 00:00:00

La table B doit contenir les information telque

SQL> SELECT * FROM B;
Event_A 01/01/2009 00:00:00 03/01/2009 00:00:00
Event_B 04/01/2009 00:00:00 05/01/2009 00:00:00
Event_A 06/01/2009 00:00:00 07/01/2009 00:00:00

Essayez ainsi :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
WITH A (Col1, Col2) AS
(
  SELECT 'Event_A', CAST('01/01/2009' AS SMALLDATETIME) UNION ALL
  SELECT 'Event_A', CAST('02/01/2009' AS SMALLDATETIME) UNION ALL
  SELECT 'Event_A', CAST('03/01/2009' AS SMALLDATETIME) UNION ALL
  SELECT 'Event_B', CAST('04/01/2009' AS SMALLDATETIME) UNION ALL
  SELECT 'Event_B', CAST('05/01/2009' AS SMALLDATETIME) UNION ALL
  SELECT 'Event_A', CAST('06/01/2009' AS SMALLDATETIME) UNION ALL
  SELECT 'Event_A', CAST('07/01/2009' AS SMALLDATETIME)
), A_Id (Col1, Col2, G_Id) AS
(
  SELECT Col1, Col2,
         ROW_NUMBER() OVER(                  ORDER BY Col2 ASC) -
         ROW_NUMBER() OVER(PARTITION BY Col1 ORDER BY Col2 ASC)
    FROM A
)
  SELECT Col1, MIN(Col2) AS Date_Min, MAX(Col2) AS Date_Max
    FROM A_Id
GROUP BY G_Id, Col1
ORDER BY MIN(Col2) ASC;
 
Col1    Date_Min                Date_Max
------- ----------------------- -----------------------
Event_A 01/01/2009 00:00:00     03/01/2009 00:00:00
Event_B 04/01/2009 00:00:00     05/01/2009 00:00:00
Event_A 06/01/2009 00:00:00     07/01/2009 00:00:00

Je vous invite à lire ce sujet si vous voulez comprendre la solution :
http://beyondrelational.com/blogs/tc...hroz-alam.aspx

15/08/2009, 19h42
bilelx2002

Merci pour cette réponse qui répond à mon problème.