YearMonth entre 2 dates en fonction d'un statut

**sdisp+** · 02/08/2022, 16h20

Bonjour,

J'ai besoin d'aide sur une requête pour un problème plutôt complexe, je vais essayer de l'expliquer au mieux.

Règles:
- Récupéré le idRecord, yearMonth entre le 1er status non exclus (isStateExcluded = 0) et le 1er status exclus (isStateExcluded = 1) ou TODAY s'il n'y a pas de status exclus.
- On compte le mois d'exclusion

Cas pratique simple :
Si un record est en status 0 en Janvier, que rien ne se passe jusqu'en Juin et qu'en juin le record passe en status 1, alors on doit compter le record en janvier, février, mars, avril, mai et juin (on compte le dernier mois) soit un résultat attendu de :

yearMonth	idRecord
2022-01	15
2022-02	15
2022-03	15
2022-04	15
2022-05	15
2022-06	15

Cas pratique réel :

Code SQL :

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
WITH CTE AS( 
      SELECT 15 as idRecord, '2021-11-23 17:32:53' as dateRecord, 0 as isStateExcluded UNION ALL
      SELECT 15 as idRecord, '2021-11-25 13:34:06' as dateRecord, 0 as isStateExcluded UNION ALL
      SELECT 15 as idRecord, '2021-11-25 16:07:45' as dateRecord, 0 as isStateExcluded UNION ALL
      SELECT 15 as idRecord, '2022-01-07 17:37:56' as dateRecord, 1 as isStateExcluded UNION ALL
      SELECT 15 as idRecord, '2022-01-07 17:39:53' as dateRecord, 0 as isStateExcluded UNION ALL
      SELECT 15 as idRecord, '2022-01-07 17:40:31' as dateRecord, 0 as isStateExcluded UNION ALL
      SELECT 15 as idRecord, '2022-01-07 17:40:41' as dateRecord, 0 as isStateExcluded UNION ALL
      SELECT 15 as idRecord, '2022-01-11 12:20:12' as dateRecord, 0 as isStateExcluded UNION ALL
      SELECT 15 as idRecord, '2022-01-11 12:23:16' as dateRecord, 0 as isStateExcluded UNION ALL
      SELECT 15 as idRecord, '2022-01-12 10:48:41' as dateRecord, 0 as isStateExcluded UNION ALL
      SELECT 15 as idRecord, '2022-03-12 11:46:08' as dateRecord, 1 as isStateExcluded UNION ALL
      SELECT 15 as idRecord, '2022-03-12 11:46:25' as dateRecord, 1 as isStateExcluded UNION ALL
      SELECT 15 as idRecord, '2022-05-11 12:20:12' as dateRecord, 0 as isStateExcluded 
)
SELECT *
FROM CTE

idRecord	dateRecord	isStateExcluded
15	2021-11-23 17:32:53	0
15	2021-11-25 13:34:06	0
15	2021-11-25 16:07:45	0
15	2022-01-07 17:37:56	1
15	2022-01-07 17:39:53	0
15	2022-01-07 17:40:31	0
15	2022-01-07 17:40:41	0
15	2022-01-11 12:20:12	0
15	2022-01-11 12:23:16	0
15	2022-01-12 10:48:41	0
15	2022-03-12 11:46:08	1
15	2022-03-12 11:46:25	1
15	2022-05-11 12:20:12	0

Je souhaite obtenir ceci :

yearMonth	idRecord
2021-11	15
2021-12	15
2022-01	15
2022-02	15
2022-03	15
2022-05	15
2022-06	15
2022-07	15
2022-08	15

Explications:
- en 2021-11, le record 15 a eu des transactions qui ne sont pas exclus , il faut donc que cette ligne soit comptée, dans la table résultat j'ai donc : 2021-11|15
- en 2021-12, il n'y a pas de record, mais le statut n'as pas été exclus avant, il faut donc compter cette ligne, dans la table résultat j'ai donc : 2021-12|15
- en 2022-01, le 1er enregistrement est un statut d'exclusion, vue que le mois d'exclusion est compter, la table résultat a : 2022-01|15
- en 2022-02, il n'y a pas de record, le statut lui a en 1er lieu été exclus mais il a été inclus par la suite, il faut donc compter cette ligne, dans la table résultat j'ai donc : 2022-02|15
- en 2022-03, le 1er enregistrement est un statut d'exclusion, vue que le mois d'exclusion est compté, la table résultat a : 2022-03|15
- en 2022-04, il n'y a pas de record et le statut a été précédemment exclus il ne faut donc PAS compter d'enregistrement
- en 2022-05, cette ligne est en statut inclus, il faut donc la compter, dans la table résultat j'ai donc : 2022-05|15
- en 2022-06, il n'y a pas de record, il n'y a plus de statut d'exclusion on compte donc cette ligne jusqu'à aujourd'hui, dans la table résultat j'ai donc : 2022-06|15
- en 2022-07, il n'y a pas de record, il n'y a plus de statut d'exclusion on compte donc cette ligne jusqu'à aujourd'hui, dans la table résultat j'ai donc : 2022-07|15
- en 2022-08, il n'y a pas de record, il n'y a plus de statut d'exclusion on compte donc cette ligne jusqu'à aujourd'hui, dans la table résultat j'ai donc : 2022-08|15

