Attempting to get monthly totals for subqueries

Question

I'm trying to get monthly subtotals, breaking down into specific product types. This worked for one month, but when I try to do a number of months I get repeating totals inside the subqueries.

Tricky part is each Opp Status needs to join a central calendar table using a different date field and has REquirements in the WHERE clause

DECLARE @FISCAL_PERIOD VARCHAR(10)

SET @FISCAL_PERIOD = '2022FP01'

SELECT 
CAL.FISCAL_PERIOD, 'CONTACTABLE' AS 'Opp Status', 
count(*) AS 'Opps', 

    (SELECT count(*) AS 'Windscreen' 
     FROM 
        DET.[LCC_OPP_DETAILS] LCC
     LEFT JOIN 
        [DET_RAW].[ORACLE_DWH].[BELDW_CALENDAR_V2] CAL
     ON 
        LCC.JOB_CONTACTABLE_CREATED_DATE = CAL.CALENDAR_DAY
        AND CAL.ETL_IS_DELETED = 0
        AND LCC.ETL_IS_DELETED = 0
     WHERE 
        CAL.FISCAL_PERIOD >= @FISCAL_PERIOD
        AND LCC.JOB_CONTACTABLE_CREATED_DATE IS NOT NULL
        AND JOB_TYPE = 'WINDSCREEN'
    
    ) AS WINDSCREEN,

    (SELECT count(*) AS 'Windscreen' 
     FROM 
        DET.[LCC_OPP_DETAILS] LCC
     LEFT JOIN 
        [DET_RAW].[ORACLE_DWH].[BELDW_CALENDAR_V2] CAL
     ON 
        LCC.JOB_CONTACTABLE_CREATED_DATE = CAL.CALENDAR_DAY
        AND CAL.ETL_IS_DELETED = 0
     WHERE 
        CAL.FISCAL_PERIOD >= @FISCAL_PERIOD
        AND LCC.JOB_CONTACTABLE_CREATED_DATE IS NOT NULL
        AND JOB_TYPE = 'WINDSCREEN'  
        AND RECAL_FLAG = 1
    ) AS 'Windscreen+Recal'

FROM 
DET.[LCC_OPP_DETAILS] LCC
LEFT JOIN 
[DET_RAW].[ORACLE_DWH].[BELDW_CALENDAR_V2] CAL
ON 
 LCC.JOB_CONTACTABLE_CREATED_DATE = CAL.CALENDAR_DAY
AND CAL.ETL_IS_DELETED = 0
WHERE 
CAL.FISCAL_PERIOD >= @FISCAL_PERIOD
AND LCC.JOB_CONTACTABLE_CREATED_DATE IS NOT NULL
AND LCC.ETL_IS_DELETED = 0

GROUP BY 
CAL.[FISCAL_PERIOD]


UNION


SELECT 
CAL.FISCAL_PERIOD, 'JOB_DONE' AS 'Opp Status', count(*) AS 'Opps',

(SELECT count(*) AS 'Windscreen' 
     FROM 
        DET.[LCC_OPP_DETAILS] LCC
     LEFT JOIN 
        [DET_RAW].[ORACLE_DWH].[BELDW_CALENDAR_V2] CAL
     ON 
        LCC.JOB_COMPLETED_DATE = CAL.CALENDAR_DAY
        AND CAL.ETL_IS_DELETED = 0
     WHERE 
        CAL.FISCAL_PERIOD = @FISCAL_PERIOD
        AND JOB_TYPE = 'WINDSCREEN' 
        AND LCC.LAST_ACTIVITY_STATUS_TEXT = 'JOB_CLOSED'
    ) as 'WINDSCREEN',

    (SELECT count(*) AS 'Windscreen' 
     FROM 
        DET.[LCC_OPP_DETAILS] LCC
     LEFT JOIN 
        [DET_RAW].[ORACLE_DWH].[BELDW_CALENDAR_V2] CAL
     ON 
        LCC.JOB_COMPLETED_DATE = CAL.CALENDAR_DAY
        AND CAL.ETL_IS_DELETED = 0
     WHERE 
        CAL.FISCAL_PERIOD = @FISCAL_PERIOD
        AND JOB_TYPE = 'WINDSCREEN'  
        AND LCC.LAST_ACTIVITY_STATUS_TEXT = 'JOB_CLOSED'
        AND RECAL_FLAG = 1
    )  AS 'WINDSCREEN+RECAL'



FROM 
DET.[LCC_OPP_DETAILS] LCC
LEFT JOIN 
[DET_RAW].[ORACLE_DWH].[BELDW_CALENDAR_V2] CAL
ON 
 LCC.JOB_COMPLETED_DATE = CAL.CALENDAR_DAY
AND CAL.ETL_IS_DELETED = 0
WHERE 
CAL.FISCAL_PERIOD = @FISCAL_PERIOD
AND LCC.LAST_ACTIVITY_STATUS_TEXT = 'JOB_CLOSED'
AND LCC.ETL_IS_DELETED = 0


GROUP BY 
CAL.[FISCAL_PERIOD]
ORDER BY 
CAL.FISCAL_PERIOD,  'Opp Status'

I tried adding a group by to the subqueries but then it errors as they are returning multiple values. IS this not possible or does it need to be rewritten in a windows function or CTE ?

Answer 1

Was a little hard to describe. Needed a count of each column but the fact that each of the status values needed to join to the calendar table on a slightly different date made it hard, I struggled to use a CTE or windows function.

