PB avec deux Requêtes SQL

[PaulNero]

Bonjour,
depuis des années j'utilise cette requête dans un DTS pour me charger des données dans un fichier chaque début du mois.Il charge ainsi les données du mois précédent:

Code sql :

1
2
3
SELECT numero_cli, nom, prenom,date_encais,numero_cli_fact, date_rembours, num_fact,type_fact FROM Client INNER JOIN facture ON Client.numero_cli=facture.numero_cli_fact
WHERE year(date_encais)=getdate() AND
month(date_encais)=month(getdate())-1

le soucis c'est qu'au mois de janvier,il devrait me charger les données du mois de décembre de l'année précédente, mais il ne les trouve pas et c'est normal, car getdate() renvoit la date en cours.

j'ai ainsi opté pour cette procédure T-SQL:

Code sql :

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
USE madatabase;
begin
  declare @year_sec date
  declare @month_sec month
SET @year_sec=year(gatdate())
SET @month_sec=month(gatedate())
IF @month_sec=1 
     SET @month_sec=12
     SET @year_sec= @year_sec -1
     begin
         SELECT numero_cli, nom, prenom,date_encais,numero_cli_fact,
         date_rembours, num_fact,type_fact FROM Client INNER JOIN facture ON  
         Client.numero_cli=facture.numero_cli_fact
         WHERE year(date_encais)=@year_sec AND
         month(date_encais)=@month_sec
     end
else
     begin
         SELECT numero_cli, nom, prenom,date_encais,numero_cli_fact,
         date_rembours, num_fact,type_fact FROM Client INNER JOIN facture ON  
         Client.numero_cli=facture.numero_cli_fact
         WHERE year(date_encais)=@year_sec AND
         month(date_encais)=@month_sec
     end
 
end

Pensez vous que cela est optimisé? des idées?

[Kropernic]

Pourquoi ne pas faire ceci ?

Code sql :

1
2
3
4
5
6
7
SELECT
                 numero_cli, nom, prenom,date_encais,numero_cli_fact, date_rembours, num_fact,type_fact 
FROM 
        Client INNER JOIN facture ON Client.numero_cli=facture.numero_cli_fact
WHERE
        date_encais between datediff(month,date_encais,-1) and getdate()

N.B. : j'ai écrit datediff de tête, la syntaxe est à vérifier.

Il faudra juste p-e adapter les bornes de l'opérateur between pour correspondre exactement à votre besoin.

[PaulNero]

Merci de ta participation,
mais je pense pas que datediff résoudra le problème de janvier!!
en effet, si je suis le 2 janvier 2013 et que je veuille récupérer les données du mois d'avant, j'ai un getdate() qui me retournera 2013 et si je fais month -1 j'ai "0" donc la requête ne retournera rien du tout.


cordialement

[Kropernic]

Et bien justement non.

C'est là tout l'intérêt de travailler sur les types de données corrects.

Tu veux faire un traitement sur une date (obtenir la date un mois avant dans ce cas). Il faut donc travailler avec des dates et les fonctions appropriées.

La fonction DATEDIFF renverra une date (valide!) qui correspondra à ce qui lui a été passé en paramètre.

En utilisant la fonction MONTH(qui retourne un entier!), tu sors du "domaine" des dates et obtient donc des résultats farfelus en appliquant des traitements à priori triviaux dessus.

Tout le monde sait bien que le mois avant janvier est décembre (même les SGDBR) mais si tu lui dis de prendre l'entier avant 1, il est normal qu'il te réponde que c'est 0. J'en ferais de même.

[PaulNero]

En utilisant la fonction MONTH(qui retourne un entier!), tu sors du "domaine" des dates et obtient donc des résultats farfelus en appliquant des traitements à priori triviaux dessus.

j'ai beaucoup rigolé je t'assure.
bon devenons sérieux.

si je te prends au mot, cela donnerait ceci dans ma requête:

Code sql :

1
2
3
4
5
6
7
SELECT
                 numero_cli, nom, prenom,date_encais,numero_cli_fact, date_rembours, num_fact,type_fact 
