Discussion :

[Kropernic]

Hello,

Cela a très certainement déjà été demandé à de nombreuses reprises mais je ne parviens pas à mettre la main dessus.

Considérons, par exemple, la table d'addresses suivantes (example simplifié pour focaliser le besoin):

Code :

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

1
2
3
4
5
6
7
8
CREATE TABLE T_ADDRESS_ADR (	ADR_ID INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
	CLI_ID INT NOT NULL REFERENCES T_CLIENT_CLI.CLI_ID,
	ADR_START_DATE DATETIME2 NOT NULL,
	ADR_END_DATE DATETIME2 NOT NULL DEFAULT '99991231',
	ADR_LINE1 VARCHAR(500),
	ADR_LINE2 VARCHAR(500),
	ADR_LINE3 VARCHAR(500)
)

Le besoin est de récupérer l'adresse actuelle ou la dernière valable.

Actuellement, j'y parviens à base de CTE et de la fonction de fenêtrage ROW_NUMBER.

Cela donne quelque chose dans ce genre-ci :

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
WITH ADR_TMP AS (	SELECT	ADR_ID, CLI_ID, ADR_START_DATE, ADR_END_DATE, ADR_LINE1, ADR_LINE2, ADR_LINE3, ROW_NUMBER() OVER(PARTITION BY CLI_ID ORDER BY ADR_END_DATE DESC, ADR_START_DATE DESC) AS 'RN'
	FROM	T_ADDRESS_ADR
),
ADR AS (
	SELECT	ADR_ID, CLI_ID, ADR_START_DATE, ADR_END_DATE, ADR_LINE1, ADR_LINE2, ADR_LINE3
	FROM	T_ADDRESS_ADR
	WHERE	GETDATE() BETWEEN ADR_START_DATE AND ADR_END_DATE
	UNION
	SELECT	T.ADR_ID, T.CLI_ID, T.ADR_START_DATE, T.ADR_END_DATE, T.ADR_LINE1, T.ADR_LINE2, T.ADR_LINE3
	FROM	T_ADDRESS_ADR T
				INNER JOIN ADR_TMP V
					ON	T.ADR_ID = V.ADR_ID
	WHERE	V.RN = 1
)
 
 
SELECT	*
FROM	ADR
WHERE	CLI_ID = XXX

Cela fonctionne mais n'y aurait-il pas mieux ?

Merci d'avance.

[Séb.]

Cela ne revient pas à chercher pour ton client la ligne avec MAX(ADR_END_DATE) ?

Code :

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

1
2
3
4
5
6
7
8
SELECT TES_COLONNES
FROM T_ADDRESS_ADR
WHERE (CLI_ID, ADR_END_DATE) = (
	SELECT CLI_ID, MAX(ADR_END_DATE)
	FROM T_ADDRESS_ADR
	WHERE CLI_ID = XXX
	GROUP BY CLI_ID
)

D'ailleurs il faudra une contrainte d'unicité (CLI_ID, ADR_END_DATE).

[Kropernic]

Non car on pourrait avoir une ligne pour une adresse qui commence dans le futur par exemple.

N.B. : J'ai pris le cas des adresses pour l'example mais il y a le même problème avec tout un tas d'autres tables qui tournent autour du client où le problème est chaque fois d'avoir l'info courante ou la dernière valide.

[Séb.]

Je ne comprends pas. Peux-tu donner un échantillon et le résultat attendu ?

[Waldar]

Cela ne revient pas à chercher pour ton client la ligne avec MAX(ADR_END_DATE) ?

Non, si il y a une adresse préremplie pour le futur, ça bloquera l'adresse courante.

La requête initiale me paraît plutôt correcte, vous pouvez toutefois vous passer de la jointure entre ADR_TMP et T_ADRESSE puisque vous avez déjà tout dans ADR_TMP.
Il est possible que dans ce cas un OR soit plus efficace que l'UNION (car les deux filtres me paraissent difficilement indexable) :

