Query with Joins needs Rewritten to include records that are not in the Detail Level File

YoYoYo 0
Viewed 34

I have a query that is excluding records where there is no match on the first join. Below is only giving Driver_Trend records that have a match in Driver_Incidents though I need it to include ones that are not found in Driver_Incidents.

SELECT A.DRIVER_NAME AS DRIVER_NAME, A.DRIVER_ID AS DRIVER_ID, C.TRC_TERMINAL AS CSC, A.OBSERVATIONS AS OBSERVATIONS, 
    A.INCIDENTS AS INCIDENTS, B.SPEED_LIMIT AS SPEED_LIMIT, B.SPEED AS SPEED, B.DIFFERENCE AS DIFFERENCE,     
    A.REPORT_DATE AS REPORT_DATE, B.TIME AS TIME,    
  
    CASE WHEN DIFFERENCE >= 6 AND DIFFERENCE <= 10 THEN '1' ELSE '0' END AS SIX_TEN_MPH,
    CASE WHEN DIFFERENCE > 10 AND DIFFERENCE <= 15 THEN '1' ELSE '0' END AS ELEVEN_FIFTEEN_MPH,
    CASE WHEN DIFFERENCE > 15 THEN '1' ELSE '0' END AS SIXTEEN_PLUS_MPH
  
  FROM "PROD"."PUBLIC"."SG_DRIVER_TREND" A
  LEFT JOIN "PROD"."PUBLIC"."SG_DRIVER_INCIDENTS" B
    ON A.DRIVER_ID = B.DRIVER_ID
  LEFT JOIN "PROD"."PUBLIC"."TMW_TRACTORPROFILE" C
    ON B.Vehicle = C.TRC_NUMBER
  WHERE A.DRIVER_ID != ''  
    AND A.REPORT_DATE BETWEEN '2022-07-01' AND '2022-07-31'
    AND B.TIME BETWEEN '2022-07-01' AND '2022-07-31'
       AND SUBSTRING(B.TIME, 0, 10) <= A.REPORT_DATE                                          -- Less than or equal to report date
       AND SUBSTRING(B.TIME, 0, 10) > SUBSTRING(DATEADD(week,-1, A.REPORT_DATE), 0, 10)       -- Greater than 1 week ago

I have tried all different types of joins but still no luck. Could this query be rewritten to get what I need with Subselect or other? Code examples would be appreciated. This is in Snowflake if that matters.

snowflake-cloud-data-platform sql-server sql
1 Answers

Conditions in WHERE clause referencing outer table makes the query to effectively work as INNER JOIN and should be moved to ON clasue instead:

SELECT A.DRIVER_NAME AS DRIVER_NAME, A.DRIVER_ID AS DRIVER_ID, C.TRC_TERMINAL AS CSC, A.OBSERVATIONS AS OBSERVATIONS, 
    A.INCIDENTS AS INCIDENTS, B.SPEED_LIMIT AS SPEED_LIMIT, B.SPEED AS SPEED, B.DIFFERENCE AS DIFFERENCE,     
    A.REPORT_DATE AS REPORT_DATE, B.TIME AS TIME,    
  
    CASE WHEN DIFFERENCE >= 6 AND DIFFERENCE <= 10 THEN '1' ELSE '0' END AS SIX_TEN_MPH,
    CASE WHEN DIFFERENCE > 10 AND DIFFERENCE <= 15 THEN '1' ELSE '0' END AS ELEVEN_FIFTEEN_MPH,
    CASE WHEN DIFFERENCE > 15 THEN '1' ELSE '0' END AS SIXTEEN_PLUS_MPH
  
FROM "PROD"."PUBLIC"."SG_DRIVER_TREND" A
LEFT JOIN "PROD"."PUBLIC"."SG_DRIVER_INCIDENTS" B
  ON A.DRIVER_ID = B.DRIVER_ID
 AND B.TIME BETWEEN '2022-07-01' AND '2022-07-31'
 AND SUBSTRING(B.TIME, 0, 10) <= A.REPORT_DATE                                          -- Less than or equal to report date
 AND SUBSTRING(B.TIME, 0, 10) > SUBSTRING(DATEADD(week,-1, A.REPORT_DATE), 0, 10)     
LEFT JOIN "PROD"."PUBLIC"."TMW_TRACTORPROFILE" C
  ON B.Vehicle = C.TRC_NUMBER
WHERE A.DRIVER_ID != ''  
  AND A.REPORT_DATE BETWEEN '2022-07-01' AND '2022-07-31';
Lukasz Szozda0
Related