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