Bonjour,
Je cherche à splitter les lignes qui ont des périodes qui se chevauchent pour un même id avec la version la plus récente en prioritaire.
J'ai deux tables, une courante contenant la version la plus récente et une d'historique contenant toutes les anciennes versions.
Voici la déclaration de ces tables pour test :
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 IF OBJECT_ID('tempdb..#T_DATA_DAT') IS NOT NULL DROP TABLE #T_DATA_DAT CREATE TABLE #T_DATA_DAT ( DAT_ID int NOT NULL, -- IDENTITY(-2147483648,1) DAT_START datetime2(0) NOT NULL, DAT_END datetime2(0) NOT NULL, DAT_VERSION smallint NOT NULL, DAT_DATA1 int NULL, DAT_DATA2 decimal(8, 2) NULL, DAT_DATA3 decimal(4, 2) NOT NULL, DAT_DATA4 decimal(4, 2) NOT NULL, DAT_CREATED_ON datetime2(3) NOT NULL, CONSTRAINT PK_T_DATA_DAT PRIMARY KEY ( DAT_ID ASC ) ); GOJ'ai fait quelques jeux de données de test. Devant ces jeux de donnée j'ai mis un commentaire pour essayer de rendre visuel ce que je souhaite obtenir.
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 IF OBJECT_ID('tempdb..#T_DATA_HISTORY_DAH') IS NOT NULL DROP TABLE #T_DATA_HISTORY_DAH; GO CREATE TABLE #T_DATA_HISTORY_DAH ( DAH_ID int NOT NULL, DAH_START datetime2(0) NOT NULL, DAH_END datetime2(0) NOT NULL, DAH_VERSION smallint NOT NULL, DAH_DATA1 int NULL, DAH_DATA2 decimal(8, 2) NULL, DAH_DATA3 decimal(4, 2) NOT NULL, DAH_DATA4 decimal(4, 2) NOT NULL, DAH_CREATED_ON datetime2(3) NOT NULL, CONSTRAINT PK_T_DATA_HISTORY_DAH PRIMARY KEY ( DAH_ID, DAH_VERSION ) ); GO
La première ligne de ce commentaire est la ligne du temps composée des différents mois.
Les lignes suivantes sont les jeux de données, le numéro est le numéro de version et les signes ( - , ~ , #) la composant montre les différences de data.
Ce qui se trouve après la ligne RESULT est ce que je souhaite obtenir.
Jeu de données n°1 :
Donc ici on peut voir qu'il y a deux possibilités de résultat, l'important est de garder les valeurs. Si toutes les valeurs sont les mêmes, la ligne peut-être fusionnée comme la ligne 7 mais ce n'est pas du tout une obligation.
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 /* DATA SET ID 1 |====JAN====|====FEV====|====MAR====|====AVR====|====MAI====|====JUIN===|====JUIL===| <-1--> <----2----> <--4--> <--5---> <----7----> <--3--> <~~6~~> ----------------------------------RESULT--------------------------------------------- <----> <-------------------> <-><~~~~~> <---------> OR <----> <---------><-><-----> <-><~~~~~> <---------> */ INSERT INTO #T_DATA_HISTORY_DAH VALUES (1 , '2018-02-01', '2018-02-15', 1, 1000, 100, 10, 1, '2018-02-10 15:00:00.000'); INSERT INTO #T_DATA_HISTORY_DAH VALUES (1 , '2018-03-01', '2018-03-31', 2, 1000, 100, 10, 1, '2018-03-01 15:00:00.000'); INSERT INTO #T_DATA_HISTORY_DAH VALUES (1 , '2018-03-31', '2018-04-15', 3, 1000, 100, 10, 1, '2018-03-15 15:00:00.000'); INSERT INTO #T_DATA_HISTORY_DAH VALUES (1 , '2018-04-10', '2018-04-20', 4, 1000, 100, 10, 1, '2018-04-01 15:00:00.000'); INSERT INTO #T_DATA_HISTORY_DAH VALUES (1 , '2018-05-01', '2018-05-20', 5, 1000, 100, 10, 1, '2018-05-01 15:00:00.000'); INSERT INTO #T_DATA_HISTORY_DAH VALUES (1 , '2018-05-10', '2018-05-25', 6, 500, 100, 10, 1, '2018-05-05 15:00:00.000'); INSERT INTO #T_DATA_DAT VALUES (1 , '2018-06-01', '2018-06-30', 7, 1000, 100, 10, 1, '2018-06-01 15:00:00.000');
Jeu de données n°2 :
Ici on peut voir que les datas qui sont englobé dans une version plus récente disparaisse (Version 1 et 6).
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 /* DATA SET ID 2 |====JAN====|====FEV====|====MAR====|====AVR====|====MAI====|====JUIN===|====JUIL===| <--------------2------------------> <----5---> <-----7---> <----8----> <~3~~> <~1~~> <~~4~~> <~6~> <~9~> ----------------------------------RESULT--------------------------------------------- <-><~~~~><-----------><~~~~~><----> <--------> <---------> <-><~~~><-> */ INSERT INTO #T_DATA_HISTORY_DAH VALUES (2 , '2018-02-03', '2018-02-15', 1, 500, 100, 10, 1, '2018-01-01 15:00:00.000'); INSERT INTO #T_DATA_HISTORY_DAH VALUES (2 , '2018-01-01', '2018-03-31', 2, 500, 100, 10, 1, '2018-01-02 15:00:00.000'); INSERT INTO #T_DATA_HISTORY_DAH VALUES (2 , '2018-01-10', '2018-01-20', 3, 1000, 100, 10, 1, '2018-01-13 15:00:00.000'); INSERT INTO #T_DATA_HISTORY_DAH VALUES (2 , '2018-02-27', '2018-03-15', 4, 500, 100, 10, 1, '2018-02-04 15:00:00.000'); INSERT INTO #T_DATA_HISTORY_DAH VALUES (2 , '2018-05-01', '2018-05-30', 5, 1000, 100, 10, 1, '2018-03-01 15:00:00.000'); INSERT INTO #T_DATA_HISTORY_DAH VALUES (2 , '2018-06-12', '2018-06-20', 6, 500, 100, 10, 1, '2018-03-20 15:00:00.000'); INSERT INTO #T_DATA_HISTORY_DAH VALUES (2 , '2018-06-01', '2018-06-30', 7, 1000, 100, 10, 1, '2018-06-20 15:00:00.000'); INSERT INTO #T_DATA_HISTORY_DAH VALUES (2 , '2018-07-01', '2018-07-30', 8, 1000, 100, 10, 1, '2018-06-25 15:00:00.000'); INSERT INTO #T_DATA_DAT VALUES (2 , '2018-07-10', '2018-07-20', 9, 500, 100, 10, 1, '2018-07-01 15:00:00.000');
Les datas qui englobent des versions plus récentes sont décomposés. C'est le cas de la version 2 qui engobe la 3 et la 4 ainsi que de la version 8 qui englobe la 9.
Jeu de données n°3 :
Et voila, je pense que c'est assez parlant.
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 /* DATA SET ID 3 |====JAN====|====FEV====|====MAR====|====AVR====|====MAI====|====JUIN===|====JUIL===| <--------------2------------------------------------------> <~~3~~> <~~~~~~5~~~~~~> <~~~~~6~~~~> <~~~~~1~~~~> <####4###> <#####7#####> ----------------------------------RESULT--------------------------------------------- <-><~><#####><~~~~~~~><###########><~~~~~~><--------------><~~~~~> */ INSERT INTO #T_DATA_HISTORY_DAH VALUES (3 , '2018-05-17', '2018-06-15', 1, 500, 100, 10, 1, '2018-01-01 15:00:00.000'); INSERT INTO #T_DATA_HISTORY_DAH VALUES (3 , '2018-01-01', '2018-05-31', 2, 1000, 100, 10, 1, '2018-01-02 15:00:00.000'); INSERT INTO #T_DATA_HISTORY_DAH VALUES (3 , '2018-01-10', '2018-01-20', 3, 500, 100, 10, 1, '2018-01-05 15:00:00.000'); INSERT INTO #T_DATA_HISTORY_DAH VALUES (3 , '2018-01-15', '2018-02-12', 4, 20, 100, 10, 1, '2018-01-10 15:00:00.000'); INSERT INTO #T_DATA_HISTORY_DAH VALUES (3 , '2018-02-07', '2018-03-12', 5, 500, 100, 10, 1, '2018-02-01 15:00:00.000'); INSERT INTO #T_DATA_HISTORY_DAH VALUES (3 , '2018-03-20', '2018-04-17', 6, 500, 100, 10, 1, '2018-02-20 15:00:00.000'); INSERT INTO #T_DATA_DAT VALUES (3 , '2018-02-25', '2018-03-31', 7, 20, 100, 10, 1, '2018-02-21 15:00:00.000');
J'ai bien sûr essayé de résoudre ça mais ma logique ensembliste n'est pas des meilleurs. Je me doute qu'il faudra passer par des CTE et de la récursivité, surtout pour le jeu de données n°3 mais je coince complètement.
Jusqu'ici j'ai ceci :
Qui ne fait qu'agréger les deux sources et enlever les versions anciennes qui sont englobées dans une version plus récente.
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 WITH AllData AS ( SELECT * FROM #T_DATA_DAT UNION ALL SELECT * FROM #T_DATA_HISTORY_DAH ) , DataFilterIncludesOldVersion AS ( SELECT * FROM AllData d1 WHERE NOT EXISTS ( SELECT 1 FROM AllData d2 WHERE d1.DAT_ID = d2.DAT_ID AND d1.DAT_START >= d2.DAT_START AND d1.DAT_END <= d2.DAT_END AND d1.DAT_VERSION < d2.DAT_VERSION ) ) SELECT * FROM DataFilterIncludesOldVersion d1 ORDER BY d1.DAT_ID, d1.DAT_CREATED_ON
Si je ne trouve pas de solution propre je créerai une table parallèle que je reconstituerai chaque nuit par un batch mais j'aimerais vraiment éviter ça si c'est possible.
Merci d'avance pour votre aide.
Partager