Snowflake Aggregate in Case Statement

Question

SELECT  DISTINCT
    COUNT(CASE_ID) AS Case_Count,
    COUNT(CASE WHEN TIMEDIFF('second', ASSET_CHECKED_IN_DATE, ASSET_READY_DATE) > 60 
        AND TIMEDIFF('minute', ASSET_CHECKED_IN_DATE, ASSET_READY_DATE) < 259200
        THEN 1 END),0) AS CLOSED_CASES_WITH_DWELL_TIME,
    SUM(CASE WHEN ASSET_READY_DATE >= ASSET_CHECKED_IN_DATE THEN
        TIMEDIFF('minute', ASSET_CHECKED_IN_DATE, ASSET_READY_DATE)/1440 END) AS SUM_DWELL_TIME,
    DIVISION,
    DEALER_NAME,
    OWNERGROUPNAME,        
    DEALERCODE, 
    PHYSICALSTATE,
    COUNTRY,
    Name,
    TRUNC((CASE_CLOSED_DATE),'Month') AS CASE_CLOSED_MONTH,
    //CASE_CLOSED_MONTH,
    COUNT(CASE WHEN ASSET_READY_DATE IS NOT NULL THEN 'Close' END) CASE_STATUS
FROM
(
    SELECT  DISTINCT
        C.CASE_ID,
        M.DIVISION,
        C.DEALER_NAME,
        M.OWNERGROUPNAME,
        M.DEALERCODE,
        M.PHYSICALSTATE,
        M.COUNTRY,
        X.NAME

        CASE 
            WHEN ((TO_TIMESTAMP(C.ASSET_CHECKED_IN_AT) IS NOT NULL AND TO_TIMESTAMP(C.STATUS_CHANGED_TO_CHECKED_IN_AT) IS NOT NULL)) THEN 
                LEAST(TO_TIMESTAMP(C.Asset_checked_in_at),TO_TIMESTAMP(C.STATUS_CHANGED_TO_CHECKED_IN_AT))
            WHEN ((TO_TIMESTAMP(C.ASSET_CHECKED_IN_AT) IS NULL AND TO_TIMESTAMP(C.STATUS_CHANGED_TO_CHECKED_IN_AT) IS NOT NULL)) THEN
                TO_TIMESTAMP(C.STATUS_CHANGED_TO_CHECKED_IN_AT)
            WHEN ((TO_TIMESTAMP(C.ASSET_CHECKED_IN_AT) IS NOT NULL AND TO_TIMESTAMP(C.STATUS_CHANGED_TO_CHECKED_IN_AT) IS NULL)) THEN
                TO_TIMESTAMP(C.ASSET_CHECKED_IN_AT)
        END AS ASSET_CHECKED_IN_DATE,

        TO_TIMESTAMP(C.STATUS_CHANGED_TO_COMPLETE_HERE_AT) AS ASSET_READY_DATE,
        TO_DATE(C.NEW_CASE_CREATED_AT) AS NEW_CASE_CREATED_AT,
        TO_DATE(C.CLOSED_AT) AS CASE_CLOSED_DATE

    FROM TABLE_C C
    INNER JOIN TABLE_M" M ON C.IDID = M.ID
    FULL JOIN  TABLE_X as X on M.CODE = X.CODE 
)

GROUP BY
        CASE_CLOSED_MONTH, 
        OWNERGROUPNAME,
        DEALERCODE,
        DIVISION,
        PHYSICALSTATE,
        COUNTRY,
        DPM_NAME,
        ASSET_READY_DATE,
        ASSET_CHECKED_IN_DATE,
        DEALER_NAME

UNION

SELECT DISTINCT
    CLOSED_CASES_COUNT AS Case_Count,
    CASE WHEN CLOSED_CASES_WITH_DWELL_COUNT ILIKE 0 THEN 0 ELSE 1 END Closed_Cases_With_Dwell,
    COALESCE(ROUND(SUM_DWELL_DAYS/NULLIF(CLOSED_CASES_COUNT,0),2),0) Dwell_Time,
    R.DIVISION,
    DEALER_NAME,
    OWNER_GROUP,
    DEALER_CODE,
    R.STATE,
    COUNTRY,
    X.NAME,
    CLOSED_AT AS Case_Closed_Date,
    NULL AS CASE_STATUS

My goal is to roll up the columns I have in aggregates by month. Count(Case_ID) works and seems to show the cases by month, but I've tried using SUM and Count for the Case Statements and while the query runs the results do not seem to roll up by month.

Results: Query Results from running a top 20

Answer 1

So I have thrown away the second block of code after the UNION because it seems invalid.

but you main SQL can be rewritten as using a couple of CTE's like :

