juin
2017
Un article de SQLpro
Comment effectuer en une seule requête des rapprochement bancaires pour solder des débits et des crédits ? Cette requête peut répondre à votre attente.
Soit une table constituée de toutes les opérations de débit et crédit des clients constituée comme suit :
(OPC_ID INT IDENTITY PRIMARY KEY,
OPC_REFCODE CHAR(8) NOT NULL UNIQUE,
CLI_ID INT NOT NULL, -- REFERENCES T_CLIENT_CLI (CLI_ID)
OPC_DATE DATE NOT NULL,
OPC_MONTANT DECIMAL(16,2) NOT NULL CHECK(OPC_MONTANT >= 0),
OPC_CREDIT BIT NOT NULL);
Toutes les colonnes de la table ne sont pas NULLable :
* OPC_ID est la clef primaire de la table.
* OPC_REFCODE une clé alternative (contrainte UNIQUE) sémantique portant la référence de la pièce comptable.
* CLI_ID est une clef étrangère pointant sur la table des clients (non modélisée).
* OPC_DATE est la date d’opération.
* OPC_MONTANT est le montant de l’opération, montant toujours positif (notez la contrainte CHECK).
* OPC_CREDIT est un booléen indiquant s’il s’agit d’un crédit (valeur 1) ou d’un débit (valeur 0).
Si votre SGBDR ne supporte pas IDENTITY (norme SQL) utilisez le mécansime d’incrément propre à votre système (par exemple SERIAL pour PostGreSQL et dans ce cas supprimez le INT, car PostGreSQL ne permet pas de choisir le type de données affecté à un auto incrément). De même si votre SGBDR ne supporte pas le type BIT, remplacez_le par un type booléen adapté à votre SGBDR (par exemple pour PostGreSQL BOOLEAN).
Insertion d’un jeu de données :
('AX', 10001, '2017-05-30', 90, 0),
('DZ', 10001, '2017-05-12', 100, 1),
('EF', 10001, '2017-04-10', 50, 0),
('SH', 10001, '2017-03-25', 100, 0),
('MN', 10001, '2017-03-10', 90, 1),
('GL', 10001, '2017-02-20', 80, 1),
('AZ', 10002, '2017-05-30', 75, 1),
('DG', 10002, '2017-05-10', 100, 0),
('ES', 10002, '2017-04-30', 100, 1),
('SV', 10002, '2017-03-30', 100, 1),
('MH', 10002, '2017-03-15', 50, 0),
('GW', 10002, '2017-02-20', 100, 0);
Deux clients (10001 et 10002) ont fait différentes opérations d’achat et paiement et nous devons effectuer les rapprochement entre ces différentes opérations pour en connaître le solde.
Le résultat doit être le suivant :
----------- ---------- -------- ------------- ---------- --------- -------------- ---------
10001 2017-03-25 SH 100.00 2017-02-20 GL 80.00 -20.00
10001 2017-03-25 SH 100.00 2017-03-10 MN 90.00 70.00
10001 2017-04-10 EF 50.00 2017-03-10 MN 90.00 20.00
10001 2017-05-30 AX 90.00 2017-03-10 MN 90.00 -70.00
10001 2017-05-30 AX 90.00 2017-05-12 DZ 100.00 30.00
10002 2017-02-20 GW 100.00 2017-03-30 SV 100.00 0.00
10002 2017-03-15 MH 50.00 2017-03-30 SV 100.00 -50.00
10002 2017-03-15 MH 50.00 2017-04-30 ES 100.00 50.00
10002 2017-05-10 DG 100.00 2017-04-30 ES 100.00 -50.00
10002 2017-05-10 DG 100.00 2017-05-30 AZ 75.00 25.00
La requête suivante permet de parvenir à ce résultat :
TD AS
(
SELECT CLI_ID, OPC_DATE AS DateDebit,
OPC_MONTANT AS MontantDebit,
OPC_REFCODE AS RefDebit,
SUM(OPC_MONTANT) OVER(PARTITION BY CLI_ID ORDER BY OPC_DATE) AS CumulDebit
FROM T_OPERATION_COMPTABLE_OPC
WHERE OPC_CREDIT = 0
),
TC AS
(
SELECT CLI_ID, OPC_DATE AS DateCredit,
OPC_MONTANT AS MontantCredit,
OPC_REFCODE AS RefCredit,
SUM(OPC_MONTANT) OVER(PARTITION BY CLI_ID ORDER BY OPC_DATE) AS CumulCredit
FROM T_OPERATION_COMPTABLE_OPC
WHERE OPC_CREDIT = 1
)
SELECT TD.CLI_ID, DateDebit, RefDebit, MontantDebit,
DateCredit, RefCredit, MontantCredit,
CumulCredit - CumulDebit AS Solde
FROM TD
INNER JOIN TC
ON TC.CLI_ID = TD.CLI_ID
AND( (TC.CumulCredit > TD.CumulDebit - TD.MontantDebit
AND TC.DateCredit TC.CumulCredit - TC.MontantCredit
AND TC.DateCredit >= TD.DateDebit) )
ORDER BY TD.CLI_ID, TD.DateDebit, TC.DateCredit;
Expert S.G.B.D relationnelles et langage S.Q.L
Moste Valuable Professionnal Microsoft SQL Server
Société SQLspot : modélisation, conseil, formation,
optimisation, audit, tuning, administration SGBDR
Enseignant: CNAM PACA, ISEN Toulon, CESI Aix en Prov.
L’entreprise SQL Spot
Le site web sur le SQL et les SGBDR
Développez et administrez pour la performance avec SQL Server 2014