Cumul à date

**Mirror69** · 18/06/2010, 15h42

Bonjour,

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

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

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

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 :

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

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 :

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

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.

**Oishiiii** · 18/06/2010, 16h23

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.

**Mirror69** · 18/06/2010, 16h29

Pour le calcul du cumul à date annuel du 04/01 ET l'affaire 1 on a :

Code :

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

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.

**Mirror69** · 18/06/2010, 16h56

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 :

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

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 :

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

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)

**Oishiiii** · 18/06/2010, 18h06

J'ai utilisé une CTE:

Création de la vue:

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

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

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

Le résultat semble correct:

Code :

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

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.

**Waldar** · 18/06/2010, 18h32

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

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

**Oishiiii** · 18/06/2010, 18h42

C'est tellement plus simple..

Je vais retourner bosser les fonctions de fenêtrage.

**Mirror69** · 21/06/2010, 10h23

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 :

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

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 :

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

**Waldar** · 21/06/2010, 11h52

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 :

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

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

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 :

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

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 :

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

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

**Mirror69** · 21/06/2010, 12h22

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 !

**Mirror69** · 22/06/2010, 09h39

OK je m'incline, ça marche !

Cumul à date

MS SQL Server

Discussions similaires

Partager

Partager