I am attempting to use Cross Apply
to get a SUM() for each month and create a rolling trend report. My issue is that with my query I keep getting an error of
Msg 207, Level 16, State 1, Line 6
Invalid column name 'TotalCost'.
Which is obviously an error in my syntax, but I just can not seem to find it. Here is the query that I attempted (and have been toying with for 3 hours now)
SELECT
a.[iDate]
,TotalCost=SUM(ISNULL([value1],0)+ISNULL([value2],0)+ISNULL([value3],0)+ISNULL([value4],0)+ISNULL([value5],0))
,Rolling12Months=CASE
WHEN ROW_NUMBER() OVER (ORDER BY a.[iDate]) < 12 THEN NULL
ELSE TotalCost+Value
END
FROM numbergame a
CROSS APPLY
(
SELECT
VALUE
FROM
(
SELECT
b.[iDate]
,VALUE=SUM(ISNULL([value1],0)+ISNULL([value2],0)+ISNULL([value3],0)+ISNULL([value4],0)+ISNULL([value5],0))
FROM numbergame b
WHERE b.[iDate] < a.[iDate]
GROUP BY [iDate]
) b
) b
GROUP BY [iDate]
ORDER BY a.[iDate]
and in all transparency it is Post # 4 taken from Simple Talk. Now here is my sample DDL in a mm/dd/yyyy format with only one day for each month, but it should get the point across.
Create Table numbergame
(
iDate date
,value1 float
,value2 float
,value3 float
,value4 float
,value5 float
)
INSERT INTO numbergame VALUES
('01/01/2015', 10, 20, 30, 40, 50)
,('02/01/2015', 1, 2, 3, 4, 5)
,('03/01/2015', 5, 4, 3, 2, 1)
,('04/01/2015', 9, 8, 7, 6, 5)
,('05/01/2015', 5, 6, 7, 8, 9)
,('06/01/2015', 10, 8, 6, 4, 2)
,('07/01/2015', 11, 12, 13, 14, 15)
,('08/01/2015', 15, 14, 12, 13, 11)
,('09/01/2015', 3, 3, 3, 3, 3)
,('10/01/2015', 4, 4, 4, 4, 4)
,('11/01/2015', 2, 2, 2, 2, 2)
,('12/01/2015', 4, 4, 4, 4, 4)
How should my query (or if there is a better/quicker way to do this) be altered so that I can display my rolling trend?
Best Answer
Without spending a bunch of time analyzing the original Post # 4 taken from Simple Talk, I wrapped up the summing of the columns in a CTE and used that in the main query - it looked simpler to me - does it meet your requirements?