WITH cte_a_clea AS (
    SELECT 
        c.case_id,
        m.division,
        c.dealer_name,
        m.ownergroupname,
        m.dealercode,
        m.physicalstate,
        m.country,
        x.name,
        TO_TIMESTAMP(c.asset_checked_in_at) AS asset_checked_in_at_ts
        TO_TIMESTAMP(c.status_changed_to_checked_in_at) as status_changed_to_checked_in_at_ts
        TO_TIMESTAMP(c.status_changed_to_complete_here_at) AS asset_ready_date,
        TO_DATE(c.new_case_created_at) AS new_case_created_at,
        TO_DATE(c.closed_at) AS case_closed_date

    FROM table_c AS c
    JOIN table_m AS m
        ON c.idid = m.id
    FULL JOIN table_x AS x 
        ON M.CODE = X.CODE 
), cte_a AS (
    SELECT DISTINCT
        case_id,
        division,
        dealer_name,
        ownergroupname,
        dealercode,
        physicalstate,
        country,
        name,
    
        CASE 
            WHEN asset_checked_in_at_ts IS NOT NULL AND status_changed_to_checked_in_at_ts IS NOT NULL THEN 
                LEAST(asset_checked_in_at_ts, status_changed_to_checked_in_at_ts)
            WHEN asset_checked_in_at_ts IS NULL AND status_changed_to_checked_in_at_ts IS NOT NULL THEN
                status_changed_to_checked_in_at_ts
            WHEN asset_checked_in_at_ts IS NOT NULL AND status_changed_to_checked_in_at_ts IS NULL THEN
                asset_checked_in_at_ts
        END AS asset_checked_in_date,

        asset_ready_date,
        new_case_created_at,
        case_closed_date
    FROM cte_a_clea
)

SELECT DISTINCT
    COUNT(CASE_ID) AS Case_Count,
    COUNT(CASE WHEN TIMEDIFF('second', ASSET_CHECKED_IN_DATE, ASSET_READY_DATE) > 60 
        AND TIMEDIFF('minute', ASSET_CHECKED_IN_DATE, ASSET_READY_DATE) < 259200
        THEN 1 END),0) AS CLOSED_CASES_WITH_DWELL_TIME,
    SUM(CASE WHEN ASSET_READY_DATE >= ASSET_CHECKED_IN_DATE THEN
        TIMEDIFF('minute', ASSET_CHECKED_IN_DATE, ASSET_READY_DATE)/1440 END) AS SUM_DWELL_TIME,
    DIVISION,
    DEALER_NAME,
    OWNERGROUPNAME,        
    DEALERCODE, 
    PHYSICALSTATE,
    COUNTRY,
    Name,
    TRUNC((CASE_CLOSED_DATE),'Month') AS CASE_CLOSED_MONTH,
    //CASE_CLOSED_MONTH,
    COUNT(CASE WHEN ASSET_READY_DATE IS NOT NULL THEN 'Close' END) CASE_STATUS
FROM cte_a

GROUP BY
        CASE_CLOSED_MONTH, 
        OWNERGROUPNAME,
        DEALERCODE,
        DIVISION,
        PHYSICALSTATE,
        COUNTRY,
        DPM_NAME,
        ASSET_READY_DATE,
        ASSET_CHECKED_IN_DATE,
        DEALER_NAME

I moved all the to_timestamp stuff into the first CTE so that the case statement producing asset_checked_in_date is more clean to read.

we can now see you are doing a DISTINCT and a GROUP BY one of which is not needed, and given you have some usages of COUNT() it would seem to be the DISTINCT.

Anyways, your GROUP BY clauses should relate to the output columns not the input columns, thus when I (add another CTE to do the timediffs) and swap to a group by via the number columns, I suspect you will get the results you are looking for:

I also compress it all up, as in Snowflake you can refer to prior output columns.

WITH cte_a AS (
    SELECT DISTINCT
        c.case_id,
        m.division,
        c.dealer_name,
        m.ownergroupname,
        m.dealercode,
        m.physicalstate,
        m.country,
        x.name,
        TO_TIMESTAMP(c.asset_checked_in_at) AS asset_checked_in_at_ts
        TO_TIMESTAMP(c.status_changed_to_checked_in_at) as status_changed_to_checked_in_at_ts
        TO_TIMESTAMP(c.status_changed_to_complete_here_at) AS asset_ready_date,
        TO_DATE(c.new_case_created_at) AS new_case_created_at,
        TO_DATE(c.closed_at) AS case_closed_date,
    
        CASE 
            WHEN asset_checked_in_at_ts IS NOT NULL AND status_changed_to_checked_in_at_ts IS NOT NULL THEN 
                LEAST(asset_checked_in_at_ts, status_changed_to_checked_in_at_ts)
            WHEN asset_checked_in_at_ts IS NULL AND status_changed_to_checked_in_at_ts IS NOT NULL THEN
                status_changed_to_checked_in_at_ts
            WHEN asset_checked_in_at_ts IS NOT NULL AND status_changed_to_checked_in_at_ts IS NULL THEN
                asset_checked_in_at_ts
        END AS asset_checked_in_date,
        
        TIMEDIFF('second', asset_checked_in_date, asset_ready_date) AS timediff_sec,
        TIMEDIFF('minute', asset_checked_in_date, asset_ready_date) AS timediff_min

    FROM table_c AS c
    JOIN table_m AS m
        ON c.idid = m.id
    FULL JOIN table_x AS x 
        ON M.CODE = X.CODE 
        
)

SELECT 
    COUNT(case_id) AS case_count,
    COUNT(CASE WHEN timediff_sec > 60 AND timediff_min < 259200 THEN 1 END) AS closed_cases_with_dwell_time,
    SUM(CASE WHEN asset_ready_date >= asset_checked_in_date THEN
        timediff_min/1440 END) AS sum_dwell_time,
    division,
    dealer_name,
    ownergroupname,        
    dealercode, 
    physicalstate,
    country,
    name,
    TRUNC((case_closed_date),'Month') AS case_closed_month,
    COUNT(CASE WHEN asset_ready_date IS NOT NULL THEN 'Close' END) AS case_status
FROM cte_a

GROUP BY
    4, --division,
    5, --dealer_name,
    6, --ownergroupname,        
    7, --dealercode, 
    8, --physicalstate,
    9, --country,
    10, --name,
    11, --case_closed_month