Discussion :

[ededainville]

Bonjour,

j'ai besoin de générer un prévisionnel hebdomadaire de facturation sur la base de mes contrats restant à facturer. La facturation se fait le dernier jour de chaque mois, puis le dernier jour du contrat si celui-ci n'est pas en fin de mois.
> numéro de contrat dticket
> Date de début de contrat start_rent
> Date de fin de contrat stop_rent
> restant à facturer : uninvoiced_net


Exemple: un Contrat de 1000€ allant du 25/09 au 15/11, soit 36 jours ouvrés.
La première facture sera faite au 30/09, soit 4 jours ouvrés et 4/36 de 1000€ soit 111,11€
La deuxième facture sera faite au 31/10, soit 22 jours ouvrés et 22/36 de 1000€ soit 611,11€
La dernière facture sera faite au 15/11, soit 10 jours ouvrés et 10/36 de 1000€ soit 277,78€

Est-il possible de faire cela en lignes ? Donc ici 3 lignes. Mais cela induirait que le nombre de factures potentielles soit une variable or je ne vois pas comment traduire cela dans les jointures.

Merci d'avance pour votre aide !

[Waldar]

L'idéal serait d'avoir une table calendrier où vous stockez vos jours ouvrés pour affiner avec les jours fériés.
Ici j'ai simplement conservé l'idée jour de la semaine = ouvré.

Données

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
create table MaTable
( dticket           integer
, start_rent        date
, stop_rent         date
, uninvoiced_net    decimal(7,2)
)
GO
 
insert into MaTable values
(1, '2020-09-25', '2020-11-15', 1000.00),
(2, '2020-09-25', '2020-09-27',   50.00)
GO

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
with cte_recur (dticket, day_rent, stop_rent, uninvoiced_net) as
(
select dticket
     , start_rent
     , stop_rent
     , uninvoiced_net
  from MaTable
 union all
select dticket
     , dateadd(day, 1, day_rent)
     , stop_rent
     , uninvoiced_net
  from cte_recur
 where day_rent < stop_rent
)
  select dticket
       , max(day_rent) as jour_facture
       , sum(case when datename(weekday, day_rent) in ('Saturday', 'Sunday') then 0 else 1 end) as ouvre
       , sum(case when datename(weekday, day_rent) in ('Saturday', 'Sunday') then 0.00 else 1.00 end)
       / sum(sum(case when datename(weekday, day_rent) in ('Saturday', 'Sunday') then 0.00 else 1.00 end)) over(partition by dticket) 
       * uninvoiced_net as montant_facture
    from cte_recur
group by dticket
       , datepart(month, day_rent)
       , uninvoiced_net
order by 1, 2
 
| dticket | jour_facture | ouvre | montant_facture |
|---------|--------------|-------|-----------------|
|       1 |   2020-09-30 |     4 |         111.111 |
|       1 |   2020-10-31 |    22 |         611.111 |
|       1 |   2020-11-15 |    10 |         277.777 |
|       2 |   2020-09-27 |     1 |              50 |

[ededainville]

Merci Waldar, et vive la philanthropie !

Je teste et vous donne le résultat / mes commentaires si j'ai d'autres questions.

Bien à vous,