Discussion :

[riad_09]

bonjour

j'ai une table qui contient un historique de donnée exemple:

date objet chmp1 chmp2 champ3 ...
01/01/09 678 TRUE LOCKED YES
02/01/09 678 FALSE LOCKED NO
je veut faire une procedure qui me donne la difference en deux dates(passé comme paramètre ) des colones comme ceci :



object Champ old new
678 chmp1 TRUE FALSE
678 chmp3 YES NO

je c que c'est difficile mais je c aussi qu'il y' a des génie du sql aussi

merci d'avance

[Waldar]

C'est faisable mais ça ressemble à de la bidouille quand même :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
-- jeu de test
With MaTableHisto (histo_dt, histo_obj, col1, col2, col3) AS
(
select cast('01/01/2009' as smalldatetime), 678, 'TRUE' , 'LOCKED', 'YES' union all
select cast('02/01/2009' as smalldatetime), 678, 'FALSE', 'LOCKED', 'NO'
)
-- selection première date
  ,  Date_1 (histo_obj, col, col_val) AS
(
select histo_obj, 'col1', col1
  from MaTableHisto
 where histo_dt = cast('01/01/2009' as smalldatetime)
 union all
select histo_obj, 'col2', col2
  from MaTableHisto
 where histo_dt = cast('01/01/2009' as smalldatetime)
 union all
select histo_obj, 'col3', col3
  from MaTableHisto
 where histo_dt = cast('01/01/2009' as smalldatetime)
)
-- selection seconde date
  ,  Date_2 (histo_obj, col, col_val) AS
(
select histo_obj, 'col1', col1
  from MaTableHisto
 where histo_dt = cast('02/01/2009' as smalldatetime)
 union all
select histo_obj, 'col2', col2
  from MaTableHisto
 where histo_dt = cast('02/01/2009' as smalldatetime)
 union all
select histo_obj, 'col3', col3
  from MaTableHisto
 where histo_dt = cast('02/01/2009' as smalldatetime)
)
-- selection finale
select coalesce(D1.histo_obj, D2.histo_obj) as histo_obj,
       coalesce(D1.col, D2.col) as col,
       D1.col_val as old,
       D2.col_val as new
  from Date_1 AS D1
       full outer join Date_2 AS D2
         on D2.histo_obj = D1.histo_obj
        and D2.col = D1.col
 where coalesce(D1.col_val, '-1') <> coalesce(D2.col_val, '-1');
 
histo_obj   col  old    new
----------- ---- ------ ------
678         col1 TRUE   FALSE
678         col3 YES    NO

[riad_09]

C'est faisable mais ça ressemble à de la bidouille quand même :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
-- jeu de test
With MaTableHisto (histo_dt, histo_obj, col1, col2, col3) AS
(
select cast('01/01/2009' as smalldatetime), 678, 'TRUE' , 'LOCKED', 'YES' union all
select cast('02/01/2009' as smalldatetime), 678, 'FALSE', 'LOCKED', 'NO'
)
-- selection première date
  ,  Date_1 (histo_obj, col, col_val) AS
(
select histo_obj, 'col1', col1
  from MaTableHisto
 where histo_dt = cast('01/01/2009' as smalldatetime)
 union all
select histo_obj, 'col2', col2
  from MaTableHisto
 where histo_dt = cast('01/01/2009' as smalldatetime)
 union all
select histo_obj, 'col3', col3
  from MaTableHisto
 where histo_dt = cast('01/01/2009' as smalldatetime)
)
-- selection seconde date
  ,  Date_2 (histo_obj, col, col_val) AS
(
select histo_obj, 'col1', col1
  from MaTableHisto
 where histo_dt = cast('02/01/2009' as smalldatetime)
 union all
select histo_obj, 'col2', col2
  from MaTableHisto
 where histo_dt = cast('02/01/2009' as smalldatetime)
 union all
select histo_obj, 'col3', col3
  from MaTableHisto
 where histo_dt = cast('02/01/2009' as smalldatetime)
)
-- selection finale
select coalesce(D1.histo_obj, D2.histo_obj) as histo_obj,
       coalesce(D1.col, D2.col) as col,
       D1.col_val as old,
       D2.col_val as new
  from Date_1 AS D1
       full outer join Date_2 AS D2
         on D2.histo_obj = D1.histo_obj
        and D2.col = D1.col
 where coalesce(D1.col_val, '-1') <> coalesce(D2.col_val, '-1');
 
histo_obj   col  old    new
----------- ---- ------ ------
678         col1 TRUE   FALSE
678         col3 YES    NO

merci Waldar pour ta réponse

mais le problème est que mes tables contiennent une trentaine de colonne et je veut faire apparaitre pour chaque le changement sur les colonnes entre deux dates (je veut que ces dates soit dynamique Date1 et Date2)
y'a t'il moyen de boucler sur les colonnes??

merci