How to calculate the difference in revenue for 2 specific periods from data in a single table?

I am using SQL Server 2014 and I have the following T-SQL query which is supposed to calculate the difference in total revenue based on 2 specific periods. The data to perform the computation come from a single SQL Table.

To summarize, I have a column in table T1 called Revenue and another column called Month. I need to find the difference in revenue for Months September 2020 to December 2020 versus September 2019 to December 2019.

My T-SQL query stands as follows:

USE [MyDatabase]

;with cte1 as 
(
  SELECT 
    sum ([Revenue]) as 'Revenue Sep 19 - Dec 19'
  FROM
    [T1]
  WHERE
    [Month] between '2019-09-01' and '2019-12-01'
),
cte2 as (
  SELECT
    sum ([Revenue]) as 'Revenue Sep 20 - Dec 20'
  FROM
    [T1]
  WHERE
    [Month] between '2020-09-01' and '2020-12-01'
),
cte3 as (
  SELECT 
    cte2.[Revenue Sep 20 - Dec 20] as 'Total Revenue',
    'Sep 20 - Dec 20' as 'Period',
    '1' as 'ID'
  FROM
    [cte2]

  UNION ALL 

  SELECT 
    cte1.[Revenue Sep 19 - Dec 19] as 'Total Revenue',
    'Sep 19 - Dec 19' as 'Period',
    '1' as 'ID'
  FROM
    [cte1]
)
select a.[Total Revenue] - b.[Total Revenue]
from
  (select cte3.[Total Revenue] from [cte3] where cte3.[Period] = 'Sep 20 - Dec 20') a
  JOIN
  (select cte3.[Total Revenue] from [cte3] where cte3.[Period] = 'Sep 19 - Dec 19') b
  ON b.[ID] = a.[ID]

I have based my query on the following: How to calculate between different group of rows of the same table

However, when running my query, I am getting the following error message:

Invalid column name ‘ID’.

I can’t figure out what I am doing wrong here. Isn’t column ID present in the cte3?

Answer

ID must be present in the select list of both a and b for it to be visible to the join:

from
  (select cte3.ID, cte3.[Total Revenue] from [cte3] where cte3.[Period] = 'Sep 20 - Dec 20') a
  JOIN
  (select cte3.ID, cte3.[Total Revenue] from [cte3] where cte3.[Period] = 'Sep 19 - Dec 19') b
  ON b.[ID] = a.[ID]

Advertisement

Answer