samedi 14 novembre 2015

Bring through previous 12 months count while grouping by period

I'm trying to use the below code to bring though a count of all lines in the last 12 month period for the period and plant, please see the output below. So for example with the below output, rather than the 12 months column currently containing the total for the period, I want the count using a period between 201001-201101 (Please note, my example was only for the dataset below and the 12 months column needs to adapt for each period).

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