pepps11976
asked on
Sql Query With Rolling 12 Months
Hi All I have the following Query which seems to work ok
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
John
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
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
John
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:
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.
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')))
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.
ASKER
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
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
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.
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 |
ASKER
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
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)
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)
ASKER
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
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
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
;
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.
also, reversing the table sequence appears appropriate (because the original right outer implies dbo.ctran has precedence over dbo.ctran)
;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
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
;
ASKER
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
John
ASKER
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
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:
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Excellent, happy this worked out - cheers, Paul
Have you tried my query?
ASKER
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
thankyou
John
hth
Mike