Fill data gaps - UNION, PARTITION BY, or JOIN?
- by Dave Jarvis
Problem
There are data gaps that need to be filled. Would like to avoid UNION or PARTITION BY if possible.
Query Statement
The select statement reads as follows:
SELECT
count( r.incident_id ) AS incident_tally,
r.severity_cd,
r.incident_typ_cd
FROM
report_vw r
GROUP BY
r.severity_cd, r.incident_typ_cd
ORDER BY
r.severity_cd,
r.incident_typ_cd
Data Sources
The severity codes and incident type codes are from:
severity_vw
incident_type_vw
The columns are:
incident_tally
severity_cd
incident_typ_cd
Actual Result Data
36 0 ENVIRONMENT
1 1 DISASTER
27 1 ENVIRONMENT
4 2 SAFETY
1 3 SAFETY
Required Result Data
36 0 ENVIRONMENT
0 0 DISASTER
0 0 SAFETY
27 1 ENVIRONMENT
0 1 DISASTER
0 1 SAFETY
0 2 ENVIRONMENT
0 2 DISASTER
4 2 SAFETY
0 3 ENVIRONMENT
0 3 DISASTER
1 3 SAFETY
Question
How would you use UNION, PARTITION BY, or LEFT JOIN to fill in the zero counts?
