Discussion :

[Reskibil]

Bonjour,

J'ai cette requête :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
SELECT     dbo.[Dim Company].GKA, 
[Country Code], 
[Reporting Line], 
CASE WHEN left([Proposal Type],3)='New' then 'New Proposal' WHEN left([Proposal Type],3)='Ren' then 'Renewal' else null end as [Type], 
CASE WHEN LEFT([Proposal Status], 3) >= '020' THEN 'Issued' ELSE NULL END AS [Status], 
 YEAR([Issuing Date]) AS Year,
FROM         dbo.[Fact Proposal] INNER JOIN
                      dbo.[Dim Company] ON dbo.[Fact Proposal].Company_Key = dbo.[Dim Company].Company_Key
WHERE LEFT([Proposal Status], 3) >= '020'
and LEFT([Proposal Type], 3)='New'
group by dbo.[Dim Company].GKA, dbo.[Fact Proposal].[Country Code], dbo.[Fact Proposal].[Reporting Line]
,YEAR([Issuing Date]),left([Proposal Type],3),LEFT([Proposal Status], 3)

Elle marche bien à part pour les [Proposal Type]. Ce champ peut avoir plusieurs valeurs (New Proposal-Existing Client, New Proposal-New Client, etc...) et je ne voudrais que [New Proposal] (le case au début quoi). Le probleme c'est qu'au niveau du group by, les lignes ne sont pas aggrégée à ce niveau. J'ai donc des doublons. N'étant pas très très doué en SQL, j'ai donc besoin d'un petit coup de main.

Merci d'avance

[al1_24]

il suffit de rappeler dans le GROUP BY les termes de l'expression à regrouper :

Code :

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

1
2
3
4
5
6
7
...
group by dbo.[Dim Company].GKA, 
dbo.[Fact Proposal].[Country Code], 
dbo.[Fact Proposal].[Reporting Line], 
YEAR([Issuing Date]),
CASE WHEN left([Proposal Type],3)='New' then 'New Proposal' WHEN left([Proposal Type],3)='Ren' then 'Renewal' else null end as [Type], 
CASE WHEN LEFT([Proposal Status], 3) >= '020' THEN 'Issued' ELSE NULL END AS [Status]

[Reskibil]

Merci pour la réponse et désolé pour mon retard mais j'étais parti sur tout autre chose.

Bon en gros ca marche pas.

