0

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?

1

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?

--drop table numbergame
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)
,('01/01/2016', 110, 120, 130, 140, 150)
,('02/01/2016', 11, 12, 13, 14, 15)
,('03/01/2016', 15, 14, 13, 12, 11)
,('04/01/2016', 19, 18, 17, 16, 15)
,('05/01/2016', 15, 16, 17, 18, 19)
,('06/01/2016', 110, 18, 16, 14, 12)
,('07/01/2016', 111, 112, 113, 114, 115)
,('08/01/2016', 115, 114, 112, 113, 111)
,('09/01/2016', 13, 13, 13, 13, 13)
,('10/01/2016', 14, 14, 14, 14, 14)
,('11/01/2016', 12, 12, 12, 12, 12)
,('12/01/2016', 14, 14, 14, 14, 14)
;
-- Rolling twelve month total by using a correlated sub-query
with numbergameAlreadySummed as
(
select IDATE,VALUE = ISNULL([value1],0)+ISNULL([value2],0)+ISNULL([value3],0)+ISNULL([value4],0)+ISNULL([value5],0)
from numbergame
)
--select * from numbergamealreadysummed
SELECT a.[IDate]
    ,a.Value
    ,Rolling12Months = CASE 
        WHEN ROW_NUMBER() OVER (
                ORDER BY a.[IDate]
                ) < 12
            THEN NULL
        ELSE a.Value + (
                SELECT Value = SUM(Value)
                FROM (
                    SELECT TOP 11 b.[IDate]
                        ,Value
                    FROM numbergamealreadysummed b
                    WHERE b.[IDate] < a.[IDate]
                    ORDER BY b.[IDate] DESC
                    ) b
                )
        END
FROM numbergamealreadysummed a
ORDER BY a.[IDate];
6
  • same issue as before, I get an invalied column name 'Value' On the Select a.[iDate], a.Value is 1st instance, second is the ... ELSE a.VALUE + Sep 14 '16 at 13:21
  • wow - now, I'm really confused - I copied and pasted the code I supplied in my answer into a new SSMS query window pointing to a Sql 2008 instance and I didn't receive any error and was able to get my result set back Sep 14 '16 at 13:24
  • I just rebooted and tried again and now the error I receive is - Recursive common table expression 'numbergameAlreadySummed' does not contain a top-level UNION ALL operator Sep 14 '16 at 13:34
  • That is strange - I originally ran my test against Sql 2008, but I noticed you were on Sql 2008 R2, so I ran my test on Sql 2008 R2 instance and I still didn't get any errors and my results came back fine. I'm assuming you're copying my code exactly intact and making no changes? Sep 14 '16 at 13:42
  • I see what I did. The last FROM in your query I was pointing at my source table NOT the CTE you created. Sorry for causing a headache for you! And thank you for the help Sep 14 '16 at 13:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.