Discussion :

[pekre]

bonjour,
j'ai une table historique qui contient un champ de type date nommé mydate.
je voudrais ecrire une requete sur cette table mais je n'arrive pas.niveau debutant en sql server.voici ce que la requete doit faire:
- on ne touche pas aux enregistrements dont la date est moins de 18 mois (cad date_du_jour - mydate< 18 mois)
- on conserve que le dernier jour du mois(par exemple 30 septembre ,31 octobre,etc) des enregistrements dont les dates sont comprises entre 18 mois et 60 mois (cad 18mois<date_du_jour - mydate< 60 mois)
-on supprime les enregistrements dont les dates sont supérieures à 60 mois (cad date_du_jour - mydate> 60 mois)

merci de votre aide

[Invité]

Peux-tu nous montrer ce que tu as fait avant de faire tout le travail à ta place ?

[aieeeuuuuu]

Bonjour

quelque chose comme ceci (pas testé) :

Code :

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

1
2
3
4
5
6
7
8
9
 
DELETE FROM LaTable
WHERE 
    LaColonneDate < DATEADD(MONTH, -60, GETDATE())
    OR (
        LaColonneDate < DATEADD(MONTH, -18, GETDATE())
        AND
        MONTH(LaColonneDate ) = MONTH(DATEADD(DAY, 1, LaColonneDate ))
    )

[pekre]

avec un peu de reflexion, jai trouvé que c'etait tout simple.voici ce que j'ai fait sous oracle.ça m'air plutôt pas mal.Je l'ai pas encore ecrire en sqlserver

Code :

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

1
2
3
4
5
6
7
8
 
 
delete from histo where mydate not in(
      select  mydate  from histo
      where round(((select sysdate from dual )-mydate)/30)<18
      union
      select  last_day(mydate)  from histo
      where round(((select sysdate from dual )-mydate)/30) between 18 and 60)

[elsuket]

Bonjour,

J'aurai écrit cela, étant donné que c'est ensembliste et cherchable (pas d'appel de fonction ou de calcul sur la colonne mydate) :

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
-- Suppression des lignes entre 18 et 60 mois, excepté le dernier jour de chaque mois
WITH
	N1 AS(SELECT 0 AS a UNION ALL SELECT 0)
	, N2 AS(SELECT A.a FROM N1 AS A CROSS JOIN N1 AS B)
	, N3 AS(SELECT A.a FROM N2 AS A CROSS JOIN N2 AS B)
	, N4 AS(SELECT A.a FROM N3 AS A CROSS JOIN N3 AS B)
	, N AS
	(
		SELECT	ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS n
			, DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0) AS premier_jour_du_mois
		FROM	N4
	)
	, A_SUPPRIMER AS
	(
		SELECT	premier_jour_du_mois
			, DATEADD(day, -1, DATEADD(month, -n, premier_jour_du_mois)) AS avant_dernier_jour_du_mois
		FROM	N
		WHERE	n BETWEEN 18 AND 60
	)
DELETE FROM	dbo.historique 
FROM		dbo.historique AS H
INNER JOIN	A_SUPPRIMER AS A
			ON H.mydate BETWEEN A.premier_jour_du_mois AND A.avant_dernier_jour_du_mois
 
-- Suppression des lignes plus anciennes que 60 mois
DELETE FROM dbo.historique
WHERE mydate < DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) - 60, 0)

@++

[ypelissier]

Salut,
Je ne suis pas un débutant en SQL sauf en ce qui concerne les requêtes récursives mais alors là, je n'ai rien compris à la construction de la requête !!!

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
-- Suppression des lignes entre 18 et 60 mois, excepté le dernier jour de chaque mois
WITH
	N1 AS(SELECT 0 AS a UNION ALL SELECT 0)
	, N2 AS(SELECT A.a FROM N1 AS A CROSS JOIN N1 AS B)
	, N3 AS(SELECT A.a FROM N2 AS A CROSS JOIN N2 AS B)
	, N4 AS(SELECT A.a FROM N3 AS A CROSS JOIN N3 AS B)
	, N AS
	(
		SELECT	ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS n
			, DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0) AS premier_jour_du_mois
		FROM	N4
	)
	, A_SUPPRIMER AS
	(
		SELECT	premier_jour_du_mois
			, DATEADD(day, -1, DATEADD(month, -n, premier_jour_du_mois)) AS avant_dernier_jour_du_mois
		FROM	N
		WHERE	n BETWEEN 18 AND 60
	)