FROM 
        Client INNER JOIN facture ON Client.numero_cli=facture.numero_cli_fact
WHERE
        date_encais = datediff(month,date_encais,-1) AND 
      date_encais=datediff(year,date_encais,1)

mais il me semble que si j'exécute ça le 5 du mois par exemple, il me prendra les données entre le 1er et le 5?

cordialement

[PaulNero]

En utilisant la fonction MONTH(qui retourne un entier!), tu sors du "domaine" des dates et obtient donc des résultats farfelus en appliquant des traitements à priori triviaux dessus.

j'ai beaucoup rigolé je t'assure.
bon devenons sérieux.

si je te prends au mot, cela donnerait ceci dans ma requête:

Code sql :

1
2
3
4
5
6
7
SELECT
                 numero_cli, nom, prenom,date_encais,numero_cli_fact, date_rembours, num_fact,type_fact 
FROM 
        Client INNER JOIN facture ON Client.numero_cli=facture.numero_cli_fact
WHERE
        date_encais = datediff(month,date_encais,-1) AND 
      date_encais=datediff(year,date_encais,1)

mais il me semble que si j'exécute ça le 5 du mois par exemple, il me prendra les données entre le 1er et le 5?

cordialement

[Kropernic]

Content de t'avoir fait rire. Il y a 2 ans (avant que je m'investisse personnellement dans l'apprentissage du monde des DB), je faisais comme toi pour ce genre de chose en utilisant la fonction Month et en me faisant ch*** pour gérer les débuts et fin d'années.

Puis j'ai compris qu'il fallait travailler avec les types de données et depuis, ça va beaucoup mieux.

Bref, revenons à ton problème.

