Probleme de group by

Bonjour,

J'ai cette requête :
Code:

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

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

Code:

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]

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:

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