Discussion :

[ads42]

Bonjour,
je ne sais pas si c'est faisable en SQL (MySQL) mais je vais vous expliquer mon cas.
Sur une facture je peux avoir differentes TVA (par ex 19.6 et 5.5).
J'ai une vue qui me donne un total HT par valeur de TVA.
Prenons un exemple simple :
La vue a 2 champs : Taux_TVA, Total_HT

sur le total de la facture je peux avoir une remise globale (en HT). Cette remise n'a pas de taux de TVA, donc il faut que je la defalque des montants ht.

Jusque la c'est simple, maintenant rentrons dans le concret
sur la TVA a 5.5 j'ai 200E HT.
et sur la TVA a 19.6 j'ai 800E HT.
et j'ai une remise de 300E HT.

Le premier reflexe qu'on peux avoir c'est de dire qu'on l'enleve sur le premier enregistrement, sauf que ici c'est pas possible, il faudrait repartr la valeur a soustraire en fonction fonction du total HT, par ex ici 200E puis 100E.

l'ideal serait bien sur de soustraire proportionnelement mais j'ai peur d'avoir des problemes d'arrondis.

Je debute en SQL, il y a t'il une syntaxe possible pour ce genre de chose ?

Merci

[SQLpro]

Une telle requête est intéressante à faire et pas trop compliquée avec un bon SGBDR comme SQL Server ou PostGreSQL. En MySQL elle est imbitable car il y a tellement dans manque dans cet ersatz de SGBD pas relationnel, que vous allez souffrir !

Voici un exemple de la chose :

1 - les tables

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
CREATE TABLE T_FACTURE_FCT
(FCT_ID              INT NOT NULL PRIMARY KEY,
 FCT_DATE            DATETIME NOT NULL,
 FCT_REMISE_HT       DECIMAL(16,2));
 
CREATE TABLE T_TVA
(TVA_ID              INT NOT NULL PRIMARY KEY,
 TVA_TAUX            FLOAT NOT NULL);
 
CREATE TABLE T_FACTURE_ITEM_FCI
(FCI_ID              INT NOT NULL PRIMARY KEY,
 FCT_ID              INT NOT NULL REFERENCES T_FACTURE_FCT (FCT_ID),
 FCI_DESIGNATION     VARCHAR(32) NOT NULL,
 FCI_MONTANT_HT      DECIMAL(16,2) NOT NULL CHECK (FCI_MONTANT_HT >= 0),
 FCI_QUANTITE        FLOAT NOT NULL DEFAULT 1 CHECK (FCI_QUANTITE > 0),
 TVA_ID              INT REFERENCES T_TVA (TVA_ID));

2 - le jeu d'essais

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
INSERT INTO T_TVA VALUES (1, 7.0);
INSERT INTO T_TVA VALUES (2, 19.6);
 
INSERT INTO T_FACTURE_FCT VALUES (11, '2012-05-10', 300.0);
INSERT INTO T_FACTURE_FCT VALUES (12, '2012-05-11', 100.0);
 
INSERT INTO T_FACTURE_ITEM_FCI VALUES (101, 11, 'Machin', 200.0, 1, 1)
INSERT INTO T_FACTURE_ITEM_FCI VALUES (102, 11, 'Truc', 400.0, 2, 2)
 
INSERT INTO T_FACTURE_ITEM_FCI VALUES (111, 12, 'Bidule', 300.0, 2, 1)
INSERT INTO T_FACTURE_ITEM_FCI VALUES (112, 12, 'Choses', 100.0, 4, 2)
INSERT INTO T_FACTURE_ITEM_FCI VALUES (113, 12, 'Zloup', 500.0, 1, 2)

3 - 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
WITH T_REMISE AS
(SELECT DISTINCT 
        SUM(FCI_MONTANT_HT * FCI_QUANTITE) 
           OVER(PARTITION BY T.FCT_ID, TVA_ID) / 
        SUM(FCI_MONTANT_HT * FCI_QUANTITE) 
           OVER(PARTITION BY T.FCT_ID) 
           * FCT_REMISE_HT AS RATIO,
        T.FCT_ID, TVA_ID 
 FROM   T_FACTURE_ITEM_FCI AS I
        INNER JOIN T_FACTURE_FCT AS T
              ON I.FCT_ID = T.FCT_ID)
