Cumul à date

Version imprimable

Cumul à date

Bonjour,

ci-dessous une table d'historique de montant de facturation jour par jour pour les affaires de ma société :
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 ANNEE MOIS JOUR AGENCE AFFAIRE MONTANT 2010 1 1 LYON 1 10 2010 1 1 LYON 1 20 2010 1 1 LYON 1 30 2010 1 2 LYON 2 10 2010 1 2 LYON 2 10 2010 1 2 LYON 2 10 2010 1 4 LYON 1 50 2010 1 4 LYON 1 60 2010 1 4 LYON 1 100 2010 1 4 PARIS 2 10 2010 1 4 PARIS 2 10 2010 1 4 PARIS 2 10 2010 1 5 PARIS 3 1000 2010 2 10 PARIS 1 20
Je souhaite faire une requête sur cette table qui m'affiche pour un jour donné la liste de toutes les affaires de l'année, le cumul du montant depuis le début de l'année, du mois, le montant du jour (0 si rien pour le jour) et la dernière agence impactée.

Résultat attendu pour le 04/01/2010 :
Code:

1 2 3 4 ANNEE MOIS JOUR AGENCE AFFAIRE ANNUEL MOIS JOUR 2010 1 4 LYON 1 270 270 210 2010 1 4 PARIS 2 60 60 30
Résultat attendu pour le 10/02/2010 :
Code:

1 2 3 4 5 ANNEE MOIS JOUR AGENCE AFFAIRE ANNUEL MOIS JOUR 2010 2 10 PARIS 1 290 20 20 2010 2 10 PARIS 2 60 0 0 2010 2 10 PARIS 3 1000 0 0
Résultat attendu pour le 10/03/2010 :
Code:

1 2 3 4 5 ANNEE MOIS JOUR AGENCE AFFAIRE ANNUEL MOIS JOUR 2010 3 10 PARIS 1 290 0 0 2010 3 10 PARIS 2 60 0 0 2010 3 10 PARIS 3 1000 0 0
La difficulté est de :

1. Avoir la liste de toutes les affaires depuis le début de l'année
2. Obtenir pour chaque affaire leur dernière imputation connue
3. Faire le cumul à date depuis le début de l'année, du mois et la valeur du jour (0 si vide)

Merci pour votre aide.

18/06/2010, 16h23
Oishiiii

Bonjour,

Je n'arrive pas à retrouver le résultat attendu pour les colonnes Annuel, Mois et Jour à partir des données que vous nous présentez.
Pour le 04/01/2010 comment trouvez-vous 270 pour la colonne annuel.

Quel votre SGBD ?
Essayez de nous fournir directement la structure de la table au format SQL (CREATE TABLE..) ainsi qu'un jeu d'essai (INSERT INTO...) ce sera plus facile pour tester.
18/06/2010, 16h29
Mirror69
Pour le calcul du cumul à date annuel du 04/01 ET l'affaire 1 on a :
Code:

1 2 3 4 5 6 7 8 ANNEE MOIS JOUR AGENCE AFFAIRE MONTANT 2010 1 1 LYON 1 10 2010 1 1 LYON 1 20 2010 1 1 LYON 1 30 2010 1 4 LYON 1 50 2010 1 4 LYON 1 60 2010 1 4 LYON 1 100
la montant annuel est donc 10 + 20 + 30 +50 + 60 + 100 = 270

Je vous fais passer un jeu d'essai tout de suite.

Voici le jeu d'essai pour les tests :

Pour info je travaille sur SQl Server 2008 et le but est de faire une vue
avec comme filtre une année, un mois et un jour
(SELECT * FROM VIEW_FACTURATION WHERE ANNEE = 2010 AND MOIS = 2 AND JOUR = 1)

Script de creation de table :

Code:

1
2
3
4
5
6
7
8
9
10
11
 
CREATE TABLE [dbo].[Facturation](
	[ANNEE] [int] NULL,
	[MOIS] [int] NULL,
	[JOUR] [int] NULL,
	[AGENCE] [varchar](50) NULL,
	[AFFAIRE] [varchar](50) NULL,
	[MONTANT] [money] NULL
) ON [PRIMARY]
 
GO

INSERT :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
 
