Discussion :

[Mirror69]

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]

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]

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]

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]

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]

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