Sql Query With Rolling 12 Months

Hi All I have the following Query which seems to work ok

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

Open in new window


But I need to add another column that adds a total for the column Quantity as a rolling 12 months so it would total the previous 12 months and add that total in the column at the moment the query looks like this

Data
John
pepps11976Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

DcpKingCommented:
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
0
Ryan McCauleyEnterprise Analytics ManagerCommented:
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')))

Open 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.
0
pepps11976Author Commented:
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
0
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

Ryan McCauleyEnterprise Analytics ManagerCommented:
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

Open in new window

0
pepps11976Author Commented:
Ok this is the error I am getting

Error
John
0
PortletPaulfreelancerCommented:
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 |

Open in new window

0
pepps11976Author Commented:
Ok I am trying to reproduce this in a query

Data
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
0
PortletPaulfreelancerCommented:
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)
0
pepps11976Author Commented:
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
0
PortletPaulfreelancerCommented:
>>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
;

Open in new window

line 1: "TOP 100 PERCENT" achieves nothing, it may be removed.
Lines 2-5 & 35-37: It will be of benefit later (for the running total) to simplify these so that instead of a seperate year and month - plus varchar - that you work with a single date being the 1st of each month
Lines 10-19: there does not appear to be any reason for dbo.dmcont or dbo.dmhelpeq
Lines 21-32: you use an OUTER join, but then include both tables in the where clause. This negates the outer join and you may as well use an INNER join.
also, reversing the table sequence appears appropriate (because the original right outer implies dbo.ctran has precedence over dbo.ctran)
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

Open in new window

could you run this version? Does it equate to your original?
0
PortletPaulfreelancerCommented:
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
;

Open in new window

0
pepps11976Author Commented:
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
0
pepps11976Author Commented:
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
0
ZberteocCommented:
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

Open in new window

0
PortletPaulfreelancerCommented:
http:#39528041 >>but the rest are not ie January 13 total should be 65173 but it is 66015

http:#39527773 try with a change in Line 39: alter the -12 to be -11
i.e.
          LEFT JOIN cte AS r ON r.year_month BETWEEN dateadd(MONTH,-11,cte.year_month) AND cte.year_month

I'm assuming the other columns are correct.

Perhaps you could post the results you are looking at?
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
PortletPaulfreelancerCommented:
Excellent, happy this worked out - cheers, Paul
0
ZberteocCommented:
Have you tried my query?
0
pepps11976Author Commented:
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
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.