vendredi 13 novembre 2015

Bring through 12 months Count using Group by

I'm trying to add a column to the below code which brings though a count of all lines in the last 12 month period for the period and plant, please see the output below.

Period Plant Stock Special MonthTotal 12Months

201101 0EA0 27 0 27 27

201101 0EB0 35 2 37 37

The issue I'm having is that rather than bring through the last 12 month count, my code is merely bringing through the count for the current period. Please can someone assist?

   select 
            convert(varchar(6),dateadd(mm,0,P.Dt),112) as Period,P.Plant,
            Sum(Case When Left(Upper(Material),2) = 'ZZ' then 1 else 0 end) as Stock,
            Sum(Case When Left(Upper(Material),2) <> 'ZZ' then 1 else 0 end) as Special
            ,Count(*) as MonthTotal,Sum(Case When 
                        convert(varchar(6),dateadd(mm,0,P.Dt),112)  
                 Between
                        convert(varchar(6),dateadd(mm,-12,P.Dt),112)    
                 And
                        convert(varchar(6),dateadd(mm,0,P.Dt),112)  Then 1 else 0 End
                )as [12Months]
    from 
            iesaonline.dbo.DS_POs  as P where  
                                Plant IN(
                    Select Client From METRICS.DBO.CO_001_Plants_090_Final
                                  where CustGrp = 'Hovis'
                                        )
    Group by 
            P.Plant,convert(varchar(6),dateadd(mm,0,P.Dt),112)                                  
    order by 
            convert(varchar(6),dateadd(mm,0,Dt),112),Plant                  

Aucun commentaire:

Enregistrer un commentaire