INSERT INTO [AXIGEST_SURF_ODS].[dbo].[Facturation]([ANNEE],[MOIS],[JOUR],[AGENCE],[AFFAIRE] ,[MONTANT]) VALUES (2010,1,1,LYON,1,10.00)
INSERT INTO [AXIGEST_SURF_ODS].[dbo].[Facturation]([ANNEE],[MOIS],[JOUR],[AGENCE],[AFFAIRE] ,[MONTANT]) VALUES (2010,1,1,LYON,1,20.00)
INSERT INTO [AXIGEST_SURF_ODS].[dbo].[Facturation]([ANNEE],[MOIS],[JOUR],[AGENCE],[AFFAIRE] ,[MONTANT]) VALUES (2010,1,1,LYON,1,30.00)
INSERT INTO [AXIGEST_SURF_ODS].[dbo].[Facturation]([ANNEE],[MOIS],[JOUR],[AGENCE],[AFFAIRE] ,[MONTANT]) VALUES (2010,1,2,LYON,2,10.00)
INSERT INTO [AXIGEST_SURF_ODS].[dbo].[Facturation]([ANNEE],[MOIS],[JOUR],[AGENCE],[AFFAIRE] ,[MONTANT]) VALUES (2010,1,2,LYON,2,10.00)
INSERT INTO [AXIGEST_SURF_ODS].[dbo].[Facturation]([ANNEE],[MOIS],[JOUR],[AGENCE],[AFFAIRE] ,[MONTANT]) VALUES (2010,1,2,LYON,2,10.00)
INSERT INTO [AXIGEST_SURF_ODS].[dbo].[Facturation]([ANNEE],[MOIS],[JOUR],[AGENCE],[AFFAIRE] ,[MONTANT]) VALUES (2010,1,4,LYON,1,50.00)
INSERT INTO [AXIGEST_SURF_ODS].[dbo].[Facturation]([ANNEE],[MOIS],[JOUR],[AGENCE],[AFFAIRE] ,[MONTANT]) VALUES (2010,1,4,LYON,1,60.00)
INSERT INTO [AXIGEST_SURF_ODS].[dbo].[Facturation]([ANNEE],[MOIS],[JOUR],[AGENCE],[AFFAIRE] ,[MONTANT]) VALUES (2010,1,4,LYON,1,100.00)
INSERT INTO [AXIGEST_SURF_ODS].[dbo].[Facturation]([ANNEE],[MOIS],[JOUR],[AGENCE],[AFFAIRE] ,[MONTANT]) VALUES (2010,1,4,PARIS,2,10.00)
INSERT INTO [AXIGEST_SURF_ODS].[dbo].[Facturation]([ANNEE],[MOIS],[JOUR],[AGENCE],[AFFAIRE] ,[MONTANT]) VALUES (2010,1,4,PARIS,2,10.00)
INSERT INTO [AXIGEST_SURF_ODS].[dbo].[Facturation]([ANNEE],[MOIS],[JOUR],[AGENCE],[AFFAIRE] ,[MONTANT]) VALUES (2010,1,4,PARIS,2,10.00)
INSERT INTO [AXIGEST_SURF_ODS].[dbo].[Facturation]([ANNEE],[MOIS],[JOUR],[AGENCE],[AFFAIRE] ,[MONTANT]) VALUES (2010,1,5,PARIS,3,1000.00)

J'ai utilisé une CTE:

Création de la vue:

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
 
CREATE VIEW vue_facturation AS 
with MontantAnnuel(annee, agence, affaire, annuel) AS (
  select annee, agence, affaire, SUM(Montant)
  from facturation
  group by annee, agence, affaire
), MontantMensuel(annee, mois, agence, affaire, mensuel) AS (
  select annee, mois, agence, affaire, SUM(Montant)
  from facturation
  group by annee, mois, agence, affaire
), MontantJournalier(annee, mois, jour, agence, affaire, MontantJour) AS (
  select annee, mois, jour, agence, affaire, SUM(Montant)
  from facturation
  group by annee, mois, jour, agence, affaire
)
select 
  distinct f.annee, f.mois, f.jour, f.agence, f.affaire, 
  annuel, mensuel, MontantJour
