lundi 26 octobre 2015

Group by calculated Case column from another table

I have the below code which works correctly however the data isn't in the format that I want to display. The code only works when I group by Plant however this will obviously create multiple PlantGrps. Is it possible to group by the case when statement?

select 
    Case 
    When Plant IN(
                    Select Client From 
                                            CO_001_Plants_090_Final
                                                                    where CustGrp = 'HovisMills'
                    ) Then 'HovisMills' 
        When Plant IN(
                    Select Client From 
                                            CO_001_Plants_090_Final
                                                                    where CustGrp = 'HovisBakeries'
                    ) Then 'HovisBakeries'                  

                            When Plant IN(
                    Select Client From 
                                            CO_001_Plants_090_Final
                                                                    where CustGrp = 'HovisGroup'
                    ) Then 'HovisGroup'     

                    end as PlantGrp,
    --Type
    sum(Line_Val),Count(Material)

    FROM            dbo.vw_DA_003_ChargingRecord as P

    where   

            Plant IN(select Client 
    FROM            dbo.CO_001_Plants_090_Final
    where Upper(CustGrp) = 'HOVIS')
    and Charging_Period = '201509'
    and Type <> 'Carriage'
    group by 


    Plant, Type

Aucun commentaire:

Enregistrer un commentaire