February 27, 2008 at 4:05 am
I am looking to create some output that gives a running cumulative total of data throughout a calendar year.
I have so far got some T-SQL that gives Sales in each Year/Month period for each month from Jan 2006 to Feb 2008 inclusive (26 periods). I want to alter this so that the Feb 2006 figure is the cumulative sales from Jan AND Feb 2006 together, and continue that through to Dec 2006 before resetting so that Jan 2007 is ONLY Jan 2007 and then Feb 2007 is the cumulative sales from Jan AND Feb 2007.
Here is the T-SQL:
SELECT
LEFT(Sales.YYYYMM, 4) AS 'Year',
'Month' = case when RIGHT(Sales.YYYYMM, 2) = '01' then 'Jan'
when RIGHT(Sales.YYYYMM, 2) = '02' then 'Feb'
when RIGHT(Sales.YYYYMM, 2) = '03' then 'Mar'
when RIGHT(Sales.YYYYMM, 2) = '04' then 'Apr'
when RIGHT(Sales.YYYYMM, 2) = '05' then 'May'
when RIGHT(Sales.YYYYMM, 2) = '06' then 'Jun'
when RIGHT(Sales.YYYYMM, 2) = '07' then 'Jul'
when RIGHT(Sales.YYYYMM, 2) = '08' then 'Aug'
when RIGHT(Sales.YYYYMM, 2) = '09' then 'Sep'
when RIGHT(Sales.YYYYMM, 2) = '10' then 'Oct'
when RIGHT(Sales.YYYYMM, 2) = '11' then 'Nov'
when RIGHT(Sales.YYYYMM, 2) = '12' then 'Dec' end,
SUM(Sales.Qty) AS 'Sales'
FROM
Sales
GROUP BY
YYYYMM
ORDER BY
YYYYMM
Here is the current result:
2006Jan605242
2006Feb386332
2006Mar970551
2006Apr812792
2006May994796
2006Jun976148
2006Jul794592
2006Aug480582
2006Sep826863
2006Oct1194024
2006Nov928539
2006Dec508839
2007Jan686473
2007Feb493232
2007Mar2166496
2007Apr2247859
2007May2516104
2007Jun2015471
2007Jul1757607
2007Aug1473518
2007Sep2649345
2007Oct2920386
2007Nov2156320
2007Dec2021420
2008Jan2015330
2008Feb1450220
I want the result to be:
2006Jan605242
2006Feb991574
etc...
2007Jan686473
2007Feb 1179705
etc...
Is this possible with just T-SQL? (I do not wish to use a procedure that carries out the cumulative summation first)
Your advice would be greatly appreciated.
February 27, 2008 at 6:48 am
Refusing to consider using a temporary table is going to cost you dearly in performance. The one way to do such a thing creates a dataset with very high cardinality (i.e. it grows very fast as the amount of points to calculate go up).
Here's a decent article on the very subject:
http://www.sqlservercentral.com/articles/Advanced+Querying/61716/[/url]
The "straight T-SQL" with no temporary objects uses comparison non-equijoins to do its work, so it gets very inefficient rather quickly (to the point where it becomes very noticeable, even by human standards). The fact that we need to do the joins on calculated elements isn't going to help the cause either...
;with RunningCTE as
(
SELECT YYYYMM, left(YYYYMM,4)as YYYY, Right(yyyymm,2)as MM,
case when RIGHT(Sales.YYYYMM, 2) = '01' then 'Jan'
when RIGHT(Sales.YYYYMM, 2) = '02' then 'Feb'
when RIGHT(Sales.YYYYMM, 2) = '03' then 'Mar'
when RIGHT(Sales.YYYYMM, 2) = '04' then 'Apr'
when RIGHT(Sales.YYYYMM, 2) = '05' then 'May'
when RIGHT(Sales.YYYYMM, 2) = '06' then 'Jun'
when RIGHT(Sales.YYYYMM, 2) = '07' then 'Jul'
when RIGHT(Sales.YYYYMM, 2) = '08' then 'Aug'
when RIGHT(Sales.YYYYMM, 2) = '09' then 'Sep'
when RIGHT(Sales.YYYYMM, 2) = '10' then 'Oct'
when RIGHT(Sales.YYYYMM, 2) = '11' then 'Nov'
when RIGHT(Sales.YYYYMM, 2) = '12' then 'Dec' end as [Month],
SUM(Sales.Qty) AS 'Sales'
FROM
Sales
GROUP BY YYYYMM
ORDER BY YYYYMM
)
select s.YYYY,
s.MM,
s.sales MonthlySales,
sum(sr.sales) YTDSales
from RunningCTe s
Inner join RunningCTE st on s.YYYY=sr.YYYY and s.YYYYMM>=sr.YYYYMM
Group by s.YYYYMM
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
February 27, 2008 at 9:27 am
Thanks for the suggestion. I tried the code and got an error:
The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is also specified.
I spotted one slight error in the code which was the inner join to Running CTe having a table alias of st when the rest of the statement refered to table alias sr, so I corrected that first of all. The error above is still there and I can't see how to overcome that.
Any further thoughts?
February 27, 2008 at 9:41 am
Actually - the ORDER by doesn't need to be in the inner query (cut and paste error on my part when copying your code). Bump it to the outer query. Should take care of the error.
Again though - unless you plan on using this on only the smallest of data sets, I'd encourage you to read the article mentioned above.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
February 27, 2008 at 11:15 am
Thanks - that works a treat now. I will bear in mind your comments about the temporary table. 🙂
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply