Post through the previous 12 months when grouping by period

Question

I am trying to use the code below to count all rows for the last 12 month period for a period and setup, see output below. So, for example, with the result below, and not with the 12-month column, which currently contains the total value for the period, I want to calculate using the period between 201001-201101 (note that my example was only for a data set below and a column of 12 months must be adapted for each period).

Period Plant Stock Special MonthTotal 12Months 201101 0EA0 27 0 27 27 201101 0EB0 35 2 37 37 

The problem I am facing is that, instead of migrating the last 12 months, my code simply wraps the bill for the current period. Can someone please help?

  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 

Answer 1

The problem is that you are grouping by year / month and trying to summarize values ​​outside the range of year / month. Without sampled data, I can't be sure, but it looks like you want a 12-month amount. Something like below should get you where you want to go.

 ;with monthlySubtotal as ( select dateadd(m, 1-datepart(day, p.dt), p.dt) as PeriodMonth ,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 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 ,dateadd(m, 1-datepart(day, p.dt), p.dt) ) SELECT convert(varchar(6),m1.PeriodMonth,112) Period , m1.Plant , m1.Stock , m1.Special , m1.MonthTotal , SUM(m2.monthtotal) 12mototal FROM monthlySubtotal m1 JOIN monthlySubtotal m2 ON m2.plant = m1.plant AND m2.periodmonth BETWEEN dateadd(m, -11, m1.periodmonth) AND m1.periodmonth --You may want to filter this --WHERE m1.periodmonth >= startdate GROUP BY convert(varchar(6),m1.PeriodMonth,112) , m1.Plant , m1.Stock , m1.Special , m1.MonthTotal ORDER BY Period , Plant 

Answer 2

There is no need to do everything at the same time.

It's easier to get monthly values ​​first

 SELECT DATEADD(month, DATEDIFF(month, 0, Dt), 0) as FOM , Plant , Stock = SUM(CASE WHEN LEFT(Upper(Material), 2) = 'ZZ' THEN 1 ELSE 0 END) , Special = SUM(CASE WHEN LEFT(Upper(Material), 2) = 'ZZ' THEN 0 ELSE 1 END) FROM DS_POs GROUP BY Plant, DATEADD(month, DATEDIFF(month, 0, Dt), 0) 

and using this as a base to get the last 12-month result with CROSS APPLY

 WITH DS_POSM AS ( SELECT DATEADD(month, DATEDIFF(month, 0, Dt), 0) as FOM , Plant , Stock = SUM(CASE WHEN LEFT(Upper(Material), 2) = 'ZZ' THEN 1 ELSE 0 END) , Special = SUM(CASE WHEN LEFT(Upper(Material), 2) = 'ZZ' THEN 0 ELSE 1 END) FROM DS_POs GROUP BY Plant, DATEADD(month, DATEDIFF(month, 0, Dt), 0) ) SELECT Convert(char(6), FOM, 112) Period , Plant , Stock , Special , MonthTotal = Stock + Special , ly.[12Months] FROM DS_POSM a CROSS APPLY (SELECT Sum(Stock + Special) [12Months] FROM DS_POSM lastyear WHERE lastyear.FOM Between DateAdd(mm, -12, a.FOM) And a.FOM AND lastyear.Plant = a.Plant ) ly ORDER BY FOM, Plant 

DATEADD(month, DATEDIFF(month, 0, Dt), 0) get the first day of the month Dt