Fill data gaps without UNION
- by Dave Jarvis
Problem
There are data gaps that need to be filled, possibly using PARTITION BY.
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
Code Tables
The severity codes and incident type codes are from:
severity_vw
incident_type_vw
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
Any ideas how to use PARTITION BY (or JOINs) to fill in the zero counts?
