Aide sur Requête SQL dans base SQLITE

Bonjour,
Je voudrais implémenter un Calcul de Budget dans mon petit Programme
Je travaille sur DELPHI 10.4.1 et Sqlite comme base de données
J’ai une table : Details Cpt, Budgets_Dep et Budgets Recettes
Voici la structure de mes 3 Tables :

Code:

---

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

CREATE TABLE DetailsCpt ( IdDetails INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, IdCpt INTEGER NULL, DateEnregistre DATE NOT NULL, Types VARCHAR(50) NOT NULL, NumChq VARCHAR(7) NULL, Tiers VARCHAR(80) NOT NULL, Categories VARCHAR(50) NOT NULL, Debit NUMERIC(7,2) NULL DEFAULT 0, Credit NUMERIC(7,2) NULL DEFAULT 0, CONSTRAINT Comptes_DetailsCpt FOREIGN KEY (IdCpt) REFERENCES Comptes (IdCpt) ON DELETE RESTRICT ON UPDATE RESTRICT ); CREATE TABLE [Budgets_Depenses] ( IdBudgetD INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, DteDebut DATE NULL, DteFin DATE NULL, Categories VARCHAR(50) NOT NULL, Periodicite VARCHAR(50) NOT NULL, MontDepenses NUMERIC(7,2) NULL, IdDetail INTEGER NULL );   CREATE TABLE Budgets_Recettes ( IdBudgetR INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, DteDebut DATE NULL, DteFin DATE NULL, Categories VARCHAR(50) NOT NULL, Periodicite VARCHAR(50) NOT NULL, MontRecettes NUMERIC(7,2) NOT NULL DEFAULT 0.00, IdDetail INTEGER NULL );

---

Dans mes 3 tables j’ai un Champ Categories que je voudrais exploiter
Mon but serait d’obtenir :
A partir de la table DetailsCpt pouvoir comparer chaque catégories (dont je fais le regroupement) à la catégorie des Dépenses et Recettes Budgétées et en calculer l’écart.
Exemple :
Categories Bugétée Réel Ecart
Santé 250.00 300,00 +100

Voici la requête SQL que j’utilise :

Code:

---

1 2 3 4 5

SELECT U.Categories FROM DetailsCpt AS U INNER JOIN Budgets_Recettes AS R ON U.Categories = R.Categories INNER JOIN Budgets_Depenses AS D ON U.Categories = D.Categories

---

Je ne maitrise pas les jointures, et j’ai besoin de votre aide précieuse.
Par avance Merci

Une petite correction , en fait voici la requête utilisée réelement

Code:

---

1 2 3 4 5 6 7

SELECT U.Categories , Sum(Debit) AS Depenses ,Sum(Credit) As Recettes, R.Categories ,D.categoriesv FROM DetailsCpt AS U INNER JOIN Budgets_Recettes AS R ON U.Categories = R.Categories INNER JOIN Budgets_Depenses AS D ON U.Categories = D.Categories GROUP By Categories ORDER BY DateEnregistre Asc

---

Bonjour,

1/ Il manque une catégorisation dans ta requête La voici corrigée:

Code:

---

1 2 3 4 5 6 7 8

SELECT U.Categories , Sum(Debit) AS Depenses ,Sum(Credit) As Recettes, R.Categories ,D.categories FROM DetailsCpt AS U JOIN Budgets_Recettes AS R ON U.Categories = R.Categories JOIN Budgets_Depenses AS D ON U.Categories = D.Categories GROUP By r.Categories ORDER BY DateEnregistre Asc

---

2/ Tu fais un tri sur la date d'enregistrement qui me semble inutile ...

3/ Un jeu de données du coup ce serait pas si mal...

4/ Si j'ai bien compris il te faudrait une CTE pour calculer les sommes puis tu fais ta requête ensuite (par exemple ...)

Code:

---

1 2 3 4 5