DELETE FROM	dbo.historique 
FROM		dbo.historique AS H
INNER JOIN	A_SUPPRIMER AS A
			ON H.mydate BETWEEN A.premier_jour_du_mois AND A.avant_dernier_jour_du_mois

Pourrais-tu expliquer un petit peu ?

Merci

[SQLpro]

La requête est nullement récursive. C'est un simple appel de CTE.
A lire : http://sqlpro.developpez.com/cours/s...te-recursives/

A +

[elsuket]

Bonjour,

Pardon de ne pas avoir commenté le code.

Commençons par les premières CTE, nommées N[n] : le but de celles-ci est de générer une ensemble de nombres, puisque j'ai supposé que vous ne disposez pas d'une telle table utilitaire.
Lorsqu'on écrit SELECT 0 AS a UNION ALL SELECT 0, nous obtenons deux lignes. Les CTE suivantes sont des produits cartésiens, donc à chaque nouvelle CTE, on augmente le nombre de lignes du carré de la précédente.
Ici nous avions besoin des nombres 18 à 60, donc avec la quatrième CTE, nous avons assez de lignes (et leur unique colonne est à zéro).

Dans la CTE nommée N, l'appel à la fonction ROW_NUMBER() permet de générer les nombres 1 à 256, auquel on associe la date du premier jour du mois, que l'on obtient avec DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0).
Dans cette dernière expression, 0 est en fait automatiquement transtypé par SQL Server au type datetime, ce qui équivaut au 1er Janvier 1900 (cf. SELECT CAST(0 AS datetime)).
Vous pouvez vous référer au billet que j'ai écrit sur le calcul de dates remarquables, ici.

Dans la CTE nommée A_SUPPRIMER, l'expression DATEADD(month, -n, premier_jour_du_mois) soustrait le nombre de mois, c'est à dire le nombre n généré par la CTE nommée N.
Donc par cet appel, nous obtenons toutes les dates de premiers jours du mois pour les mois compris entre 18 et 60 mois dans le passé.
Nous embarquons cette valeur dans l'expression DATEADD(day, -1, DATEADD(month, -n, premier_jour_du_mois)) : nous enlevons donc un jour à la date de premier jour du mois : nous obtenons donc le dernier jour du mois précédent.
Cela permet de ne pas avoir à gérer la complexité des années bissextiles pour le calcul du dernier jour du mois de Février, plus les différences 30/31 jours suivant les mois.
C'est ainsi qu'on obtient la liste de tous les derniers jours du mois pour les 18 à 60 derniers mois.

Dès lors, il suffit de réaliser une jointure sur la colonne date de la table cible : c'est celle qui est dans le DELETE.

C'est pour cela que j'abuse des CTE : elles permettent d'exploiter toute la puissance de SQL / T-SQL dans une même requête.
Cela a pour effet notamment de pouvoir se passer de tables temporaires (#table), de variables de type TABLE, ou d'expressions de requête plus compliquées.
Ici la résolution de la requête est entièrement ensembliste, et il n'y aucune copie de donnée dans TempDB.

@++

[ypelissier]

Merci pour le lien, je le connaissait mais je l'ai seulement vue dans le cadre d'une requête récursive... Il semblerait que les CTE soit un peu plus large et que les requêtes récursives ne face qu'un morceau de ce que peut faire les CTE.
Donc pour me répondre à moi-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
-- Suppression des lignes entre 18 et 60 mois, excepté le dernier jour de chaque mois
WITH
-- N1 fait 2 lignes
	N1 AS(SELECT 0 AS a UNION ALL SELECT 0)
-- N2 fait le carré de N1, donc 4 lignes
	, N2 AS(SELECT A.a FROM N1 AS A CROSS JOIN N1 AS B)
-- N3 fait le carré de N2, donc 16 lignes
	, N3 AS(SELECT A.a FROM N2 AS A CROSS JOIN N2 AS B)
-- N4 fait le carré de N3, donc 256 lignes
	, N4 AS(SELECT A.a FROM N3 AS A CROSS JOIN N3 AS B)
-- N sort une liste numéroté de 1 à 256 avec le premier jour du mois (courant)
	, N AS
	(
		SELECT	ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS n
			, DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0) AS premier_jour_du_mois
		FROM	N4
	)