from facturation AS f
  join MontantAnnuel AS a
    on f.annee = a.annee
    and f.agence = a.agence
    and f.affaire = a.affaire
  join MontantMensuel AS m
    on f.annee = m.annee
    and f.agence = m.agence
    and f.affaire = m.affaire
    and f.mois = m.mois
  join MontantJournalier AS j
    on f.annee = j.annee
    and f.agence = j.agence
    and f.affaire = j.affaire
    and f.mois = j.mois
    and f.jour = j.jour

Selection:

Code:

1
2
3
4
5
select * 
from vue_facturation
where annee = 2010 
  and mois = 1 
  and jour = 4

Le résultat semble correct:

Code:

1
2
3
4
 
annee  mois  jour  agence  affaire  annuel  mensuel  MontantJour
2010    1     4     LYON      1     270,00  270,00     210,00
2010    1     4     PARIS     2      30,00   30,00      30,00

Les pros d'SQL Server feront certainement mieux.

J'ai utilisé les fonctions de fenêtrages, ce sera plus rapide :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
With SR ([ANNEE],[MOIS],[JOUR],[AGENCE],[AFFAIRE],[ANNUEL],[MENSUEL],[QUOTIDIEN], rn) as
(
select [ANNEE],[MOIS],[JOUR],[AGENCE],[AFFAIRE],
       sum([MONTANT]) over(partition by [AFFAIRE], [ANNEE])                ,
       sum([MONTANT]) over(partition by [AFFAIRE], [ANNEE], [MOIS])        ,
       sum([MONTANT]) over(partition by [AFFAIRE], [ANNEE], [MOIS], [JOUR]),
       row_number() over(partition by [AFFAIRE] order by [ANNEE] desc, [MOIS] desc, [JOUR] desc)
  from [dbo].[Facturation]
 where [ANNEE] <= 2010
   and [MOIS]  <= 1
   and [JOUR]  <= 4
)
select [ANNEE],[MOIS],[JOUR],[AGENCE],[AFFAIRE],
       [ANNUEL], [MENSUEL], [QUOTIDIEN]
  from SR
 where rn = 1;
 
ANNEE MOIS JOUR AGENCE AFFAIRE ANNUEL MENSUEL QUOTIDIEN
----- ---- ---- ------ ------- ------ ------- ---------
2010     1    4   LYON       1 270,00  270,00 210,00
2010     1    4  PARIS       2  60,00   60,00  30,00

18/06/2010, 18h42
Oishiiii

C'est tellement plus simple.. :salive:
Je vais retourner bosser les fonctions de fenêtrage. :fessee:

Merci pour vos réponses.

Je viens de tester la requête utilisant le fenêtrage de Waldar qui me parait intéressante mais à priori elle ne règle pas encore mon problème pas sous la forme actuelle ...

En ajoutant une ligne dans la table :

Code:

INSERT INTO [AXIGEST_SURF_ODS].[dbo].[Facturation]([ANNEE],[MOIS],[JOUR],[AGENCE],[AFFAIRE] ,[MONTANT]) VALUES (2010,2,10,PARIS,1,100)

Si on teste sur le 04/05/2010, on obtient ceci :
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 WITH SR ([ANNEE],[MOIS],[JOUR],[AGENCE],[AFFAIRE],[ANNUEL],[MENSUEL],[QUOTIDIEN], rn) AS ( SELECT [ANNEE],[MOIS],[JOUR],[AGENCE],[AFFAIRE], sum([MONTANT]) over(partition BY [AFFAIRE], [ANNEE]) , sum([MONTANT]) over(partition BY [AFFAIRE], [ANNEE], [MOIS]) , sum([MONTANT]) over(partition BY [AFFAIRE], [ANNEE], [MOIS], [JOUR]), row_number() over(partition BY [AFFAIRE] ORDER BY [ANNEE] DESC, [MOIS] DESC, [JOUR] DESC) FROM [dbo].[Facturation] WHERE [ANNEE] <= 2010 AND [MOIS] <= 5 AND [JOUR] <= 4 ) SELECT [ANNEE],[MOIS],[JOUR],[AGENCE],[AFFAIRE], [ANNUEL], [MENSUEL], [QUOTIDIEN] FROM SR WHERE rn = 1; 2010 1 4 LYON 1 280,00 280,00 210,00 2010 1 4 PARIS 2 60,00 60,00 30,00
On remarque que la facture du 10/02/2010 n'est pas prise en compte, que les valeurs mensuelles et quotidiennes devrait être à 0 et que il faudrait afficher 2010 5 4 à la place de 2010 1 4 ....