with Sommes as ( select categories, Sum(Debit) AS Depenses ,Sum(Credit) As Recettes from detailscpt group by categories) select categories, recettes - depenses from sommes

---

Cordialement

Effectivement c'est OK

Merci beaucoup pour ton Aide

Citation:

---

Effectivement c'est OK

---

Si c'est OK => RESOLU!

Bonjour,

ça pique un peu.

Je m'explique si je reprend la dernière requête

Code:

---

1 2 3 4 5 6 7 8

SELECT U.Categories , Sum(Debit) AS Depenses ,Sum(Credit) As Recettes, R.Categories ,D.categories FROM DetailsCpt AS U JOIN Budgets_Recettes AS R ON U.Categories = R.Categories JOIN Budgets_Depenses AS D ON U.Categories = D.Categories GROUP By r.Categories ORDER BY DateEnregistre Asc

---

On a une duplication de colonnes inutiles : R.Categories,D.categories.
Le GROUP BY devrait se faire sur U.Categories
Les jointures ne sont pas suffisamment explicites (Quid quand il n'y a pas de Budget Recettes ou dépenses j'aurais utilisé un LEFT JOIN
et, je ne vois pas à quoi elles servent pour l'instant :koi:
ni d'ailleurs le tri sur la date (pour l'instant)

Code:

---

1 2 3 4 5 6 7

SELECT U.Categories , Sum(Debit) AS Depenses ,Sum(Credit) As Recettes FROM DetailsCpt AS U LEFT JOIN Budgets_Recettes AS R ON U.Categories = R.Categories AND LEFT JOIN Budgets_Depenses AS D ON U.Categories = D.Categories GROUP By r.Categories

---

en résumé seul ceci suffirait

Code:

---

1 2 3

SELECT U.Categories , Sum(Debit) AS Depenses ,Sum(Credit) As Recettes FROM DetailsCpt AS U GROUP By r.Categories

---

mais si l'objectif est de recenser les dépassements de budgets ça coince aux entournures

La date est une donnée à prendre en compte et si l'on veut avoir à la fois les dépassements de recettes et de dépenses les jointures vont être importantes et un impératif sera nécessaire : que les dates début/fin soient les mêmes pour les deux budgets !
Si c'est le cas,je suggérerai déjà de n'avoir qu'une seule table Budgets avec les colonnes mntdepenses et mntrecettes
Si cette fusion est faite alors

Code:

---

1 2 3 4 5 6 7 8 9

WITH C AS ( SELECT R.IDBUDGET,Sum(COALESCE(U.Credit,0)) Credit,SUM(COALESCES(U.DEBIT,0)) Debit FROM Budgets R LEFT JOIN DetailsCpt AS U ON U.Categories = R.Categories AND U.DateEnregistre BETWEEN U.DTEDEBUT AND U.DTEFIN GROUP BY R.IDBUDGET)   SELECT B.CATEGORIES,C.CREDIT,C.DEBIT,(B.MNTRECETTES-C.CREDIT) ECARTRECETTE,(B.MNTDEPENSES-C.Debit) ECART DEPENSE FROM C JOIN BUDGETS B ON B.IDBUDGET=C.IDBUDGET ORDER BY B.CATEGORIES,B.DTEFIN

---

si l'on veut avoir une ligne par budget recettes et dépenses
(*même là une seule table budget aurait suffit avec une colonne pour distinguer RECETTES de DEPENSES)
je proposerai une UNION de deux requêtes pour la CTE

Code:

---

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15

