Pb SQL Ensembliste (sans Curseur)

Version imprimable

Bonjour à tous,
J'ai ce pb que je n'arrive pas à résoudre en SQL Ensembliste. j'étais obligé de le faire avec le Curseur.
J'ai un bénéficiaire qui a 2 types de commandes:
Commande crédit : on crédite son compte.
Commande Débit : on débite son compte.
Toutes les commandes sont ordonnées par ordre décroissant par rapport à la date de la commande.
Il s'agit de trouver quelles sont les commandes Crédit auxquelles sont associées les commandes Débit en commençant par la commande Débit la plus ancienne.

CodeBenef DateCommande MontantCredit MontantDebit ReferenceCommande

10001 30/05/2017 0 90 AX

10001 12/05/2017 100 0 DZ

10001 10/04/2017 0 50 EF

10001 25/03/2017 0 100 SH

10001 10/03/2017 90 0 MN

10001 20/02/2017 80 0 GL

Le résultat final doit être:

CodeBenef RefCommandeDebit RefCommandeCredit

10001 SH GL

10001 SH MN

10001 EF MN

10001 AX MN

10001 AX DZ

SH = GL (80 euros) + 20 (euros de MN) ---- > SH est liée à GL et MN
EF = 50 euros ---- > on Prends 50 des 70 euros qui restent de MN --> EF est liée à MN
AX = 90 euros -- > on prends les 20 qui restent de MN et 70 euros de DZ ---> AX est liée à MN et DZ.
Résolu avec un curseur, je voudrais pouvoir le faire sans le curseur mais en ensembliste.
Merci d'avance. si vous avez des idées pour aborder ce genre de pb. Vu le pb ça ressemble à de la récursivité. la ligne suivante dépend de la précédente.

Bonjour,

Voici une piste (tous les cas ne sont pas pris en compte)

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
 
tmp AS (
	SELECT	*
			, SUM(MontantCredit) OVER(ORDER BY DateCommande) as SCredit
			, SUM(MontantDebit)	OVER(ORDER BY DateCommande) as SDebit
	FROM latable
)
SELECT Deb.ReferenceCommande as ReferenceDebit, Cred.ReferenceCommande AS ReferenceCredit
FROM tmp Deb
	LEFT JOIN tmp Cred
		ON		Cred.montantCredit > 0
		AND		Cred.SCredit > Deb.SDebit - Deb.MontantDebit 
		AND		Cred.DateCommande < Deb.DateCommande
WHERE Deb.MontantDebit > 0

28/06/2017, 10h12
solitude

Merci pour votre idée.
J'ai testé des bouts de code qui ressemble un peu sauf que le problème pour les lignes suivantes ça ne fonctionnait pas.
Je vais tester votre idée.
Merci.

Je suis arrivé à la même chose que toi...

Avec la table :

Code:

1
2
3
4
5
CREATE TABLE X (CodeBenef INT, 	
                DateCommande DATE,
                MontantCredit  DECIMAL(16,2),	
                MontantDebit  DECIMAL(16,2), 	
                ReferenceCommande VARCHAR(16))

Les données :

Code:

1
2
3
4
5
6
7
INSERT INTO X VALUES 
(10001,  '30/05/2017', 	0,     90,   'AX'),
(10001,  '12/05/2017' ,	100,   0,    'DZ'),
(10001,  '10/04/2017' ,	0,     50,   'EF'),
(10001,  '25/03/2017' ,	0,     100,  'SH'),
(10001,  '10/03/2017' ,	90,    0,    'MN'),
(10001,  '20/02/2017' ,	80,    0,    'GL');

La requête :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
WITH
TD AS 
(
SELECT CodeBenef, DateCommande, MontantDebit, ReferenceCommande,
       SUM(MontantDebit) OVER(PARTITION BY CodeBenef ORDER BY DateCommande) AS CumulDebit
FROM X WHERE MontantCredit = 0
),
TC AS 
(
SELECT CodeBenef, DateCommande AS DatePaiement, MontantCredit, ReferenceCommande,
       SUM(MontantCredit) OVER(PARTITION BY CodeBenef ORDER BY DateCommande) AS CumulCredit
FROM X WHERE MontantDebit = 0
)
SELECT *, CumulCredit - CumulDebit AS SOLDE
FROM   TD 
       LEFT OUTER JOIN TC
            ON  TC.CodeBenef = TD.CodeBenef
                AND TC.montantCredit > 0
		AND TC.CumulCredit > TD.CumulDebit - TD.MontantDebit 
		AND TC.DatePaiement < TD.DateCommande
                AND TD.MontantDebit > 0