Tout d'abord, je tiens à m'excuser car j'ai utilisé la mauvaise fonction... Il s'agit bien sûr de DATEADD et non pas DATEDIFF. DATEDIFF retourne un entier et non pas une date (ce qui fait qu'on retombe dans le genre de cas où ça ne va pas). Il n'empêche que le raisonnement reste le même.

La clause WHERE telle qu'écrite dans ta requête demande que date_encais soit égale à date_encais avec un mois de moins (pour la première condition).

Si je caricature un peu, cela revient à écrire

Code :

WHERE 3 = 3 - 1

On se rend vite compte qu'il y a un souci (mais cela vient p-e de la confusion que j'ai introduite par l'utilisation de la mauvaise fonction).

Si j'ai bien compris, tu veux donc les lignes dont la valeur de la colonnes date_encais se trouve dans le mois précédent le mois en cours.

J'écrirais donc une clause WHERE ressemblant (je reste prudent) à ceci :

Code :

1
2
3
 
WHERE
        date_encais BETWEEN DATEADD(month,-1,@UneDateDeReference) AND @UneDateDeReference

Si la variable @UneDateDeReference est égale, par exemple, à 20130108 (la date d'aujourd'hui quoi), alors, la condition de la clause where que j'ai écrite sera ceci :

Code :

WHERE date_encais BETWEEN '20121208' AND '20130108'

Reste donc à avoir une date de référence qui fasse en sorte que la requête porte sur le premier du mois précédent jusqu'au premier du mois actuel.

Je ferais quelque chose du genre :

Code :

1
2
3
4
 
DECLARE @UneDateDeReference DATE;
 
SET @UneDateDeReference = DATEADD(DAY,-(DAY(GETDATE())-1),GETDATE());

Suite au prochain message, je n'aime pas les pavés... (et aussi car il faut que je me relise et que c'est plus facile au format forum qu'au format texte)

[Kropernic]

Maintenant qu'on a donc notre date de référence, la clause WHERE devient (presque) évidente :

Code :

1
2
 
WHERE date_encais BETWEEN DATEADD(MONTH,-1,@UneDateDeReference) AND @UneDateDeReference

Avec cela, peu importe que tu exécutes la requête le premier ou le 26 du mois, tu auras toujours les lignes dont la date_encais est comprise dans le précédent le mois courant.

[aieeeuuuuu]

Bonjour,

Attention, il n'y a pas que le probleme du jour, mais également des heures, minutes,...

plus simplement, pour "arrondir" une date vous pouvez combiner un DATEADD et un DATEDIFF par rapport à une date arbitraire (0 dans l'exemple ci dessous)

Votre clause WHERE devient donc

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
 
WHERE date_encais BETWEEN 
		DATEADD(
			MONTH
			,DATEDIFF(
				MONTH
				,0
				,GETDATE()
			) - 1
			,0
		) 
AND
		DATEADD(
			MONTH
			,DATEDIFF(
				MONTH
				,0
				,GETDATE()
			)
			,0
		)

(notez quand même que les données du premier jour à minuit pour le mois suivant seront prises en compte, je vous laisse modifier en cas de besoin)

[Kropernic]

Envoyé par aieeeuuuuu

Attention, il n'y a pas que le probleme du jour, mais également des heures, minutes,...

C'est pas faux. Mais la version de sql server n'ayant pas été précisée ni même le DDL des tables, j'ai supposé qu'on était dans un version > 2005 avec une colonne de type DATE et non DATETIME. Ce qui (me) simplifiait beaucoup les choses ^^

Sinon, histoire d'être complet, voici comment j'aurais fait si la colonne était de type DATETIME (Je ne dénigre nullement la manière de faire montrée par aieeeuuuu).

Et donc, avec une colonne de type DATETIME, j'aurais juste exclue la partie time du problème.

Pour la date de référence (j'ai changé le nom de variable par faire plus court) :

Code :

1
2
3
DECLARE @DATE DATETIME;
SET @DATE = DATEADD(DAY,-(DAY(GETDATE())-1),GETDATE());
SET @DATE = CAST(FLOOR(CAST(@DATE AS FLOAT)) AS DATETIME);

La clause restant ainsi inchangée.

(la note signalée par aieeeuuuu en fin de message restant valable).

[Kropernic]

Je me rends compte d'une coquille dans la solution que j'ai proposée.

Telle quelle, le critère de la requête si elle était exécutée aujourd'hui serait du 01/12/2012 au 01/01/2013.

De ce fait, cela peut être problématique.

Si la colonne est de type DATE, il faut bien sûr retirer un jour à la borne supérieur.

Si la colonne est de type DATETIME, là ça marche car, implicitement, sql server ajoute 00:00:00.000 comme composante temporelle.

[PaulNero]

Bonjour,
et désolé pour hier, je suis rentré trop tôt.
Quand je lis le fil de la discussion, je me rends compte qu'on a toujours les problèmes du premier jour du mois suivant.Or, moi si j'exécute mon DTS le 3 janvier 2013, je soushaite avoir les données de 1/12/2012 au 31/12/2012 à minuit.
C'est pour éviter les contraintes de datetime et smalldatetime que j'ai préféré gerer des entiers, d'où mon premier script T-sql.


cordialement

[Kropernic]

Quel est le type de donnée de la colonne date_encais (colonne sur laquelle s'effectue le filtre) ?

Il n'y a pas de problème, il faut juste bien comprendre comment fonctionne sql server.

Si la colonne est de type DATETIME (ce qui était d'office le cas avec sql server 2005 et antérieures), quand on écrit une condition du genre

Code :

date_encais BETWEEN '20121201' AND '20121231'

sql server "traduit" cela en

Code :

date_encais BETWEEN '20121201 00:00:00.000' AND '20121231 00:00:00.000'

Ce qui fait que les ventes de la journée du 31 décembre seront exclues de la requête.

De ce fait, toujours en partant de la formule de calcule d'une date de référence que j'ai donnée précédemment, le filtre de la clause where est

Code :

date_encais BETWEEN dateadd(month,-1,@ladate) AND @ladate

(ici, @ladate est donc le 01/01/2013)
En remplaçant les fonctions et variables par leur valeur, on a donc le filtre suivant qui correspond à ce qui est souhaiter :

Code :

date_encais BETWEEN '20121201 00:00:00.000' AND '20130101 00:00:00.000'

Le seul risque est d'avoir une transaction qui se serait déroulée pile à premier janvier à 0 heures, 0 secondes et 0 milisecondes. Soit on accepte ce risque (c'est ce que je fais tous les jours dans mon travail car les magasins ne sont pas ouverts à cette heure-là dans la boîte où je bosse), soit on ajoute un condition à la clause where pour exclure cet instant.

Si la colonne est de type date, sql server ne "traduit" rien du tout vu qu'il n'a pas à ajouter de composante temporelle pour coller au type de donnée.

Du coup, toujours avec la même valeur pour la variable @ladate, une condition telle qu'écrite ci-dessus intégrera les données du premier janvier qui ne devraient pas l'être. Il faut donc y retirer un jour.

[aieeeuuuuu]

Envoyé par PaulNero

je soushaite avoir les données de 1/12/2012 au 31/12/2012 à minuit.

Je suppose que vous vouliez dire du 1/12/2012 au 31/12/2012 à 23:59:59.999...
comme le dit Kropernic, cela dépend du type de votre colonne, mais dans la requete que je vous ai proposée, seules les données du 1/01/2013 à 0:00:00.000 seront incluses.
Pour éviter ce cas, vous pouvez modifier dans la clause WHERE que je vous ai proposée le BETWEEN par un >= et un < :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
 
WHERE date_encais >=
		DATEADD(
			MONTH
			,DATEDIFF(
				MONTH
				,0
				,GETDATE()
			) - 1
			,0
		) 
AND date_encais <
		DATEADD(
			MONTH
			,DATEDIFF(
				MONTH
				,0
				,GETDATE()
			)
			,0
		)

[Kropernic]

Envoyé par aieeeuuuuu

Je suppose que vous vouliez dire du 1/12/2012 au 31/12/2012 à 23:59:59.999...
comme le dit Kropernic, cela dépend du type de votre colonne, mais dans la requete que je vous ai proposée, seules les données du 1/01/2013 à 0:00:00.000 seront incluses.
Pour éviter ce cas, vous pouvez modifier dans la clause WHERE que je vous ai proposée le BETWEEN par un >= et un < :

Code :

Je n'avais jamais pensé à utilisé 0 comme date de référence pour faire des comparaison. J'aime assez ^^.

Je me demande ce qui est conseillé en pratique par les "experts" du monde sql server.

Sinon, j'imagine qu'il est toujours possible de retirer une milliseconde à la borne supérieure de l'intervalle voulu XD.
Reste à savoir ce qui est le plus performant entre between et <=, > (mais p-e que l'optimiseur de requête traduit cela en between )

[SQLpro]

Envoyé par Kropernic

Sinon, j'imagine qu'il est toujours possible de retirer une milliseconde à la borne supérieure de l'intervalle voulu XD.

Non, car le type DATETIME est précis à 3 ms. En retirant 1 ms vous patinez sur le même résultat... Exemple :

Code :

SELECT DATEADD(ms, -1, CAST('20121231 00:00:00.000' AS DATETIME))

A +

[Jean.Cri1]

tu peux aussi essayer quelque chose comme :

Code :

datediff(mm,date_encais,dateadd(mm,-1,getdate())=0

[aieeeuuuuu]

Envoyé par Jean.Cri1

tu peux aussi essayer quelque chose comme :

Code :

datediff(mm,date_encais,dateadd(mm,-1,getdate())=0

Je pense que ça ne résoudra pas le problème de l'heure, mais surtout ce n'est pas sargable : le moteur ne pourra pas profiter d'un éventuel index sur la colonne date, car il devra appliquer la fonction sur chaque ligne pour voir si la condition est vérifiée.

[Jean.Cri1]

AMHA ca resoud le probleme de l'heure et ce n'est pas plus penalisant que les

Code :

year(date_encais) et month(date_encais)

de la requete initiale

[Kropernic]

C'est justement ce qui est évité avec les méthodes proposées par aieeeuuuu et moi-même.

Lorsqu'une fonction est appliquée sur une colonne de la clause WHERE, cela rend impossible l'utilisation de l'index approprié (à condition qu'il existe évidemment).

Ce n'est donc pas plus pénalisant que la méthode de la requête initiale certes. Mais cela n'en reste pas moins pas optimal pour les performances.