WITH C AS (SELECT R.IDBUDGETR IDB,Sum(COALESCE(U.Credit,0) MONTANTPERIODE FROM Budgets_Recettes R LEFT JOIN DetailsCpt AS U ON U.Categories = R.Categories AND U.DateEnregistre BETWEEN U.DTEDEBUT AND U.DTEFIN GROUP BY R.IDBUDGETR UNION SELECT D.IDBUDGETD,Sum(COALESCE(U.Debit,0) MONTANTPERIODE ECART FROM Budgets_Depenses D LEFT JOIN DetailsCpt AS U ON U.Categories = D.Categories AND U.DateEnregistre BETWEEN D.DTEDEBUT AND D.DTEFIN GROUP BY D.IDBUDGETD)   SELECT B.CATEGORIES,C.CREDIT,C.DEBIT,(COALESCE(r.MNTRECETTES,0)-C.CREDIT) ECARTRECETTE,(COALESCE(d.MNTDEPENSES,0)-C.Debit) ECARTDEPENSE FROM C LEFT JOIN BUDGET_RECETTES R ON R.IDBUDGET=C.IDBUDGET LEFT JOIN BUDGET_DEPENSES D ON D.IDBUDGET=C.IDBUDGET   ORDER BY B.CATEGORIES,R.DTEFIN,D.DTEFIN

---

Je suis loin d'être fan de ces jointures à cause de la partie sur les dates

ATTENTION : Rien ne contrôle que des dates ne se chevauchent pas au sein des tables budgets !

En fait, la structure de la Base me pique pas mal,
1 - je n'aime pas le fait que CATEGORIES ne soit pas liée à une table CATEGORIES(ID,LIBELLE) comme cela a été fait pour idcpt
2 - les tables budgets auraient tout intérêt à être fusionnées

Merci pour tes réponses Sergio

Effectivement

Citation:

---

On a une duplication de colonnes inutiles : R.Categories,D.categories.

---

C'est parce que j'utilise 2 Tables une pour les dépenses et une pour les recettes

Je vais donc essayer en créant une seule table

Citation:

---

ni d'ailleurs le tri sur la date (pour l'instant)

---

C'est que je pense visualiser le budget entre deux dates

Je vais étudier toutes tes remarques et vous tiens au courant de mes conclusions
En effet je ne suis pas très à l'aise avec les jointures !!
Merci Serge, comme toujours remarques très pertinentes

Citation:

---

Envoyé par gegila

C'est parce que j'utilise 2 Tables une pour les dépenses et une pour les recettes

---

Oui mais avec la jointure il est inutile de récupérer les deux colonnes qui de toutes façon auraient des valeurs identiques

Citation:

---

Je vais donc essayer en créant une seule table

---

ok mais selon ma première proposition "une seule table Budgets avec les colonnes mntdepenses et mntrecettes"
ou la seconde "avec une colonne pour distinguer RECETTES de DEPENSES"?

Citation:

---

C'est que je pense visualiser le budget entre deux dates

---

ben ça ce sera dans la clause WHERE pas dans un ORDER BY

Bonjour,

J'ai donc décidé d'utiliser une seule table BUDGETS ou je traite les Dépenses et le Recettes et j'ai appliqué le Code fourni par Sergio

Je viens de tester et cela me donne satisfaction.

Je te remercie beaucoup Serge

Citation:

---

Envoyé par gegila

Je te remercie beaucoup Serge

---

De nada, remercie moi en plussant la solution.

Re Bonjour Sergio

Satisfait de ta première proposition,

Citation:

---

si l'on veut avoir à la fois les dépassements de recettes et de dépenses ....

---

Code:

---

1 2 3 4 5 6 7 8

WITH C AS ( SELECT R.IDBUDGET,Sum(COALESCE(U.Credit,0)) Credit,SUM(COALESCES(U.DEBIT,0)) Debit FROM Budgets R LEFT JOIN DetailsCpt AS U ON U.Categories = R.Categories AND U.DateEnregistre BETWEEN U.DTEDEBUT AND U.DTEFIN GROUP BY R.IDBUDGET)   SELECT B.CATEGORIES,C.CREDIT,C.DEBIT,(B.MNTRECETTES-C.CREDIT) ECARTRECETTE,(B.MNTDEPENSES-C.Debit) ECART DEPENSE FROM C JOIN BUDGETS B ON B.IDBUDGET=C.IDBUDGET ORDER BY B.CATEGORIES,B.DTEFIN

---

Avant de poster le résultat : j'ai voulu essayer ce que tu m'as proposé en deuxième solution sur mono table

Citation:

---

si l'on veut avoir une ligne par budget recettes et dépenses
(*même là une seule table budget aurait suffit avec une colonne pour distinguer RECETTES de DEPENSES)
je proposerai une UNION de deux requêtes pour la CTE

---

Code:

---

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15

WITH C AS (SELECT R.IDBUDGETR IDB,Sum(COALESCE(U.Credit,0) MONTANTPERIODE FROM Budgets_Recettes R LEFT JOIN DetailsCpt AS U ON U.Categories = R.Categories AND U.DateEnregistre BETWEEN U.DTEDEBUT AND U.DTEFIN GROUP BY R.IDBUDGETR UNION SELECT D.IDBUDGETD,Sum(COALESCE(U.Debit,0) MONTANTPERIODE ECART FROM Budgets_Depenses D LEFT JOIN DetailsCpt AS U ON U.Categories = D.Categories AND U.DateEnregistre BETWEEN D.DTEDEBUT AND D.DTEFIN GROUP BY D.IDBUDGETD)   SELECT B.CATEGORIES,C.CREDIT,C.DEBIT,(COALESCE(r.MNTRECETTES,0)-C.CREDIT) ECARTRECETTE,(COALESCE(d.MNTDEPENSES,0)-C.Debit) ECARTDEPENSE FROM C LEFT JOIN BUDGET_RECETTES R ON R.IDBUDGET=C.IDBUDGET LEFT JOIN BUDGET_DEPENSES D ON D.IDBUDGET=C.IDBUDGET   ORDER BY B.CATEGORIES,R.DTEFIN,D.DTEFIN

---

Je teste cette requête directement sur ma Base avec DBBrowser pour Sqlite et non directement dans mon programme

Voila l'erreur

L'exécution s'est terminée avec des erreurs.
Résultat : near "MontPeriode": syntax error
À la ligne 1 :
WITH C AS (SELECT R.IdBudgetD,Sum(COALESCE(U.Credit,0) MontPeriode

Je vois pas la Solution

Merci de ta réponse

Ah, oui désolé :oops:, la saisie à la volée n'a pas de contrôle de syntaxe :ptdr: j'ai oublié quelques parenthèses et autres trucs (en rouge)

Code:

---

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15

WITH C AS (SELECT R.IDBUDGETR IDB,Sum(COALESCE(U.Credit,0)) MONTANT FROM Budgets_Recettes R LEFT JOIN DetailsCpt AS U ON U.Categories = R.Categories AND U.DateEnregistre BETWEEN U.DTEDEBUT AND U.DTEFIN GROUP BY R.IDBUDGETR UNION SELECT D.IDBUDGETD,Sum(COALESCE(U.Debit,0)) FROM Budgets_Depenses D LEFT JOIN DetailsCpt AS U ON U.Categories = D.Categories AND U.DateEnregistre BETWEEN D.DTEDEBUT AND D.DTEFIN GROUP BY D.IDBUDGETD) SELECT B.CATEGORIES,C.CREDIT,C.DEBIT,(COALESCE(r.MNTRECETTES,0)-C.MONTANT) ECARTRECETTE,(COALESCE(d.MNTDEPENSES,0)-C.MONTANT) ECARTDEPENSE FROM C LEFT JOIN BUDGET_RECETTES R ON R.IDBUDGET=C.IDBUDGET LEFT JOIN BUDGET_DEPENSES D ON D.IDBUDGET=C.IDBUDGET ORDER BY B.CATEGORIES,R.DTEFIN,D.DTEFIN

---

Il faut dire que celle-là ne me plait pas des masses. Cela devrait aller mieux.

comme le disait acaumes

Citation:

---

Un jeu de données du coup ce serait pas si mal...

---

sans , c'est à l'aveugle 8-)