ORDER BY TD.DateCommande, TC.DatePaiement;

Le résultat :

Code:

1
2
3
4
5
6
7
CodeBenef   DateCommande MontantDebit    ReferenceCommande CumulDebit   CodeBenef   DatePaiement MontantCredit    ReferenceCommande CumulCredit    SOLDE
----------- ------------ --------------- ----------------- ------------ ----------- ------------ ---------------- ----------------- -------------- ---------
10001       2017-03-25   100.00          SH                100.00       10001       2017-02-20   80.00            GL                80.00          -20.00
10001       2017-03-25   100.00          SH                100.00       10001       2017-03-10   90.00            MN                170.00         70.00
10001       2017-04-10   50.00           EF                150.00       10001       2017-03-10   90.00            MN                170.00         20.00
10001       2017-05-30   90.00           AX                240.00       10001       2017-03-10   90.00            MN                170.00         -70.00
10001       2017-05-30   90.00           AX                240.00       10001       2017-05-12   100.00           DZ                270.00         30.00

Tu as juste oublié la jointure sur le bénéficiaire

A +

Au final avec un jeu de données plus complet, on peut simplifier avec :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
WITH
TD AS 
(
SELECT CodeBenef, DateCommande AS DateDebit, MontantDebit, ReferenceCommande,
       SUM(MontantDebit) OVER(PARTITION BY CodeBenef ORDER BY DateCommande) AS CumulDebit
FROM X WHERE MontantCredit = 0
),
TC AS 
(
SELECT CodeBenef, DateCommande AS DateCredit, MontantCredit, ReferenceCommande,
       SUM(MontantCredit) OVER(PARTITION BY CodeBenef ORDER BY DateCommande) AS CumulCredit
FROM X WHERE MontantDebit = 0
)
SELECT *, CumulCredit - CumulDebit AS SOLDE
FROM   TD 
       INNER JOIN TC
		      ON  TC.CodeBenef = TD.CodeBenef
		      AND(    (TC.CumulCredit > TD.CumulDebit  - TD.MontantDebit  AND TC.DateCredit <= TD.DateDebit)
                 OR (TD.CumulDebit  > TC.CumulCredit - TC.MontantCredit AND TC.DateCredit >= TD.DateDebit) )
ORDER BY TD.CodeBenef, TD.DateDebit, TC.DateCredit;

A +

28/06/2017, 14h34
solitude

Franchement Merci beaucoup.
Je n'ai pas encore regardé vu que la procédure est juste en recette.
j'ai encore un petit peu de temps pour la modifier pour la mettre en production sans le curseur.
ICI les DBA n'aiment pas trop les curseurs. mais je comprends.
Merci beaucoup.
28/06/2017, 14h35
solitude

Merci beaucoup pour vous deux.

En plus ça marche même avec plusieurs bénéficiaires ça me permet de ne pas boucler sur les bénéficiaires:

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
        INSERT INTO X VALUES 
        (10001,  '2017-05-30', 	0,     90,   'AX'),
        (10001,  '2017-05-12' ,	100,   0,    'DZ'),
        (10001,  '2017-04-10' ,	0,     50,   'EF'),
        (10001,  '2017-03-25' ,	0,     100,  'SH'),
        (10001,  '2017-03-10' ,	90,    0,    'MN'),
        (10001,  '2017-02-20' ,	80,    0,    'GL'),
 
        (10002,  '2017-03-25' ,	0,     30,   'SS'),
	(10002,  '2017-03-23' ,	30,     0,   'VV'),
        (10002,  '2017-03-10' ,	0,    10,    'MM'),
        (10002,  '2017-02-20' ,	0,    10,    'LL'),
	(10002,  '2017-02-10' ,	0,    40,    'YY'),
        (10002,  '2017-01-20' ,	80,    0,    'RR')

Merci les gars.

CodeBenef	DateCommande	MontantCredit	MontantDebit	ReferenceCommande
10001	30/05/2017	0	90	AX
10001	12/05/2017	100	0	DZ
10001	10/04/2017	0	50	EF
10001	25/03/2017	0	100	SH
10001	10/03/2017	90	0	MN
10001	20/02/2017	80	0	GL

CodeBenef	RefCommandeDebit	RefCommandeCredit
10001	SH	GL
10001	SH	MN
10001	EF	MN
10001	AX	MN
10001	AX	DZ