Using CTE to union multiple result sets into one result set with separate columns

Question

I am trying to use CTE to Union multiple result sets into one result set with 6 columns.

This is the query I am starting with, it returns 3 result sets with 2 rows each:

DECLARE @User AS VARCHAR(50)
SET @User = 'testuser'

DECLARE @LastYearEndDate AS DATETIME
DECLARE @LastMonthEndDate AS DATETIME
DECLARE @LastWeekEndDate AS DATETIME
SET @LastYearEndDate = (SELECT DATEADD(dd,-1, DATEADD(yy, DATEDIFF(yy, 0, GETDATE()), 0)))
SET @LastMonthEndDate = (SELECT DATEADD(d,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))
SET @LastWeekEndDate = (SELECT DATEADD(day, -1 - (DATEPART(dw, GETDATE()) + @@DATEFIRST - 2) % 7, GETDATE()))

SELECT SUM(os.db_Exp) 
    AS YearToDateRevenue, 
    COUNT(*) AS YearToDateCount
FROM tblOrderSum os
    JOIN tblUserProfile up
        ON os.db_RTS = up.db_UserId
WHERE os.db_Deleted = 0 
    AND os.db_Date39 > @LastYearEndDate
    AND os.db_Date45 = '1900/01/01'
    AND db_Email LIKE @User + '%'


SELECT SUM(os.db_Exp) 
    AS MonthToDateRevenue, 
    COUNT(*) AS MonthToDateCount
FROM tblOrderSum os
    JOIN tblUserProfile up
        ON os.db_RTS = up.db_UserId
WHERE os.db_Deleted = 0 
    AND os.db_Date39 > @LastMonthEndDate
    AND os.db_Date45 = '1900/01/01'
    AND db_Email LIKE @User + '%'

SELECT SUM(os.db_Exp) 
    AS WeekToDateRevenue, 
    COUNT(*) AS WeekToDateCount
FROM tblOrderSum os
    JOIN tblUserProfile up
        ON os.db_RTS = up.db_UserId
WHERE os.db_Deleted = 0 
    AND os.db_Date39 > @LastWeekEndDate
    AND os.db_Date45 = '1900/01/01'
    AND db_Email LIKE @User + '%'

Output:

  Result set 1:
    YearToDateRevenue | YearToDateCount
            120               3
  Result set 2:
    MonthToDateRevenue | MonthToDateCount
            75                2
  Result set 3:
    WeekToDateRevenue | WeekToDateCount
            18                1

There maybe an easier way to do this since I am only switching out one parameter each time, but this is my attempt:

/* Same declarations as above */

WITH cte AS (
SELECT
    os.db_Exp
FROM tblOrderSum os
    JOIN tblUserProfile up
        ON os.db_RTS = up.db_UserId
WHERE os.db_Deleted = 0 
    AND os.db_Date39 > @LastYearEndDate
    AND os.db_Date39 > @LastMonthEndDate
    AND os.db_Date39 > @LastWeekEndDate
    AND os.db_Date45 = '1900/01/01'
    AND db_Email LIKE @User + '%')

 SELECT
(SELECT SUM(os.db_Exp) AS YearToDateRevenue, 
    COUNT(*) AS YearToDateCount
FROM cte
WHERE os.db_Date39 > @LastYearEndDate)

(SELECT SUM(os.db_Exp) AS YearToDateRevenue, 
    COUNT(*) AS YearToDateCount
FROM cte
WHERE  os.db_Date39 > @LastMonthEndDate)

(SELECT SUM(os.db_Exp) AS YearToDateRevenue, 
    COUNT(*) AS YearToDateCount
FROM cte
WHERE os.db_Date39 > @LastWeekEndDate)

Right now I am getting the errors:

Msg 4104, Level 16, State 1, Line 28
The multi-part identifier "os.db_Date39" could not be bound.
Msg 4104, Level 16, State 1, Line 25
The multi-part identifier "os.db_Exp" could not be bound.
Msg 116, Level 16, State 1, Line 30
Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.

Output I am trying to get:

 YearToDateRevenue | YearToDateCount | MonthToDateRevenue | MonthToDateCount | WeekToDateRevenue | WeekToDateCount
       120                  3                  75                  2                  18                  1

I am unsure of the correct way to incorporate the parameters in the cte and get this to run. Any simpler solution that I am not seeing would be appreciated as well. I am just hoping to have each sum and count in a different column. There is only one number being returned for each so this should return one row and 6 columns. Thanks

Answer 1

You can use CASE to achieve this:

SELECT SUM(CASE WHEN os.db_Date39 > @LastYearEndDate THEN os.db_Exp ELSE 0 END) AS YearToDateRevenue, 
    SUM(CASE WHEN os.db_Date39 > @LastYearEndDate THEN 1 ELSE 0 END) AS YearToDateCount,
    SUM(CASE WHEN os.db_Date39 > @LastMonthEndDate THEN os.db_Exp ELSE 0 END) AS MonthToDateRevenue, 
    SUM(CASE WHEN os.db_Date39 > @LastMonthEndDate THEN 1 ELSE 0 END) AS MonthToDateCount,
    SUM(CASE WHEN os.db_Date39 > @LastWeekEndDate THEN os.db_Exp ELSE 0 END) AS WeekToDateRevenue, 
    SUM(CASE WHEN os.db_Date39 > @LastWeekEndDate THEN 1 ELSE 0 END) AS WeekToDateCount
FROM tblOrderSum os
    JOIN tblUserProfile up
        ON os.db_RTS = up.db_UserId
WHERE os.db_Deleted = 0 
    AND os.db_Date45 = '1900/01/01'
    AND db_Email LIKE @User + '%'