Voila la requete complete :

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
53
54
SELECT     dbo.[Dim Company].GKA, 
[ZIG Number], 
[Country Code], 
[Reporting Line], 
CASE WHEN left([Proposal Type],3)='New' then 'New Proposal' 
WHEN left([Proposal Type],3)='Ren' then 'Renewal' else null end as [Type], 
CASE WHEN LEFT([Proposal Status], 3) >= '020' THEN 'Issued' ELSE NULL END AS [Status], 
YEAR([Issuing Date]) AS Year,
Case when MONTH([Issuing Date])=1 then count(*) else 0 end as [Nb1], 
Case when MONTH([Issuing Date])=2 then count(*) else 0 end as [Nb2],
Case when MONTH([Issuing Date])=3 then count(*) else 0 end as [Nb3],
Case when MONTH([Issuing Date])=4 then count(*) else 0 end as [Nb4],
Case when MONTH([Issuing Date])=5 then count(*) else 0 end as [Nb5],
Case when MONTH([Issuing Date])=6 then count(*) else 0 end as [Nb6],
Case when MONTH([Issuing Date])=7 then count(*) else 0 end as [Nb7],
Case when MONTH([Issuing Date])=8 then count(*) else 0 end as [Nb8],
Case when MONTH([Issuing Date])=9 then count(*) else 0 end as [Nb9],
Case when MONTH([Issuing Date])=10 then count(*) else 0 end as [Nb10],
Case when MONTH([Issuing Date])=11 then count(*) else 0 end as [Nb11],
Case when MONTH([Issuing Date])=12 then count(*) else 0 end as [Nb12],
Case when MONTH([Issuing Date])=1 then sum(dbo.[Fact Proposal].[Proposal Amount (Euro)]) else 0 end as [Amount1], 
Case when MONTH([Issuing Date])=2 then sum(dbo.[Fact Proposal].[Proposal Amount (Euro)]) else 0 end as [Amount2],
Case when MONTH([Issuing Date])=3 then sum(dbo.[Fact Proposal].[Proposal Amount (Euro)]) else 0 end as [Amount3],
Case when MONTH([Issuing Date])=4 then sum(dbo.[Fact Proposal].[Proposal Amount (Euro)]) else 0 end as [Amount4],
Case when MONTH([Issuing Date])=5 then sum(dbo.[Fact Proposal].[Proposal Amount (Euro)]) else 0 end as [Amount5],
Case when MONTH([Issuing Date])=6 then sum(dbo.[Fact Proposal].[Proposal Amount (Euro)]) else 0 end as [Amount6],
Case when MONTH([Issuing Date])=7 then sum(dbo.[Fact Proposal].[Proposal Amount (Euro)]) else 0 end as [Amount7],
Case when MONTH([Issuing Date])=8 then sum(dbo.[Fact Proposal].[Proposal Amount (Euro)]) else 0 end as [Amount8],
Case when MONTH([Issuing Date])=9 then sum(dbo.[Fact Proposal].[Proposal Amount (Euro)]) else 0 end as [Amount9],
Case when MONTH([Issuing Date])=10 then sum(dbo.[Fact Proposal].[Proposal Amount (Euro)]) else 0 end as [Amount10],
Case when MONTH([Issuing Date])=11 then sum(dbo.[Fact Proposal].[Proposal Amount (Euro)]) else 0 end as [Amount11],
Case when MONTH([Issuing Date])=12 then sum(dbo.[Fact Proposal].[Proposal Amount (Euro)]) else 0 end as [Amount12],
Case when MONTH([Issuing Date])=1 then sum(dbo.[Fact Proposal].[Proposal Amount (Euro)]*[CAT N]/100) else 0 end as [AmountCAT1], 
Case when MONTH([Issuing Date])=2 then sum(dbo.[Fact Proposal].[Proposal Amount (Euro)]*[CAT N]/100) else 0 end as [AmountCAT2],
Case when MONTH([Issuing Date])=3 then sum(dbo.[Fact Proposal].[Proposal Amount (Euro)]*[CAT N]/100) else 0 end as [AmountCAT3],
Case when MONTH([Issuing Date])=4 then sum(dbo.[Fact Proposal].[Proposal Amount (Euro)]*[CAT N]/100) else 0 end as [AmountCAT4],
Case when MONTH([Issuing Date])=5 then sum(dbo.[Fact Proposal].[Proposal Amount (Euro)]*[CAT N]/100) else 0 end as [AmountCAT5],
Case when MONTH([Issuing Date])=6 then sum(dbo.[Fact Proposal].[Proposal Amount (Euro)]*[CAT N]/100) else 0 end as [AmountCAT6],
Case when MONTH([Issuing Date])=7 then sum(dbo.[Fact Proposal].[Proposal Amount (Euro)]*[CAT N]/100) else 0 end as [AmountCAT7],
Case when MONTH([Issuing Date])=8 then sum(dbo.[Fact Proposal].[Proposal Amount (Euro)]*[CAT N]/100) else 0 end as [AmountCAT8],
Case when MONTH([Issuing Date])=9 then sum(dbo.[Fact Proposal].[Proposal Amount (Euro)]*[CAT N]/100) else 0 end as [AmountCAT9],
Case when MONTH([Issuing Date])=10 then sum(dbo.[Fact Proposal].[Proposal Amount (Euro)]*[CAT N]/100) else 0 end as [AmountCAT10],
Case when MONTH([Issuing Date])=11 then sum(dbo.[Fact Proposal].[Proposal Amount (Euro)]*[CAT N]/100) else 0 end as [AmountCAT11],
Case when MONTH([Issuing Date])=12 then sum(dbo.[Fact Proposal].[Proposal Amount (Euro)]*[CAT N]/100) else 0 end as [AmountCAT12]
FROM         dbo.[Fact Proposal] INNER JOIN
                      dbo.[Dim Company] ON dbo.[Fact Proposal].Company_Key = dbo.[Dim Company].Company_Key
WHERE [GKA] <>'' and [GKA]IS NOT NULL and LEFT([Proposal Status], 3) >= '020'
group by dbo.[Dim Company].GKA, 
dbo.[Dim Company].[ZIG Number], 
dbo.[Fact Proposal].[Country Code], 
dbo.[Fact Proposal].[Reporting Line],
CASE WHEN left([Proposal Type],3)='New' then 'New Proposal' 
          WHEN left([Proposal Type],3)='Ren' then 'Renewal' else null end,
CASE WHEN LEFT([Proposal Status], 3) >= '020' THEN 'Issued' ELSE NULL END, [Issuing Date]

Le probleme semble venir du [Issuing Date] car meme avec ca les group, je continue d'avoir des lignes non groupées.

En fait je pense qu'il me groupe les lignes par month([Issuing Date]) alors que je voudrais que ce soit fait par Year.

J'arrive en 2 temps (2 vues quoi) mais ce serait mieux en une seule.

Merci d'avance.

Petite précision : C'est destiné à SQL Server 2005