Discussion :

[jujuetsimon]

Bonjour à tous et merci de prendre un peu de temps pour lire mon post,

Je vais tenter d'expliquer mon problème simplement mais n'hésitez pas à me demander des précisions si je ne suis pas clair...
Tout d'abord je suis en sql server 2008 R2.
J'ai une table dont voici le script de création :

Code :

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

1
2
3
4
5
6
7
8
9
 
CREATE TABLE [dbo].[testRelance](
	[numpiece] [nchar](10) NULL,
	[montant] [numeric](18, 0) NULL,
	[solde] [numeric](18, 0) NULL,
	[date] [date] NULL,
	[client] [nchar](10) NULL,
	[type] [nchar](10) NULL
)

Je la peuple de cette manière :
numpiece montant solde date client type
f1 300 NULL 2015-01-01 1234 FAC
f2 300 NULL 2016-01-01 1234 FAC
f3 300 NULL 2017-01-01 1234 FAC
r1 -200 NULL 2017-02-02 1234 OD
r2 -200 NULL 2017-03-03 1234 OD
Il faut que je renseigne le solde de chaque ligne. FAC représente une facture et OD représente un règlement de facture.
Pour la 1ère ligne, le solde de la facture est 300, 300 aussi pour f2 et f3.
Quand vient le 1er règlement de 200, celui-ci règle en partie la 1ère facture éditée à savoir f1. Le solde de f1 passe à 100 et le solde de r1 passe à 0.
Quand vient le 2ème règlement de 200, celui-ci règle ce qui reste sur la facture f1 (solde de f1 passe à 0) et une partie de la facture f2 (solde f2 passe à 100). Le solde de R1 passe à 0.

Je parviens à faire un solde progressif sur chaque ligne mais ça ne correspond pas à ce que je veux. J'ai pensé également à faire des boucles imbriquées mais je ne vois pas trop la structure de la requête.
Je ne suis même pas sûr que ce soit possible en sql...

Si vous avez des idées, je suis preneur !!!

Merci d'avance,

Julien

[escartefigue]

Bonjour,

Pour pouvoir rapprocher le règlement de la facture, il faut un identifiant commun, or ce n'est pas le cas...

[jujuetsimon]

Bonjour Escartefigue et merci de prendre le temps de me répondre,

Non effectivement un règlement ne correspond pas forcément à une facture. Comme dans l'exemple que j'ai donné, un règlement peut régler plusieurs factures.

Julien

[escartefigue]

En ce cas, quand je reçois 200€, comment savoir si ça concerne la facture F1 ou F2, ça ne va pas !

