Bring through previous 12 months count while grouping by period

Question

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      

Answer 1

The problem seems to be that you're grouping by year/month and trying to sum values outside that year/month range. Without sample data, I can't be certain, but it sounds like you want a rolling 12 month sum. 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 first get the monthly values

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 that as the base to get the last 12 month of result using a 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 of Dt