Sql Query With Rolling 12 Months

Being lazy, I'd divert the results of your query into a temp table or a table variable that had an extra column for the running 12-month quantity summary and do the calculation there, before querying the lot as the output of whatever procedure you've got this inside.

hth

Mike

A temp table would probably be the easiest way to get this done, though you could use a CTE if you wanted to avoid the temp table but accomplish the same thing:

WITH InnerQuery
  AS (Your inner query from above)
SELECT iq.*, sum(r.Quantity) as Rolling12Months
  FROM InnerQuery iq 
  JOIN InnerQuery r -- Rolling 12 months here
    ON (DATEADD(yy, r.Year_No - 100, DATEADD(mm, r.Month_No, CONVERT(DATE, '0099-12-01'))) BETWEEN (DATEADD(yy, iq.Year_No - 101, DATEADD(mm, iq.Month_No, CONVERT(DATE, '0099-12-01'))) AND (DATEADD(yy, iq.Year_No - 100, DATEADD(mm, iq.Month_No, CONVERT(DATE, '0099-12-01')))

Select allOpen in new window

The PITA part is to join on the date you've broken apart, but I think I've done it correctly above. Another option is to leave a date column in your original query (even if it's just YYYY-MM-01) so you can more easily join back to it.

Sorry for the Dumb question but does this get added to the other query?,

If I run it on its own it fails

sorry I am still quite new to SQL Code

John

It doesn't run on its own, it gets combined with your original query - I should have provided more details. I can't validate the syntax right now, but I think this will do it - if you get an error, please post the message and I can resolve it. Also, I missed the necessary group by in my example. Try this:

WITH InnerQuery
  AS (SELECT     TOP (100) PERCENT CAST(DATENAME(Month, tran_date) AS VARCHAR) + ' ' + CAST(YEAR(tran_date) AS VARCHAR) AS Month, DATEPART(mm, tran_date) AS Month_No, 
                      DATEPART(yy, tran_date) AS Year_No, COUNT(outcome) AS [Returns], COUNT(CASE WHEN outcome = 'AGR001' THEN outcome END) AS [Agreed Returns], 
                      SUM(quantity) AS Quantity
FROM         (SELECT     dbo.dmhelp.calldate AS tran_date, dbo.dmhelp.outcome, 0 AS quantity
                       FROM          dbo.dmcont RIGHT OUTER JOIN
                                              dbo.dmhelp ON dbo.dmcont.contid = dbo.dmhelp.contid RIGHT OUTER JOIN
                                              dbo.dmhelpeq ON dbo.dmhelp.helpid = dbo.dmhelpeq.helpid
                       WHERE      (dbo.dmhelp.calldate >= '2012-01-01')
                       UNION ALL
                       SELECT     dbo.ctran.ct_date, NULL AS Expr1, dbo.ctran.ct_quan
                       FROM         dbo.ctran RIGHT OUTER JOIN
                                             dbo.cname ON dbo.ctran.ct_ref = dbo.cname.cn_ref
                       WHERE     (dbo.ctran.ct_type = 'I') AND (dbo.ctran.ct_date >= '2012-01-01') AND (dbo.cname.cn_catag = 'STD' OR
                                             dbo.cname.cn_catag = 'HBNE') AND (dbo.ctran.ct_referen LIKE 'DEL%')) AS derived
GROUP BY CAST(DATENAME(Month, tran_date) AS VARCHAR) + ' ' + CAST(YEAR(tran_date) AS VARCHAR), DATEPART(mm, tran_date), DATEPART(yy, tran_date) )
SELECT iq.*, sum(r.Quantity) as Rolling12Months
  FROM InnerQuery iq 
  JOIN InnerQuery r -- Rolling 12 months here
    ON (DATEADD(yy, r.Year_No - 100, DATEADD(mm, r.Month_No, CONVERT(DATE, '0099-12-01'))) BETWEEN (DATEADD(yy, iq.Year_No - 101, DATEADD(mm, iq.Month_No, CONVERT(DATE, '0099-12-01'))) AND (DATEADD(yy, iq.Year_No - 100, DATEADD(mm, iq.Month_No, CONVERT(DATE, '0099-12-01')))
GROUP BY iq.month, iq.year_no, iq.month_no, iq.returns, iq.[agreed returns], iq.quantity
ORDER BY iq.year_no, iq.month_no

Select allOpen in new window

Ok this is the error I am getting


John

what does '12 rolling months' mean exactly?
id "right now" is: September, 27 2013 07:38:22

would you start at:
1. September, 27 2012 07:38:22
2. September, 27 2012 00:00:00
3. September, 01 2012 00:00:00
4. something else?

Depending on the start point the rest will be determined. e.g.

    select
      getdate() as date_time_now
    
    , dateadd(day, datediff(day,0, DATEADD(day, - (DAY(getdate()) - 1), getdate() ) ), 0)
      as first_of_this_month
    
    , dateadd(day, datediff(day,0, DATEADD(day, - (DAY(getdate()) - 1), dateadd(month,-12,getdate() )) ), 0)
      as last_yr_1st_month
    
    , dateadd(month,-12, dateadd(day, datediff(day,0, getdate() ), 0) )
      as last_yr_same_day_of_month
    
    -- dateadd(day, datediff(day,0, getdate() ), 0)
    

**[Results][2]**:
    
    |                    DATE_TIME_NOW |              FIRST_OF_THIS_MONTH |                LAST_YR_1ST_MONTH |        LAST_YR_SAME_DAY_OF_MONTH |
    |----------------------------------|----------------------------------|----------------------------------|----------------------------------|
    | September, 27 2013 07:38:22+0000 | September, 01 2013 00:00:00+0000 | September, 01 2012 00:00:00+0000 | September, 27 2012 00:00:00+0000 |

Select allOpen in new window

Ok I am trying to reproduce this in a query


Period is the same as month in my query
Qty Del is Quantity in my query
Qty Rej is Returns in my query
and then in the attached image you can see 12-months Rolling Deliveries which is a total of that month plus the previous 11 added up, This is the column I am trying to create.

Hope that makes sense

John

OK, got it now, the image makes it very clear - thank you.
Could you confirm that you are using SQL 2008 - or would you be using SQL 2012?

apologies, more questions.

Is this for a 'report'? If so what is the reporting tool that you are using?
(the reason for asking is that often those tools have in-built running total capability)

Ok I am building the Query in SQL 2008 R2

this is for a report and I will be using Report Builder 3.0 to display the data

Hope that helps

John

>>sorry I am still quite new to SQL Code
No need to apologize, we were all newbie's once. This is your original query - but reformatted

SELECT TOP (100) PERCENT
        CAST(DATENAME(MONTH, tran_date) AS varchar) 
        + ' ' + CAST(YEAR(tran_date) AS varchar)             AS Month
      , DATEPART(mm, tran_date)                              AS Month_No
      , DATEPART(yy, tran_date)                              AS Year_No
      , COUNT(outcome)                                       AS [Returns]
      , COUNT(CASE WHEN outcome = 'AGR001' THEN outcome END) AS [Agreed Returns]
      , SUM(quantity)                                        AS Quantity
FROM (
        SELECT
                dbo.dmhelp.calldate AS tran_date
              , dbo.dmhelp.outcome
              , 0                   AS quantity
        FROM dbo.dmcont
        RIGHT OUTER JOIN dbo.dmhelp
                ON dbo.dmcont.contid = dbo.dmhelp.contid
        RIGHT OUTER JOIN dbo.dmhelpeq
                ON dbo.dmhelp.helpid = dbo.dmhelpeq.helpid
        WHERE (dbo.dmhelp.calldate >= '2012-01-01')
        UNION ALL
                SELECT
                        dbo.ctran.ct_date
                      , NULL AS Expr1
                      , dbo.ctran.ct_quan
                FROM dbo.ctran
                RIGHT OUTER JOIN dbo.cname
                        ON dbo.ctran.ct_ref = dbo.cname.cn_ref
                WHERE (dbo.ctran.ct_type = 'I')
                        AND (dbo.ctran.ct_date >= '2012-01-01')
                        AND (dbo.cname.cn_catag = 'STD'
                        OR dbo.cname.cn_catag = 'HBNE')
                        AND (dbo.ctran.ct_referen LIKE 'DEL%')
        ) AS derived
GROUP BY
         CAST(DATENAME(MONTH, tran_date) AS varchar) + ' ' + CAST(YEAR(tran_date) AS varchar)
       , DATEPART(mm, tran_date)
       , DATEPART(yy, tran_date)
ORDER BY
        year_no, month_no
;

Select allOpen in new window

Taking those comments into account, and introducing a CTE in preparation for the running total, I think this will be an equivalent - but simplified query.

;WITH
 CTE as (
            SELECT
                    DATEADD(month, DATEDIFF(month,0,tran_date) ,0)       AS Year_Month
                  , COUNT(outcome)                                       AS Returns
                  , COUNT(CASE WHEN outcome = 'AGR001' THEN outcome END) AS Agreed_Returns
                  , SUM(quantity)                                        AS Quantity
            FROM (
                    SELECT
                            dbo.dmhelp.calldate AS tran_date
                          , dbo.dmhelp.outcome
                          , 0                   AS quantity
                    FROM dbo.dmhelp
                    WHERE dbo.dmhelp.calldate >= '2012-01-01'
                    UNION ALL
                            SELECT
                                    dbo.ctran.ct_date
                                  , NULL
                                  , dbo.ctran.ct_quan
                            FROM dbo.cname
                            INNER JOIN dbo.ctran
                                    ON dbo.cname.cn_ref =dbo.ctran.ct_ref
                            WHERE dbo.ctran.ct_type = 'I'
                                    AND dbo.ctran.ct_date >= '2012-01-01'
                                    AND (   dbo.cname.cn_catag = 'STD'
                                         OR 
                                            dbo.cname.cn_catag = 'HBNE' 
                                        )
                                    AND dbo.ctran.ct_referen LIKE 'DEL%'
                  ) AS derived
            GROUP BY
                     DATEADD(month, DATEDIFF(month,0,tran_date) ,0)
        )
SELECT       
        CAST(DATENAME(MONTH,Year_Month) AS varchar) 
        + ' ' + CAST(YEAR(Year_Month) AS varchar)   AS Month       
      , DATEPART(month, Year_Month)                 AS Month_No
      , DATEPART(year, Year_Month)                  AS Year_No
      , Returns
      , Agreed_Returns                              AS [Agreed Returns]
      , Quantity
FROM CTE
ORDER BY
        Year_Month

Select allOpen in new window

could you run this version? Does it equate to your original?

Assuming the above is OK, then adding a running total of quantity would be as follows:

;WITH
 CTE as (
            SELECT
                    DATEADD(month, DATEDIFF(month,0,tran_date) ,0)       AS Year_Month
                  , COUNT(outcome)                                       AS Returns
                  , COUNT(CASE WHEN outcome = 'AGR001' THEN outcome END) AS Agreed_Returns
                  , SUM(quantity)                                        AS Quantity
            FROM (
                    SELECT
                            dbo.dmhelp.calldate AS tran_date
                          , dbo.dmhelp.outcome
                          , 0                   AS quantity
                    FROM dbo.dmhelp
                    WHERE dbo.dmhelp.calldate >= '2012-01-01'
                    UNION ALL
                            SELECT
                                    dbo.ctran.ct_date
                                  , NULL
                                  , dbo.ctran.ct_quan
                            FROM dbo.cname
                            INNER JOIN dbo.ctran
                                    ON dbo.cname.cn_ref =dbo.ctran.ct_ref
                            WHERE dbo.ctran.ct_type = 'I'
                                    AND dbo.ctran.ct_date >= '2012-01-01'
                                    AND (   dbo.cname.cn_catag = 'STD'
                                         OR 
                                            dbo.cname.cn_catag = 'HBNE' 
                                        )
                                    AND dbo.ctran.ct_referen LIKE 'DEL%'
                  ) AS derived
            GROUP BY
                     DATEADD(month, DATEDIFF(month,0,tran_date) ,0)
        )
, run AS (
          SELECT
            cte.year_month
          , sum(r.quantity) AS running
          FROM cte
          LEFT JOIN cte AS r ON r.year_month BETWEEN dateadd(MONTH,-12,cte.year_month) AND cte.year_month
          GROUP BY
            cte.year_month
          )
SELECT       
        CAST(DATENAME(MONTH,c.Year_Month) AS varchar) 
        + ' ' + CAST(YEAR(c.Year_Month) AS varchar)   AS Month       
      , DATEPART(month, c.Year_Month)                 AS Month_No
      , DATEPART(year, c.Year_Month)                  AS Year_No
      , c.Returns
      , c.Agreed_Returns                              AS [Agreed Returns]
      , c.Quantity
      , ISNULL(run.running,0)                         AS [12 Month Rolling Qty]
FROM CTE as C
LEFT JOIN run ON cte.year_month = run.year_month
             AND run.year_month >= ( SELECT dateadd(MONTH,11,min(year_month)) FROM cte )
ORDER BY
        Month_No, Year_No
;

Select allOpen in new window

Ok Thanks so much that seems to work, I need to ask other questions regarding the other columns that I need created but shall I ask a different question for that??

John

Ok just looking at the data when I sum up the data the first rolling 12 months is correct but the rest are not ie January 13 total should be 65173 but it is 66015

John

Try the code bellow. I modified a bit to also be more efficient. You don't have to group by all those 3 date casts only by one value which is the first of the month. In the select list you will then need to apply the casts to min(tran_date) in order to avoid the grouping error and use MONTH and YEAR functions instead of DATEPART.

I also added the 12 month column:

SELECT     TOP (100) PERCENT 
	CAST(DATENAME(Month, min(tran_date)) AS VARCHAR) + ' ' + CAST(YEAR(min(tran_date)) AS VARCHAR) AS Month, 
	MONTH(min(tran_date)) AS Month_No, 
	YEAR(min(tran_date)) AS Year_No, 
	COUNT(outcome) AS [Returns], 
	COUNT(CASE WHEN outcome = 'AGR001' THEN outcome END) AS [Agreed Returns], 
	SUM(quantity) AS Quantity,
	SUM(CASE WHEN tran_date > DATEADD(mm, -12, trandate) THEN Quantity ELSE 0 END) AS Last12MontsQuantity
FROM         
(
	SELECT     
		dbo.dmhelp.calldate AS tran_date, 
		dbo.dmhelp.outcome, 0 AS quantity
    FROM          
		dbo.dmcont 
		RIGHT OUTER JOIN dbo.dmhelp 
			ON dbo.dmcont.contid = dbo.dmhelp.contid 
		RIGHT OUTER JOIN dbo.dmhelpeq 
			ON dbo.dmhelp.helpid = dbo.dmhelpeq.helpid
    WHERE
		(dbo.dmhelp.calldate >= '2012-01-01')
    UNION ALL
    SELECT     
		dbo.ctran.ct_date, 
		NULL AS Expr1, 
		dbo.ctran.ct_quan
    FROM         
		dbo.ctran 
		RIGHT OUTER JOIN dbo.cname 
			ON dbo.ctran.ct_ref = dbo.cname.cn_ref
    WHERE     
			(dbo.ctran.ct_type = 'I') 
		AND (dbo.ctran.ct_date >= '2012-01-01') 
		AND (dbo.cname.cn_catag IN ('STD','HBNE')
		AND (dbo.ctran.ct_referen LIKE 'DEL%')
) AS derived
GROUP BY 
	DATEADD(month, DATEDIFF(month, 0, tran_date), 0)
ORDER BY 
	year_no, month_no

Select allOpen in new window

Excellent, happy this worked out - cheers, Paul

Have you tried my query?

Zberteoc, yes thanks for posting, I did try your code, but had some errors, narrowing down to -11 on the days seemed to fix things

thankyou

John