Oui j'ai survolé cette partie, c'est le résultat d'une mauvaise modélisation.

Il faudrait une table calendrier dans votre modèle de données, et aussi utiliser une seule colonne au format date plutôt que trois colonnes numériques.

Néanmoins, on peut tout convertir, mais celà à un coût en terme d'écriture du code et d'exécution comme vous allez le constater :

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
declare @dt  smalldatetime;
 
set @dt = convert(smalldatetime, '04/05/2010', 103);
 
with Calendar (dt) as
(
select @dt
 union all
select c.dt - 1
  from Calendar as C
 where year(c.dt-1) = year(@dt)
)
  ,  New_Facturation (dt, [AGENCE], [AFFAIRE], [MONTANT]) as
(
select convert(smalldatetime, cast(cast([ANNEE]*1e4 + [MOIS]*1e2 + [JOUR] as int) as char(8)), 112),
       [AGENCE], [AFFAIRE], [MONTANT]
  from [dbo].[Facturation]
 where @dt >= convert(smalldatetime, cast(cast([ANNEE]*1e4 + [MOIS]*1e2 + [JOUR] as int) as char(8)), 112)
)
  ,  Affaire_d ([AFFAIRE]) as
(
select distinct [AFFAIRE]
  from New_Facturation
)
  ,  Affaire_ag ([AFFAIRE], [AGENCE], rn) as
(
select [AFFAIRE], [AGENCE],
       row_number() over(partition by [AFFAIRE] order by dt desc)
  from New_Facturation
)
  ,  SR (dt, [ANNEE],[MOIS],[JOUR],[AGENCE],[AFFAIRE],[ANNUEL],[MENSUEL],[QUOTIDIEN]) as
(
select C.dt, year(C.dt), month(C.dt), day(C.dt), G.[AGENCE], A.[AFFAIRE],
       coalesce(sum([MONTANT]) over(partition by A.[AFFAIRE], year(C.dt)), 0),
       coalesce(sum([MONTANT]) over(partition by A.[AFFAIRE], year(C.dt), month(C.dt)), 0),
       coalesce(sum([MONTANT]) over(partition by A.[AFFAIRE], year(C.dt), month(C.dt), day(C.dt)), 0)
  from Calendar as C
       cross join Affaire_d as A
       inner join Affaire_ag as G
         on G.[AFFAIRE] = A.[AFFAIRE]
       left outer join New_Facturation as F
         on F.dt        = C.dt
        and F.[AFFAIRE] = A.[AFFAIRE]
 where G.rn = 1
)
select distinct
       [ANNEE], [MOIS], [JOUR], [AGENCE], [AFFAIRE],
       [ANNUEL], [MENSUEL], [QUOTIDIEN]
  from SR
 where dt = @dt
 order by 1 desc
option (maxrecursion 366);

04/01/2010

Code:

1
2
3
4
set @dt = convert(smalldatetime, '04/01/2010', 103);
 
2010	1	4	LYON	1	270,00	270,00	210,00
2010	1	4	PARIS	2	60,00	60,00	30,00

10/02/2010

Code:

1
2
3
4
5
set @dt = convert(smalldatetime, '10/02/2010', 103);
 
2010	2	10	PARIS	1	370,00	100,00	100,00
2010	2	10	PARIS	2	60,00	0,00	0,00
2010	2	10	PARIS	3	1000,00	0,00	0,00

04/05/2010

Code:

1
2
3
4
5
set @dt = convert(smalldatetime, '04/05/2010', 103);
 
2010	5	4	PARIS	1	370,00	0,00	0,00
2010	5	4	PARIS	2	60,00	0,00	0,00
2010	5	4	PARIS	3	1000,00	0,00	0,00

21/06/2010, 12h22
Mirror69

Merci, je vais prendre le temps de bien décortiquer la requête et de faire quelques tests avant de mettre ce post à "Résolu".

Moi qui me suit arrêté aux requêtes simples de SQL Server 2000, je vois que j'ai du boulot pour assimiler toutes les possibilités de SQL Server 2008.

Super boulot !
22/06/2010, 09h39
Mirror69

OK je m'incline, ça marche !