Rolling 12 Month Query

Hi All I posted on here the other Day and was helped out by you Friendly people but I now need to add some more columns to my Query

The working Query is 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     calldate AS tran_date, outcome, 0 AS quantity
                                                       FROM          dbo.dmhelp
                                                       WHERE      (calldate >= '2012-01-01')
                                                       UNION ALL
                                                       SELECT     dbo.ctran.ct_date, NULL AS Expr1, 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_1.Year_Month, SUM(r.Quantity) AS running
      FROM          CTE AS CTE_1 LEFT OUTER JOIN
                             CTE AS r ON r.Year_Month BETWEEN DATEADD(MONTH, - 11, CTE_1.Year_Month) AND CTE_1.Year_Month
      GROUP BY CTE_1.Year_Month)
    SELECT     TOP (100) PERCENT 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_1.running, 0) 
                            AS [12 Month Rolling Qty]
     FROM         CTE AS C LEFT OUTER JOIN
                            run AS run_1 ON C.Year_Month = run_1.Year_Month AND run_1.Year_Month >=
                                (SELECT     DATEADD(MONTH, 11, MIN(Year_Month)) AS Expr1
                                  FROM          CTE)
     ORDER BY year_no, Month_no

Open in new window


Basically I need to be able to add another column showing the rolling Quantity on the Field Returns, The same that has been done for the field Quantity

however I am unsure of how to amend the code

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.

Brendt HessSenior DBACommented:
This should do the trick.  Note that I reformatted the code for my reading pleasure (that is, I put it in my preferred format before analyzing the query), which is presented here for your potential edification into another format that you may love (or hate) for laying out queries.  Personally, I like when all top-level keywords in a query (SELECT... FROM... WHERE... joins, ORDER BY, etc) are at the same indent level, and nested queries have their own indent level.

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
			calldate AS tran_date, 
			outcome, 
			0 AS quantity
		FROM dbo.dmhelp
		WHERE calldate >= '2012-01-01'

		UNION ALL

		SELECT
			dbo.ctran.ct_date, 
			NULL AS Expr1, 
			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_1.Year_Month, 
		SUM(r.Quantity) AS running,  --<<--<<-- CHANGES HERE
		SUM(r.Returns) AS Returns  --<<--<<-- CHANGES HERE
	FROM CTE AS CTE_1 
	LEFT OUTER JOIN CTE AS r 
		ON r.Year_Month BETWEEN DATEADD(MONTH, - 11, CTE_1.Year_Month) AND CTE_1.Year_Month
	GROUP BY CTE_1.Year_Month
	)

SELECT TOP 100 PERCENT 
	DATENAME(MONTH, C.Year_Month) + ' ' + CAST(YEAR(C.Year_Month) AS varchar(4)) 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_1.running, 0) AS [12 Month Rolling Qty],  --<<--<<-- CHANGES HERE
	ISNULL(run_1.Returns, 0) AS [12 Month Rolling Returns]  --<<--<<-- CHANGES HERE
FROM CTE AS C 
LEFT OUTER JOIN run AS run_1 
	ON C.Year_Month = run_1.Year_Month 
	AND run_1.Year_Month >= (
		SELECT DATEADD(MONTH, 11, MIN(Year_Month)) AS Expr1
		FROM CTE
		)
ORDER BY year_no, Month_no

Open in new window

I should also note that on my SSMS, I have the tabs set to 4 spaces for ease of reading.
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:
no points please.

" TOP 100 PERCENT " may be removed:
the optimizer recognizes that TOP 100 PERCENT qualifies all rows and does not need to be computed at all.  It gets removed from the query plan, and there is no other reason to do an intermediate sorting operation.  As such, the output isn't returned in any particular order.
from MSDN : TOP 100 Percent ORDER BY Considered Harmful.

ps:
originally provided format may be seen here  (I prefer 'commas first' :) but not too dissimilar
0
pepps11976Author Commented:
Guys you are Great :))

I have only two more columns to add which are Percentages that I need, before I mark as answered shall I add that here or start a new thread?.

Hopefully one of you guys may pick it up

John
0
PortletPaulfreelancerCommented:
no points please:

why don't you try it?

bhess1 has clearly marked where extra columns go - and you would learn more by trying than asking.

also, percentages of what? (it may be clear to you, but my mind may do something weird you didn't expect)

tips:
I expect it would be after line 45, when all the data you need is available

for decimal percentage results you may need to do something like
,  (C.Agreed_Returns * 100.0) / C.Returns

try this for some ideas:
select
  cast( (57 * 100) / 101 as money)
, cast( (57 * 100.0) / 101 as decimal(5,2))
, cast( (57 * 100.0) / 101 as money)
0
pepps11976Author Commented:
Ok I will give it a bash.

Thanks again

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 2008

From novice to tech pro — start learning today.