In the end I managed to get it running with CASE and UNIONs as per below, interested if there is a more elegant way to get this sorted without hardcoding 'Opp Status' or the boiler plate code repeating code.

    DECLARE @FISCAL_PERIOD VARCHAR(10)

    SET @FISCAL_PERIOD = '2022FP01'

    SELECT 
        CAL.FISCAL_PERIOD, 
        'CONTACTABLE' AS OPP_STATUS,
        COUNT(*) AS 'Total Opps',
        COUNT(CASE WHEN LCC.JOB_CONTACTABLE_CREATED_DATE IS NOT NULL AND JOB_TYPE = 'WINDSCREEN' THEN 1 END) AS 'WINDSCREEN',
        COUNT(CASE WHEN LCC.JOB_CONTACTABLE_CREATED_DATE IS NOT NULL AND JOB_TYPE = 'WINDSCREEN' AND ADAS_FLAG = 1 THEN 1 END) AS 'WINDSCREEN+RCAL'
    FROM 
        DET.[LCC_OPP_DETAILS] LCC
    LEFT JOIN 
        [DET_RAW].[ORACLE_DWH].[BELDW_CALENDAR_V2] CAL
    ON 
         LCC.JOB_CONTACTABLE_CREATED_DATE = CAL.CALENDAR_DAY
    AND CAL.ETL_IS_DELETED = 0
    WHERE 
        CAL.FISCAL_PERIOD >= @FISCAL_PERIOD
        AND LCC.ETL_IS_DELETED = 0
    GROUP BY 
        CAL.[FISCAL_PERIOD]

    UNION 


    SELECT 
        CAL.FISCAL_PERIOD,
        'IDENTIFABLE' AS OPP_STATUS,
        COUNT(*) AS 'Total Opps',
        COUNT(CASE WHEN JOB_TYPE = 'WINDSCREEN' THEN 1 END) AS 'WINDSCREEN',
        COUNT(CASE WHEN JOB_TYPE = 'WINDSCREEN' AND ADAS_FLAG = 1 THEN 1 END) AS 'WINDSCREEN+RCAL'
    FROM 
        DET.[LCC_OPP_DETAILS] LCC
    LEFT JOIN 
        [DET_RAW].[ORACLE_DWH].[BELDW_CALENDAR_V2] CAL
    ON 
        LCC.JOB_OPPORTUNITY_CREATED_DATE = CAL.CALENDAR_DAY
        AND CAL.ETL_IS_DELETED = 0
    WHERE 
        CAL.FISCAL_PERIOD >= @FISCAL_PERIOD
        AND LCC.ETL_IS_DELETED = 0
    GROUP BY 
        CAL.[FISCAL_PERIOD]

    UNION

    SELECT 
        CAL.FISCAL_PERIOD, 
        'APPOINTMENT' AS OPP_STATUS,
        COUNT(*) AS 'Total Opps',
        COUNT(CASE WHEN LCC.JOB_APPOINTMENT_BOOKED_DATE IS NOT NULL AND JOB_TYPE = 'WINDSCREEN' THEN 1 END) AS 'WINDSCREEN',
        COUNT(CASE WHEN LCC.JOB_APPOINTMENT_BOOKED_DATE IS NOT NULL AND JOB_TYPE = 'WINDSCREEN' AND ADAS_FLAG = 1 THEN 1 END) AS 'WINDSCREEN+RCAL'
    FROM 
        DET.[LCC_OPP_DETAILS] LCC
    LEFT JOIN 
        [DET_RAW].[ORACLE_DWH].[BELDW_CALENDAR_V2] CAL
    ON 
         LCC.JOB_APPOINTMENT_BOOKED_DATE = CAL.CALENDAR_DAY
    AND CAL.ETL_IS_DELETED = 0
    WHERE 
        CAL.FISCAL_PERIOD >= @FISCAL_PERIOD
        AND LCC.ETL_IS_DELETED = 0
    GROUP BY 
        CAL.[FISCAL_PERIOD]

    UNION


    SELECT 
        CAL.FISCAL_PERIOD, 
        'JOB_DONE' AS OPP_STATUS,
        COUNT(*) AS 'Total Opps',
        COUNT(CASE WHEN LCC.LAST_ACTIVITY_STATUS_TEXT = 'JOB_CLOSED' AND JOB_TYPE = 'WINDSCREEN' THEN 1 END) AS 'WINDSCREEN',
        COUNT(CASE WHEN LCC.LAST_ACTIVITY_STATUS_TEXT = 'JOB_CLOSED' AND JOB_TYPE = 'WINDSCREEN' AND ADAS_FLAG = 1 THEN 1 END) AS 'WINDSCREEN+RCAL'
    FROM 
        DET.[LCC_OPP_DETAILS] LCC
    LEFT JOIN 
        [DET_RAW].[ORACLE_DWH].[BELDW_CALENDAR_V2] CAL
    ON 
         LCC.JOB_COMPLETED_DATE = CAL.CALENDAR_DAY
    AND CAL.ETL_IS_DELETED = 0
    WHERE 
        CAL.FISCAL_PERIOD >= @FISCAL_PERIOD
        AND LCC.ETL_IS_DELETED = 0
    GROUP BY 
        CAL.[FISCAL_PERIOD]

    ORDER BY FISCAL_PERIOD, OPP_STATUS