Code :

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

1
2
3
4
5
6
7
8
9
10
with CTE_ADR_TMP as
(
select ADR_ID, CLI_ID, ADR_START_DATE, ADR_END_DATE, ADR_LINE1, ADR_LINE2, ADR_LINE3, ADR_START_DATE, ADR_END_DATE
     , row_number() over(partition by CLI_ID order by ADR_END_DATE desc, ADR_START_DATE desc) as rn
  from T_ADDRESS_ADR
)
select ADR_ID, CLI_ID, ADR_START_DATE, ADR_END_DATE, ADR_LINE1, ADR_LINE2, ADR_LINE3
  from CTE_ADR_TMP
 where rn = 1
    or getdate() between ADR_START_DATE and ADR_END_DATE;

[Séb.]

Non, si il y a une adresse préremplie pour le futur, ça bloquera l'adresse courante.

Dans ce cas si on ne regarde que les lignes dont ADR_START_DATE est < à la date courante ?

Code :

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

1
2
3
4
5
6
7
8
9
10
SELECT TES_COLONNES
FROM T_ADDRESS_ADR
WHERE (CLI_ID, ADR_END_DATE) = (
	SELECT CLI_ID, MAX(ADR_END_DATE)
	FROM T_ADDRESS_ADR
	WHERE TRUE
            AND ADR_START_DATE < CURRENT_DATE
            AND CLI_ID = XXX
	GROUP BY CLI_ID
)

Je serais plus à l'aise avec un échantillon

[Waldar]

Dans ce cas si on ne regarde que les lignes dont ADR_START_DATE est < à la date courante ?

Dans ce cas là tu rates la dernière.

L'énoncé m'est apparu assez clair :

Le besoin est de récupérer l'adresse actuelle ou la dernière valable

Si la dernière date est l'adresse actuelle, on récupère cette ligne. Si ce n'est pas la même on doit récupérer deux lignes:

CLIENT1	ADR1	01/01/2000	31/12/2022	
CLIENT1	ADR2	01/01/2023	31/12/9999	<= c'est l'adresse courante et la dernière

CLIENT2	ADR1	01/01/2000	31/12/2022	
CLIENT2	ADR2	01/01/2023	31/12/2023	<= c'est l'adresse courante
CLIENT2	ADR3	01/01/2024	31/12/9999	<= c'est la dernière

[Kropernic]

Ca faisait des années que je n'étais plus venu sur ce forum. Merci les gars, ça fait plaisir de voir des gars qui comprennent qqch au sql (les devs de nos jours... c'est assez triste).
Pour revenir au sujet, j'ai l'impression que la proposition de Seb est la simplification que je cherche. Si on prend la ligne avec la date de fin la plus grande mais en limitant aux lignes ayant une date de début antérieure à la date du jour, on aura soit :

• la ligne courante dans le cas où il existe une date de fin postérieure à la date du jour
• la dernière ligne valide dans le cas où il n'existe pas de date de fin postérieure à la date du jour.

Il ne me reste plus qu'à voir comment implémenter cela avec entity framework (pas le choix malheureusement). Je passe toujours par le sql en premier pour me faire une idée de ce vers quoi je dois aller.

Encore merci à vous !!

[vanagreg]

Bonjour,

Je pense que tu as juste besoin de conditionner la clause ORDER BY dans la fonction ROW_NUMBER:

Code :

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

1
2
3
4
5
6
7
8
with CTE_ADR_TMP as
(
select ADR_ID, CLI_ID, ADR_START_DATE, ADR_END_DATE, ADR_LINE1, ADR_LINE2, ADR_LINE3, ADR_START_DATE, ADR_END_DATE
     , row_number() over(partition by CLI_ID order by case when getdate() between ADR_START_DATE and ADR_END_DATE then 0 else 1 end,  ADR_END_DATE desc, ADR_START_DATE desc) as rn
  from T_ADDRESS_ADR
)
select * from CTE_ADR_TMP 
where rn = 1;