Sous requête complexe SQL

**findjo** · 09/09/2016, 00h12

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 :

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

**François DORIN** · 09/09/2016, 08h40

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.

**escartefigue** · 09/09/2016, 09h08

Quel est votre SGBD ?

**findjo** · 09/09/2016, 10h25

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.

**aieeeuuuuu** · 09/09/2016, 11h00

Bonjour,

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

**François DORIN** · 09/09/2016, 11h12

Voici une requête :

Code :

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

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.

**escartefigue** · 09/09/2016, 14h55

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 :

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

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

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 :

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

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

Sous requête complexe SQL

Langage SQL

Discussions similaires

Partager

Partager