SELECT FCI_ID, FCT_ID, FCI_DESIGNATION, 
       FCI_MONTANT_HT, FCI_QUANTITE, TVA_ID
FROM   T_FACTURE_ITEM_FCI
UNION  ALL
SELECT NULL AS FCI_ID, FCT_ID, 
       'Remise ventilée' AS FCI_DESIGNATION,
       RATIO AS FCI_MONTANT_HT, 1 AS FCI_QUANTITE,
       TVA_ID
FROM   T_REMISE
ORDER BY FCT_ID, FCI_ID NULL LAST

4 - le résultat

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
FCI_ID      FCT_ID      FCI_DESIGNATION         FCI_MONTANT_HT         FCI_QUANTITE           TVA_ID
----------- ----------- ----------------------- ---------------------- ---------------------- -----------
101         11          Machin                  200                    1                      1
102         11          Truc                    400                    2                      2
NULL        11          Remise ventilée         60                     1                      1
NULL        11          Remise ventilée         240                    1                      2
 
111         12          Bidule                  300                    2                      1
112         12          Choses                  100                    4                      2
113         12          Zloup                   500                    1                      2
NULL        12          Remise ventilée         40                     1                      1
NULL        12          Remise ventilée         60                     1                      2

Malheureusement, MySQL n'implémente ni les CTE (à lire), ni les fonctions de fenêtrage (à lire), ni le positionnement des NULLs dans l'ORDER BY !

À lire sur cet ersatz de SGBDR qu'est MySQL...

A +

[ads42]

Merci Frédéric pour ton aide.
Ta requete telle que tu la postee concerne un sgbd de type SQLServer ou c'est la version complexe pour MySQL ?

car dans MySQL j'ai cette erreur :
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WITH T_REMISE AS (SELECT DISTINCT SUM(FCI_MONTANT_HT * FCI_QUANTITE) ' at line 1

Merci

[CinePhil]

Tu n'as pas dû lire son message jusqu'au bout car il a bien dit :

Malheureusement, MySQL n'implémente ni les CTE (à lire), ni les fonctions de fenêtrage (à lire), ni le positionnement des NULLs dans l'ORDER BY !

À lire sur cet ersatz de SGBDR qu'est MySQL...

Je te renvoie à son message pour les liens qui te donneront de la lecture.

MySQL ne connait tout simplement pas le mot-clé WITH.
Ça doit pouvoir se contourner par l'utilisation d'une table temporaire. Par contre, OVER PARTITION ne fonctionnera pas non plus avec MySQL et la requête va devenir beaucoup plus complexe.

[ads42]

Effectivement, MySQL est assez limite, mais j'ai pas le choix
Je vais essayer de le faire directement dans un script....

[ads42]

bon je me suis resolu a utiliser plutot un defalcage de la remise de maniere proportionelle.

Pour eviter d'alourdir la requete (et ralentir le serveur), je voulais mettre certaines donnees dans des variables, mais en fait la variable ne change pas toujours en fonction des enregistrements :
Voila ma requette :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
SELECT order_lines.id_order, 
order_lines.vat_rate AS tauxTVA, 
sum( order_lines.subtotal_discounted ) AS totalHTRemise, 
orders.discount AS remiseGlobale, 
(SELECT SUM( order_lines.subtotal_discounted )
 FROM order_lines INNER JOIN orders ON ( order_lines.id_order = orders.id_order )
) AS totalHT, 
@totalHTFinal := gettotalhtremise(sum( order_lines.subtotal_discounted ) , orders.discount, (SELECT SUM( order_lines.subtotal_discounted )
 FROM order_lines INNER JOIN orders ON ( order_lines.id_order = orders.id_order )
)) AS totalHTFinal,
@totalHTFinal * (100 + order_lines.vat_rate) / 100 AS totalTTC
FROM order_lines
INNER JOIN orders ON ( order_lines.id_order = orders.id_order )
GROUP BY order_lines.vat_rate

je n'ai pas voulu le faire de partout pour le moment, mais notez la variable @totalHTFinal qui me donne 2 fois la meme valeur.

Une idee d'ou vient le probleme ?
Je voudrais eviter de mettre plusieurs fois les memes SUM ou select dans ma requette.

Merci