Sous requête complexe SQL

Bonjour à tous,

J'ai un soucis pour résoudre un problème qui se pose dans les données de l'application bancaire sur la quelle je travaille.

Je vous explique le principe :

Nous proposons des services d'assurances que voici :

:Assurance Obsèques, Assurance Obsèque +, Soutra famille semestriel, Soutra famille annuel, Adhesion soutra famille

Et ces différents services d'assurance sont dans la table suivante :

CodeService	Montant	Description
645	5000	Assurance Obsèque
647	10000	Assurance Obsèque+
681	6000	Soutra famille semestriel
682	12000	Soutra famille annuel
683	1000	Adhesion soutra famille

Je prends des exemples de clients qui sont dans la table suivantes :

CodeClt	Nom	Prenoms
0001	Dupond	Yannick
0002	Yacinthe	Albert
0003	Le Boeuf	François
0004	Kapersky	Annick

les clients ont un compte qui se trouve dans une table que voici :

NumCompte	Date Creation	CodeClt	TypeCompte	Solde
11111	01/01/2013	0001	DP	6000
11112	01/05/2013	0003	DP	6000
11113	01/10/2013	0004	DP	6000

Et lorsque le client souscrit à un de ces services, ces souscriptions s'enregistrent dans la table des transactions

ID	Date	NumCompte	Montant	CodeService
1	01/02/2013	11111	5000	645
2	02/06/2013	11112	5000	645
3	01/12/13	11113	5000	645
4	01/02/2014	11111	2000	645
5	01/03/2014	11111	3000	645
6	02/06/2014	11112	4000	645
7	02/07/2014	11112	1000	645
8	01/12/14	11113	5000	645

Donc comme vous pouvez le constater, l'adhésion au service 645 est coute au client 5000 et le renouvellement se fait aussi toujours le jour d'anniversaire de la l'adhésion, mais le prélèvement du montant (5000) doit se faire automatiquement tant qu'il ya de l'argent sur le compte du client.

Or le système bancaire sur lequel nous travaillons ne prélève pas forcement les 5000 sur le compte de l'utilisateur si le solde de son compte n'atteint pas les 5000.

Exemple :

Le 18 /01/2015, Monsieur Yao AMANI décide de souscrit au service Soutra (645) et fait un dépôt initial de 100000 Francs sur son compte de dépôt.
Le 18/01/2016 Monsieur Yao AMANI a un montant de 3000 Francs sur son compte et le prélèvement automatique doit se fait pour le service au quel il a souscrit.
Alors le logiciel bancaire dans ce cas ne prélève pas automatiquement les 5000 mais prélève les 3000 francs.
Le 01/02/2016 Monsieur Yao fait un dépôt de 50000 Francs sur son compte. Alors le logiciel vient prélever les 2000 francs restant.

Le problème est qu'on me demande de faire une requête qui va le nombre de client qui on fait un renouvellement.
Or lorsque le logiciel prélève les 5000 lors du renouvellement il y a pas de problème mais lorsqu'il prélève soit 3000 et ensuite 2000 comme mentionné plus haut, là je ne sais plus comment procéder vu qu'il y aura 2 enregistrements passé à des dates de mois différents.


Voici le code que j'ai eu a écris :

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 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48

select br.short_name 'Agence' , da.rim_no 'Rim' ,rm.first_name +' '+rm.last_name 'Nom et Prénom(s) client ' , dh.acct_no 'Numero compte' , convert (char (10),rm.birth_dt, 103) 'Date de Naissance' , convert (char (10),dh.create_dt, 103) 'Date adhésion' , '500 000' 'Montant Capital' , dh.amt 'Montant Prime annuelle' /*, dh.description 'Champ libellé' ,case   when dh.reversal=0 then 'Non'   when dh.reversal=1 then 'Oui' end ' Contrepassé'*/ from dp_history dh, dp_acct da, ad_gb_branch br, rm_acct rm where dh.acct_no=da.acct_no and dh.orig_branch_no=br.branch_no and rm.rim_no=da.rim_no and dh.tran_code in (645) and dh.reversal=0 and dh.amt>0 and br.branch_no=140 /*<:and:dh.create_dt:1> <:and:br.branch_no:2> <:and:da.rim_no:3> <:and:dh.acct_no:4> <:and:dh.tran_code:5>*/ --and year(dh.create_dt)=2016 and dh.create_dt<'20160801' and da.rim_no in (select da.rim_no from dp_history dh, dp_acct da, ad_gb_branch br, rm_acct rm where dh.acct_no=da.acct_no and dh.orig_branch_no=br.branch_no and rm.rim_no=da.rim_no and dh.tran_code in (645) and dh.reversal=0 and dh.amt>0 and br.branch_no=140 /*<:and:dh.create_dt:1>   <:and:br.branch_no:2>   <:and:da.rim_no:3>   <:and:dh.acct_no:4>   <:and:dh.tran_code:5>*/ --and year(dh.create_dt)=2014 and dh.create_dt between '20160801' and '20160831' --and sum(dh.amt)=5000 )   order by br.short_name, rm.first_name +' '+rm.last_name

