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