-- A_SUPPRIMER sort une liste numéroté de 1 à 256 avec le premier jour du mois (courant) et le début dernier jour du mois considéré
	, A_SUPPRIMER AS
	(
		SELECT	premier_jour_du_mois
			, DATEADD(day, -1, DATEADD(month, -n, premier_jour_du_mois)) AS avant_dernier_jour_du_mois
		FROM	N
		WHERE	n BETWEEN 18 AND 60
	)
DELETE FROM	dbo.historique 
FROM		dbo.historique AS H
INNER JOIN	A_SUPPRIMER AS A
-- J'émets des doutes sur cette ligne car A.premier_jour_du_mois > A.avant_dernier_jour_du_mois
			ON H.mydate BETWEEN A.premier_jour_du_mois AND A.avant_dernier_jour_du_mois

Mais merci, j'ai pu comprendre un petit peu l'intérêt des premières requête qui ne servent qu'à faire des lignes numérotées de 1 à ((2²)²)²

J'aurais fait ça du coup

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
-- Suppression des lignes entre 18 et 60 mois, excepté le dernier jour de chaque mois
WITH
-- N1 fait 2 lignes
	N1 AS(SELECT 0 AS a UNION ALL SELECT 0)
-- N2 fait le carré de N1, donc 4 lignes
	, N2 AS(SELECT A.a FROM N1 AS A CROSS JOIN N1 AS B)
-- N3 fait le carré de N2, donc 16 lignes
	, N3 AS(SELECT A.a FROM N2 AS A CROSS JOIN N2 AS B)
-- N4 fait le carré de N3, donc 256 lignes
	, N4 AS(SELECT A.a FROM N3 AS A CROSS JOIN N3 AS B)
-- N sort une liste numéroté de 1 à 256 avec le premier jour du mois (courant)
	, N AS
	(
		SELECT	ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS n
			, DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0) AS premier_jour_du_mois_courant
		FROM	N4
	)
-- A_SUPPRIMER sort une liste numéroté de 1 à 256 avec le premier jour du mois (courant) et le début dernier jour du mois considéré
	, A_SUPPRIMER AS
	(
		-- Cette ligne là change
		SELECT	DATEADD(month, -n-1, premier_jour_du_mois_courant) premier_jour_du_mois
			, DATEADD(day, -1, DATEADD(month, -n, premier_jour_du_mois_courant)) AS avant_dernier_jour_du_mois
		FROM	N
		WHERE	n BETWEEN 18 AND 60
	)
DELETE FROM	dbo.historique 
FROM		dbo.historique AS H
INNER JOIN	A_SUPPRIMER AS A
			ON H.mydate BETWEEN A.premier_jour_du_mois AND A.avant_dernier_jour_du_mois

Et encore, un puriste pourrait dire que le dernier jour du mois à 00:00:00 doit être conservé car c'est la première seconde du dernier jour du mois...

P.S. : Nos messages se sont croisées En tout cas, super, merci pour les détails

[elsuket]

Effectivement l'ajout de la ligne DATEADD(month, -n-1, premier_jour_du_mois_courant) premier_jour_du_mois n'est pas mal du tout.

Ravi de vous avoir aidé

@++