---

NB: les noms des colonnes de la requête sont celles de la vraie base et pour être plus compréhensible, j'ai utilisé un schéma d'exemple plus haut



Comment dois-je procéder?

Bonjour,

A quel moment doit-on considérer qu'un renouvellement est effectif ?

Par exemple, pour reprendre le cas du compte 11112 qui a un premier prélèvement de 4000 le 02/06/2014 et un autre de 1000 le 02/07/2014 :

• Est-ce au paiement de la dernière partie (et dans ce cas là, le renouvellement est actif à la date du dernier paiement et devient la nouvelle référence pour le renouvellement l'année prochaine. Ainsi, le compte n'est supposé renouvelé qu'à partir du 02/07/2014 et le prochain renouvellement aura lieu le 02/07/2015 ;
• Est-ce au paiement de la dernière partie, mais la date de référence reste la date de prélèvement de la première partie. Ainsi, le copte n'est supposé renouvelé qu'à partir du 02/07/2014 et le prochain renouvellement aura lieu de le 02/06/2015 ;
• Est-ce au paiement de la première partie, et on considère le renouvellement (complet ou au prorata temporis) ? Et le considère-t-on si le paiement n'est pas encore terminé ?

Quoiqu'il en soit, une approche possible serait de faire un requête qui calcul les sommes effectivement versées. Par exemple, si la date de renouvellement à considérer est la date de la première partie, alors tu peux faire une requête qui calcule le montant versé pour ce renouvellement précis, en faisant la somme des montants versés depuis cette date D et jusqu'à une date D + 1 an. Si le montant vaut 5000, le renouvellement est effectif.

Quel est votre SGBD ?

Le SGBD est Sybase

En ce qui concerne les 3 points énumérés par dorinf, c'est le deuxième points qui doit être retenu.

Bonjour,

Quel résultat attendez vous à partir des données que vous nous avez fournies ?

Voici une requête :

Code:

---

1 2 3 4 5 6

SELECT T.NumCompte, T.CodeService, T.Date AS DebutRenouvellement, SUM(U.Montant) MontantPaye, MAX(U.Date) AS DateDerniereTransaction FROM (SELECT T.Date, T.NumCompte, T.CodeService FROM trans AS T INNER JOIN (SELECT MIN(Date) AS Date, NumCompte FROM trans GROUP BY NumCompte, CodeService) AS FirstTransaction ON DATEPART(MONTH, FirstTransaction.Date) = DATEPART(MONTH, T.Date) AND DATEPART(DAY, FirstTransaction.Date) = DATEPART(DAY, T.Date) ) AS T INNER JOIN trans AS U ON T.NumCompte = U.NumCompte AND U.Date BETWEEN T.Date AND DATEADD(DAY, -1, DATEADD(YEAR, 1, T.Date)) GROUP BY T.NumCompte, T.CodeService, T.Date

---

Elle renvoie une table contenant tous les renouvelles avec les colonnes suivantes :

• Le numero de compte ;
• Le service ;
• Date de début du renouvellement (première transaction) ;
• Date de fin du renouvellement (dernière transaction) ;
• Montant payé (somme des montants payés entre la première et la dernière transaction)

Exemple de résultat avec le jeu que tu as fourni :

NumCompte	CodeService	DebutRenouvellement	MontantPaye	DateDerniereTransaction
11111	        645             01/02/2013              5000            01/02/2013
11111        	645             01/02/2014              5000            01/03/2014
11113	        645             01/12/2013              5000            01/12/2013
11113 	        645             01/12/2014              5000            01/12/2014
11112	        645             02/06/2013              5000            02/06/2013
11112	        645             02/06/2014              5000            02/07/2014

Cela devrait donc faire une bonne base pour pouvoir obtenir ce que tu souhaites.

Comment la requête fonctionne ?
Une partie détermine la toute première transaction dans le temps d'un service pour chaque compte. Cela permet d'obtenir la date initial d'achat et donc de connaitre la date de renouvellement.
Ensuite, via une jointure, elle calcule l'ensemble des renouvellements, en se basant sur la date des transactions (même jour, même mois)
Après, via une jointure et un group by, je somme les montants pour tous les renouvellements. Et j'affiche aussi la date de la dernière transaction.

Voici une solution possible pour afficher les écarts entre les montants dus et les montants payés, par client, contrat, service et période

Attention : je n'ai pas de SYBASE sous la main et n'en ai plus fait depuis longtemps, les fonctions de manipulation de date sont probablement à adapter car différentes selons les SGBD

A noter : il manque l'information de fin de contrat dans la base de données, en l'état, le contrat est souscrit à vie...

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 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56

-- Etape 1 : par client, compte et service, recherche des périodes et montants attendus jusqu'à fin 2016 WITH CTE1 (C1SEQN, C1CLIE, C1CPTE, C1SERV, C1DDEB, C1DFIN, C1MONT) AS( SELECT 0 , TBCLIE , TCCPTE , TDSERV , MIN(TDDATE) AS DTDEB , ADD_MONTHS(MIN(TDDATE), 12) - 1 DAYS AS DTFIN , MIN(TAMONT) AS COTIS FROM TCLIEN INNER JOIN TCOMPT ON TCOMPT.TCCLIE=TCLIEN.TBCLIE AND TCOMPT.TCTYPE='DP' INNER JOIN TVERSE ON TVERSE.TDCPTE=TCOMPT.TCCPTE INNER JOIN TSERV ON TSERV.TASERV=TVERSE.TDSERV GROUP BY TBCLIE, TCCPTE, TDSERV UNION ALL SELECT C1SEQN + 1 , C1CLIE , C1CPTE , C1SERV , ADD_MONTHS(C1DDEB, 12) , ADD_MONTHS(C1DFIN, 12) , C1MONT FROM CTE1 WHERE C1SEQN < 050 AND C1DDEB < '2017-01-01' )   -- Etape 2 : comparaison avec les montants payés SELECT C1CLIE AS CLIENT , SUBSTR(TBNOMP, 01, 10) AS PRN10 , SUBSTR(TBPRNP, 01, 10) AS NOM10 , C1CPTE AS COMPTE , C1SERV AS SERV , C1DDEB AS DEBUT , C1MONT AS MT_DU , SUM(TDMONT) AS MT_PAYE FROM CTE1 INNER JOIN TCLIEN ON TCLIEN.TBCLIE = CTE1.C1CLIE INNER JOIN TVERSE ON TVERSE.TDCPTE = CTE1.C1CPTE AND TVERSE.TDSERV = CTE1.C1SERV AND TVERSE.TDDATE BETWEEN C1DDEB AND C1DFIN GROUP BY C1CLIE , TBNOMP , TBPRNP , C1CPTE , C1SERV , C1DDEB , C1MONT ORDER BY C1CLIE, C1CPTE, C1SERV, C1DDEB

---

En reprenant votre jeu d'essai enrichi dans la table des souscriptions comme suit (orange=modif, vert=ajout) :

Code:

---

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

TDIDEN TDDATE TDCPTE TDMONT TDSERV 1 2013-02-01 11111 5000.00 645 2 2013-06-02 11112 5000.00 645 3 2013-12-01 11113 5000.00 645 4 2014-02-01 11111 2000.00 645 5 2014-03-01 11111 3000.00 645 6 2014-06-02 11112 3500.00 645 7 2014-07-02 11112 1000.00 645 8 2014-12-01 11113 5000.00 645 9 2015-01-01 11112 2000.00 681 10 2015-02-01 11112 2000.00 681 11 2015-03-01 11112 2000.00 681 12 2016-01-01 11112 5000.00 681

---

On obtient :

Code:

---

1 2 3 4 5 6 7 8 9

CLIENT PRN10 NOM10 COMPTE SERV DEBUT MT_DU MT_PAYE 00001 Dupond Yannick 11111 645 2013-02-01 5000 5000.00 00001 Dupond Yannick 11111 645 2014-02-01 5000 5000.00 00003 Le Boeuf François 11112 645 2013-06-02 5000 5000.00 00003 Le Boeuf François 11112 645 2014-06-02 5000 4500.00 00003 Le Boeuf François 11112 681 2015-01-01 6000 6000.00 00003 Le Boeuf François 11112 681 2016-01-01 6000 5000.00 00004 Kapersky Annick 11113 645 2013-12-01 5000 5000.00 00004 Kapersky Annick 11113 645 2014-12-01 5000 5000.00

---