Discussion :

[gegila]

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 :

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
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 :

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

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

[gegila]

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

Code :

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

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

[acaumes]

Bonjour,

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

Code :

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

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 :

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

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

[gegila]

Effectivement c'est OK

Merci beaucoup pour ton Aide

[acaumes]

Effectivement c'est OK

Si c'est OK => RESOLU!

[SergioMaster]

Bonjour,

ça pique un peu.

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

Code :

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

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
ni d'ailleurs le tri sur la date (pour l'instant)

Code :

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

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 :

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

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 :

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

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 :

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

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

[gegila]

Merci pour tes réponses Sergio

Effectivement

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

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

[SergioMaster]

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

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"?

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

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

[gegila]

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

[SergioMaster]

Je te remercie beaucoup Serge

De nada, remercie moi en plussant la solution.

[gegila]

Re Bonjour Sergio

Satisfait de ta première proposition,

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

Code :

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

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

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 :

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

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

[SergioMaster]

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

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
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

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

sans , c'est à l'aveugle