Sql-server – Query To Show Monthly Income

sql serversql-server-2008-r2t-sql

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?

--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];
Related Question