Au cas où, voici une solution possible, sous réserve que chaque règlement fasse référence à une facture (la 1ère colonne contient le n° de facture, même quand c'est un règlement)

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
WITH CTE1 (NUMFAC, MTTFAC, DTEFAC, TYPFAC) AS   
    (SELECT 'F1', 300, '2015-01-01', 'F'        
     UNION ALL                                  
     SELECT 'F2', 300, '2016-01-01', 'F'        
     UNION ALL                                  
     SELECT 'F2', -15, '2016-01-20', 'R'        
     UNION ALL                                  
     SELECT 'F3', 300, '2017-01-01', 'F'        
     UNION ALL                                  
     SELECT 'F1',-200, '2017-02-02', 'R'        
     UNION ALL                                  
     SELECT 'F2',-200, '2017-02-02', 'R'        
     UNION ALL                                  
     SELECT 'F1',-050, '2017-03-10', 'R'       
   )                                          
 
  SELECT C1.NUMFAC                            
       , C1.MTTFAC                            
       , C1.DTEFAC                            
       , SUM(C1.MTTFAC)                         
         OVER(PARTITION BY C1.NUMFAC          
              ORDER BY     C1.DTEFAC ASC      
              ROWS BETWEEN UNBOUNDED PRECEDING
                       AND CURRENT ROW) as SLDE              
  FROM CTE1 C1                                
  ORDER BY C1.NUMFAC, C1.DTEFAC

Résultat :

Code :

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

1
2
3
4
5
6
7
8
9
 
NUMFAC      MTTFAC DTEFAC              SLDE
F1             300 2015-01-01         300
F1            -200 2017-02-02         100
F1             -50 2017-03-10          50
F2             300 2016-01-01         300
F2             -15 2016-01-20         285
F2            -200 2017-02-02          85
F3             300 2017-01-01         300

EDIT : vous pouvez utiliser la même méthode que ci-dessus, mais en rapprochant non pas sur le n° de facture, mais sur le n° de client (colonne que je n'avais pas mise dans mon jeu d'essai), si vraiment vous n'avez pas de solution pour avoir le n° de facture dans votre règlement.

[jujuetsimon]

En fait, le principe c'est qu'un client ne paie pas forcément tout de suite des factures (souvent des clients en difficulté financière). Du coup, on lui a envoyé 3 factures (dans mon exemple) et il paie par petits morceaux (200 dans mon exemple).
On considère arbitrairement que les factures sont réglées de la plus ancienne à la plus récente pour un client.
Le résultat attendu dans mon exemple est :
numpiece montant solde date client type
f1 300 0 01/01/2015 1234 FAC
f2 300 100 01/01/2016 1234 FAC
f3 300 300 01/01/2017 1234 FAC
r1 -200 0 02/02/2017 1234 OD
r2 -200 0 03/03/2017 1234 OD

Je vais regarder votre requête qui me donne une base de requête à laquelle je n'avais pas pensé.

Merci !

Julien

[escartefigue]

Du coup avec un jeu de données un peu plus riche, qui inclut plusieurs factures par client

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
 
WITH CTE1 (NUMFAC, NUMCLI, MTTFAC, DTEFAC, TYPFAC) AS        
    (SELECT 'F1', 111, 300, '2015-01-01', 'F'                
     UNION ALL                                               
     SELECT 'F2', 222, 300, '2016-01-01', 'F'                
     UNION ALL                                               
     SELECT 'F2', 222, -15, '2016-01-20', 'R'                
     UNION ALL                                               
     SELECT 'F3', 333, 300, '2017-01-01', 'F'                
     UNION ALL                                               
     SELECT 'F1', 111, -200, '2017-02-02', 'R'               
     UNION ALL                                               
     SELECT 'F2', 222, -200, '2017-02-02', 'R'               
     UNION ALL                                               
     SELECT 'F1', 111, -050, '2017-03-10', 'R'               
     UNION ALL                                               
     SELECT 'F4', 111, -044, '2017-03-10', 'R'               
     UNION ALL                                               
     SELECT 'F6', 222, -111, '2017-05-02', 'R'               
   )

Avec la requête suivante :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
  SELECT C1.NUMCLI                                           
       , C1.NUMFAC                                           
       , C1.MTTFAC                                           
       , C1.DTEFAC                                           
       , SUM(C1.MTTFAC)                                      
         OVER(PARTITION BY C1.NUMCLI                         
              ORDER BY     C1.DTEFAC ASC                     
              ROWS BETWEEN UNBOUNDED PRECEDING               
              AND CURRENT ROW) AS SLDE                       
  FROM CTE1 C1                                               
  ORDER BY C1.NUMCLI, C1.DTEFAC                              
 ;

Vous obtenez ce résultat :

Code :

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

1
2
3
4
5
6
7
8
9
10
NUMCLI NUMFAC      MTTFAC DTEFAC            SLDE 
   111 F1             300 2015-01-01         300 
   111 F1            -200 2017-02-02         100 
   111 F1             -50 2017-03-10          50 
   111 F4             -44 2017-03-10           6 
   222 F2             300 2016-01-01         300 
   222 F2             -15 2016-01-20         285 
   222 F2            -200 2017-02-02          85 
   222 F6            -111 2017-05-02         -26 
   333 F3             300 2017-01-01         300

Sympa le client 222 : il rembourse plus que nécessaire , mais c'est conforme au jeu de données

[jujuetsimon]

Merci Escartefigue d'avoir passé du temps pour me répondre !

Effectivement sympa le client 222 mais ça existe vraiment, simplement on fait un avoir ensuite
Je retravaille sur la requête et je clos le post.

Bonne journée,
Julien

[jujuetsimon]

Bonjour,
J'ai pu me remettre un peu sur cette saleté de requête et j'ai sorti la requête ci-dessous qui fonctionne à peu près (j'ai fini de corriger sur ma vraie requête...)

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
-- solde progressif fact
with reqSoldeProgFact as (
select t1.date, t1.montant, t1.client, SUM(t2.montant) as soldeProgrFact
from testRelance t1 left join testRelance t2 on t1.date >= t2.date and t1.type = 'FAC' and t1.client = t2.client
where t1.type = 'FAC' and t2.type = 'FAC'
group by t1.date, t1.montant, t1.client
),
-- solde progressif regl
reqSoldeProgRegl as (
select t1.date, t1.montant, t1.client, SUM(t2.montant) as soldeProgrRegl
from testRelance t1 left join testRelance t2 on t1.date >= t2.date and t1.type = 'OD' and t1.client = t2.client
where t1.type = 'OD' and t2.type = 'OD'
group by t1.date, t1.montant, t1.client
),
reqSumRegl as (
select t1.date, t1.montant, t1.client, SUM(t2.montant) as soldeRegl
from testRelance t1 left join testRelance t2 on t1.date >= t2.date and t1.type = 'OD' and t1.client = t2.client
--where t1.type = 'OD'
group by t1.date, t1.montant, t1.client
),
reqSumFactClient as (
select client, coalesce (SUM(montant),0) as soldeFact
from testRelance 
where type = 'FAC'
group by  client
),
reqSumReglClient as (
select client, coalesce (SUM(montant),0) as soldeRegl
from testRelance 
where type = 'OD'
group by  client
)
select 
t3.client,t3.numpiece, t3.montant, t3.date,t3.type,
reqSoldeProgFact.soldeProgrFact,
reqSoldeProgregl.soldeProgrRegl,
abs(coalesce(reqSumFactClient.soldeFact,0)) as SommeFact,
--reqSumRegl.*,
abs(coalesce(reqSumReglClient.soldeRegl,0)) as SommeRegl,
case
	when t3.type = 'FAC' then
		case	when abs(coalesce(reqSumReglClient.soldeRegl,0)) >=  soldeProgrFact then 0
				when (abs(coalesce(reqSumReglClient.soldeRegl,0)) <  soldeProgrFact) and (soldeProgrFact-abs(coalesce(reqSumReglClient.soldeRegl,0)) <= t3.montant) then soldeProgrFact-abs(coalesce(reqSumReglClient.soldeRegl,0))
		else t3.montant
	end
	when t3.type = 'OD' then
		case	when coalesce(reqSumFactClient.soldeFact,0) >=  coalesce(abs(soldeProgrRegl),0) then 0
				when (coalesce(reqSumFactClient.soldeFact,0) <  abs(soldeProgrRegl)) and (abs(soldeProgrRegl)-coalesce(reqSumFactClient.soldeFact,0) <= t3.montant) then abs(soldeProgrRegl)-coalesce(reqSumFactClient.soldeFact,0)
		else t3.montant
	end
end as Solde

Merci escartefigue du temps passé à me répondre.
Bonne journée,
JujuEtSimon