Voilà en gros ce que je souhaite faire, si je ne vous ai pas trop perdu dans mes explications pouvez-vous m'aider?
Merci.

**JeitEmgie** · 03/08/2022, 08h23

Vous devez d'abord combler les mois manquants en générant la liste de tous les (année, mois) possibles
et faire un cross product avec les données existantes (en excluant les (idProduct, année, mois) déjà présents)

ensuite sur ce résultat, appliquer quelques fonctions analytiques
row_number() sur la partition (idProduct, année, mois) vous permettra de détecter la condition "premier du mois"
last_value(isStateExcluded) ignore nulls sur la partition idRecord order by dateRecord vous donnera la dernière valeur rencontrée du isStateExcluded

il faudra peut-être aussi calculer le fait que le record a été exclus ce mois
(par un MAX(isStateExcluded) sur la partition (idProduct, année, mois) à appliquer sur le résultat intermédiaire précédent avec le row_number() et last_value())
en principe vous devriez avoir tout pour calculer l'état "keep" dans un CASE

**sdisp+** · 03/08/2022, 09h52

Bonjour,

Merci JeitEmgie, pour ces infos

Concernant :

Vous devez d'abord combler les mois manquants en générant la liste de tous les (année, mois) possibles
et faire un cross product avec les données existantes (en excluant les (idProduct, année, mois) déjà présents)

Je dois donc faire une jointure entre une table date et mes datas pour générer les mois manquant, par contre je ne comprends pas :

