'CTE works, but not getting all the data after group by
This is in snowflake. My goal was to eventually union a table that has data only by months. After making a CTE it works, but the data does not total up to match the original query.
My original query has data only by cases and not months, so the union does not work and thus I needed to make a CTE.
Original Query:
SELECT DISTINCT *,
CASE WHEN ASSET_READY_DATE >= ASSET_CHECKED_IN_DATE THEN
TIMEDIFF('minute', ASSET_CHECKED_IN_DATE, ASSET_READY_DATE)/1440 END
AS DT,
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 ELSE 0 END QC,
CASE WHEN ASSET_READY_DATE IS NULL THEN 'Open' ELSE 'Close' END CS
FROM
(
SELECT DISTINCT
C.CASE_ID,
M.DIVISION,
C.DEALER_NAME,
C.DEALER_ID,
M.OWNERGROUPNAME,
M.DEALERCODE,
M.PHYSICALSTATE,
M.COUNTRY,
X.DPMNAM AS DPM_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_TIMESTAMP(C.NEW_CASE_CREATED_AT) AS NEW_CASE_CREATED_AT,
TO_TIMESTAMP(C.CLOSED_AT) AS CASE_CLOSED_DATE,
CASE
WHEN C.PROGRAM_TYPE ILIKE '%mobile%' THEN 'MOBILE'
WHEN C.PROGRAM_TYPE ILIKE '%service%' THEN 'SERVICE'
WHEN C.PROGRAM_TYPE ILIKE '%other%' THEN 'OTHER'
WHEN C.PROGRAM_TYPE ILIKE '%credit%' THEN 'CREDIT'
WHEN C.PROGRAM_TYPE ILIKE '%body%' THEN 'BODY SHOP'
WHEN C.PROGRAM_TYPE ILIKE '%internal%' THEN 'INTERNAL'
WHEN C.CLOSED_AT < '2020-09-01' THEN 'SERVICE'
ELSE 'UNCATEGORIZED' END AS PROGRAM_TYPE
FROM T1 AS C
INNER JOIN T2 AS M ON C.ID1 = M.ID2
LEFT JOIN T3 AS X on M.ID2 = X.ID3
WHERE TO_TIMESTAMP_NTZ(C.Closed_At) >= '2020-01-01 00:00:00.000'
AND C.R_NUM IS NOT NULL
AND (M.DEALERCATEGORY ILIKE 'DEAL' OR M.DEALERCATEGORY ILIKE 'RC')
AND M.DEALERUSAGE ILIKE 'PRO'
AND OWNERGROUPCODE != '22'
AND M.DEALERCODE != '11'
)
UNION ALL
SELECT DISTINCT
CC AS CC,
CC AS CCDT,
CASE WHEN CC WITH DT ILIKE 0 THEN 0 ELSE CCDTC END SDT,
R.DIVISION,
DEALER_NAME,
OWNER_GROUP,
DEALER_CODE,
R.STATE,
X.CTRY,
X.DPMNAM AS DPM_NAME,
CLOSED_AT AS CASE_CLOSED_DATE
FROM T4 AS R
LEFT JOIN T3 AS X ON R.ID4 = X.ID3
WHERE TO_DATE(CLOSED_AT) >= '2020-01-01'
My CTE which works, but case totals do not match original query
WITH CTE_A_ALPHA AS (
SELECT
C.CASE_ID,
M.DIVISION,
C.DEALER_NAME,
M.OWNERGROUPNAME,
M.DEALERCODE,
M.PHYSICALSTATE,
M.COUNTRY,
X.DPMNAM AS DPM_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_TIMESTAMP(C.NEW_CASE_CREATED_AT) AS NEW_CASE_CREATED_AT,
TO_TIMESTAMP(C.CLOSED_AT) AS CASE_CLOSED_DATE,
CASE
WHEN PROGRAM_TYPE ILIKE '%mobile%' THEN 'MOBILE'
WHEN PROGRAM_TYPE ILIKE '%service%' THEN 'SERVICE'
WHEN PROGRAM_TYPE ILIKE '%other%' THEN 'OTHER'
WHEN PROGRAM_TYPE ILIKE '%credit%' THEN 'CREDIT'
WHEN PROGRAM_TYPE ILIKE '%body%' THEN 'BODY SHOP'
WHEN PROGRAM_TYPE ILIKE '%internal%' THEN 'INTERNAL'
WHEN CLOSED_AT < '2020-09-01' THEN 'SERVICE'
ELSE 'UNCATEGORIZED' END AS PROGRAM_TYPE,
CASE WHEN ASSET_READY_DATE IS NULL THEN 'Open' ELSE 'Close' END CS
FROM T1 AS C
INNER JOIN T2 AS M ON C.ID1 = M.ID2
LEFT JOIN T3 AS X on M.ID2 = X.ID3
WHERE TO_TIMESTAMP_NTZ(C.Closed_At) >= '2020-01-01 00:00:00.000'
AND C.R_NUM IS NOT NULL
AND (M.DEALERCATEGORY ILIKE 'DEAL' OR M.DEALERCATEGORY ILIKE 'RC')
AND M.DEALERUSAGE ILIKE 'PRO'
AND OWNERGROUPCODE != '22'
AND M.DEALERCODE != '11'
), CTE_A AS (
SELECT
CASE_ID,
DIVISION,
DEALER_NAME,
OWNERGROUPNAME,
DEALERCODE,
PHYSICALSTATE,
COUNTRY,
DPM_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_ALPHA
), CTE_B AS (
SELECT *,
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 CTE_A
)
SELECT
COUNT(CASE_ID) AS CC,
SUM(CASE WHEN TIMEDIFF_SEC > 60 AND TIMEDIFF_MIN < 259200 THEN 1 ELSE 0 END) AS CCWDT,
SUM(CASE WHEN ASSET_READY_DATE >= ASSET_CHECKED_IN_DATE THEN TIMEDIFF_MIN/1440 END) AS SDT,
DIVISION,
DEALER_NAME,
OWNERGROUPNAME,
DEALERCODE,
PHYSICALSTATE,
COUNTRY,
DPM_NAME,
TRUNC((CASE_CLOSED_DATE),'Month') AS CASE_CLOSED_MONTH
FROM CTE_B
GROUP BY
DIVISION,
DEALER_NAME,
OWNERGROUPNAME,
DEALERCODE,
PHYSICALSTATE,
COUNTRY,
DPM_NAME,
CASE_CLOSED_MONTH
UNION ALL
SELECT DISTINCT
CC AS CC,
CC AS CCDT,
CASE WHEN CC WITH DT ILIKE 0 THEN 0 ELSE CCDTC END SDT,
R.DIVISION,
DEALER_NAME,
OWNER_GROUP,
DEALER_CODE,
R.STATE,
X.CTRY,
X.DPMNAM AS DPM_NAME,
R.CLOSED_AT AS CD
FROM T4 AS R
LEFT JOIN T3 AS X ON R.ID4 = X.ID3
WHERE TO_DATE(R.CLOSED_AT) >= '2020-01-01'
Please troubleshoot I have been stuck on this for quite some time. Thanks!
Solution 1:[1]
Maybe it's becuase you use DISTINCT in the original query but didn't use it on the next query and the result of query not equal with the orginal
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|---|
| Solution 1 | Pooya |