faire un cross product avec les données existantes (en excluant les (idProduct, année, mois) déjà présents

En ce qui concerne ceci :

ensuite sur ce résultat, appliquer quelques fonctions analytiques
row_number() sur la partition (idProduct, année, mois) vous permettra de détecter la condition "premier du mois"
last_value(isStateExcluded) ignore nulls sur la partition idRecord order by dateRecord vous donnera la dernière valeur rencontrée du isStateExcluded

il faudra peut-être aussi calculer le fait que le record a été exclus ce mois
(par un MAX(isStateExcluded) sur la partition (idProduct, année, mois) à appliquer sur le résultat intermédiaire précédent avec le row_number() et last_value())
en principe vous devriez avoir tout pour calculer l'état "keep" dans un CASE

Je m'étais orienté dans cette direction, mais je n'avais pas pensé au last_value(isStateExcluded) et MAX(isStateExcluded).
Je vais faire des tests.

Merci encore.

**JeitEmgie** · 03/08/2022, 10h20

plutôt un UNION ALL
entre la table existante
et
un cross product entre l'ensemble des idProduct et les années/mois générés et cela pour les idProduct, année/mois qui manquent (where not exists).
où vous renverrez NULL pour le isStateExcluded et le 1er du mois à 00:00:00 pour la date
(pour que l'UNION fonctionne il faut le même nombre de colonnes des 2 côtés)

le NULL permettra d'éliminer si nécessaire les années/mois rajoutés AVANT la première apparition de l'idProduct,
puisque les années/mois générés couvriront le MIN(dateRecord) de l'ensemble des données -> aujourd'hui.

**sdisp+** · 03/08/2022, 12h40

Je progresse, en me basant sur une table date comme ci-dessous :

Nom : Date.png
Affichages : 96
Taille : 9,1 Ko

J'ai pu faire ceci:

un cross product entre l'ensemble des idProduct et les années/mois générés et cela pour les idProduct, année/mois qui manquent (where not exists).
où vous renverrez NULL pour le isStateExcluded et le 1er du mois à 00:00:00 pour la date
(pour que l'UNION fonctionne il faut le même nombre de colonnes des 2 côtés)

Avec ce code :

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
WITH CTE 
AS (
	SELECT 15 as idRecord, '2021-11-23 17:32:53' as dateRecord, 0 as isStatusExcluded UNION ALL
	SELECT 15 as idRecord, '2021-11-25 13:34:06' as dateRecord, 0 as isStatusExcluded UNION ALL
	SELECT 15 as idRecord, '2021-11-25 16:07:45' as dateRecord, 0 as isStatusExcluded UNION ALL
	SELECT 15 as idRecord, '2022-01-07 17:37:56' as dateRecord, 1 as isStatusExcluded UNION ALL
	SELECT 15 as idRecord, '2022-01-07 17:39:53' as dateRecord, 0 as isStatusExcluded UNION ALL
	SELECT 15 as idRecord, '2022-01-07 17:40:31' as dateRecord, 0 as isStatusExcluded UNION ALL
	SELECT 15 as idRecord, '2022-01-07 17:40:41' as dateRecord, 0 as isStatusExcluded UNION ALL
	SELECT 15 as idRecord, '2022-01-11 12:20:12' as dateRecord, 0 as isStatusExcluded UNION ALL
	SELECT 15 as idRecord, '2022-01-11 12:23:16' as dateRecord, 0 as isStatusExcluded UNION ALL
	SELECT 15 as idRecord, '2022-01-12 10:48:41' as dateRecord, 0 as isStatusExcluded UNION ALL
	SELECT 15 as idRecord, '2022-03-12 11:46:08' as dateRecord, 1 as isStatusExcluded UNION ALL
	SELECT 15 as idRecord, '2022-03-12 11:46:25' as dateRecord, 1 as isStatusExcluded UNION ALL
	SELECT 15 as idRecord, '2022-05-11 12:20:12' as dateRecord, 1 as isStatusExcluded
)
SELECT DISTINCT idRecord
	,[Month]
	,NULL AS isStatusExcluded
FROM [D_Date] a
CROSS JOIN CTE b
WHERE [Year_FR] IN (
		2022
		,2022 - 1
		)
	AND NOT EXISTS (
		SELECT 1
		FROM CTE c
		WHERE b.idRecord = c.idRecord
			AND CONVERT(VARCHAR(7), a.[Month], 126) = CONVERT(VARCHAR(7), c.dateRecord, 126)
		)

Ce qui me donne ceci :
Nom : Result.png
Affichages : 96
Taille : 10,1 Ko

J'ai donc les 20 mois manquants de la CTE avec NULL pour le isStatusExcluded.

En ajoutant le UNION ALL :

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
WITH CTE 
AS (
	SELECT 15 as idRecord, '2021-11-23 17:32:53' as dateRecord, 0 as isStatusExcluded UNION ALL
	SELECT 15 as idRecord, '2021-11-25 13:34:06' as dateRecord, 0 as isStatusExcluded UNION ALL
	SELECT 15 as idRecord, '2021-11-25 16:07:45' as dateRecord, 0 as isStatusExcluded UNION ALL
	SELECT 15 as idRecord, '2022-01-07 17:37:56' as dateRecord, 1 as isStatusExcluded UNION ALL
	SELECT 15 as idRecord, '2022-01-07 17:39:53' as dateRecord, 0 as isStatusExcluded UNION ALL
	SELECT 15 as idRecord, '2022-01-07 17:40:31' as dateRecord, 0 as isStatusExcluded UNION ALL
	SELECT 15 as idRecord, '2022-01-07 17:40:41' as dateRecord, 0 as isStatusExcluded UNION ALL
	SELECT 15 as idRecord, '2022-01-11 12:20:12' as dateRecord, 0 as isStatusExcluded UNION ALL
	SELECT 15 as idRecord, '2022-01-11 12:23:16' as dateRecord, 0 as isStatusExcluded UNION ALL
	SELECT 15 as idRecord, '2022-01-12 10:48:41' as dateRecord, 0 as isStatusExcluded UNION ALL
	SELECT 15 as idRecord, '2022-03-12 11:46:08' as dateRecord, 1 as isStatusExcluded UNION ALL
	SELECT 15 as idRecord, '2022-03-12 11:46:25' as dateRecord, 1 as isStatusExcluded UNION ALL
	SELECT 15 as idRecord, '2022-05-11 12:20:12' as dateRecord, 1 as isStatusExcluded
)
SELECT DISTINCT idRecord
	,[Month]
	,NULL AS isStatusExcluded
FROM [D_Date] a
CROSS JOIN CTE b
WHERE [Year_FR] IN (
		2022
		,2022 - 1
		)
	AND NOT EXISTS (
		SELECT 1
		FROM CTE c
		WHERE b.idRecord = c.idRecord
			AND CONVERT(VARCHAR(7), a.[Month], 126) = CONVERT(VARCHAR(7), c.dateRecord, 126)
		)
UNION ALL
SELECT idRecord, dateRecord, isStatusExcluded 
FROM CTE

J'obtiens :
Nom : Result.png
Affichages : 99
Taille : 16,8 Ko

je pense être sur la bonne voie, merci pour les conseilles.
Je vais continuer d'avancer la dessus en milieu d'après midi.

YearMonth entre 2 dates en fonction d'un statut

Développement SQL Server

Discussions similaires

Partager

Partager