2) county.
The problem is if i include the the chart on group header district then it displays me the data for all counties.
How do i create a comparison chart like completed inspections to Total Inspections and grouped by district and county.
SELECT (CASE WHEN fa.activity_closed_date between to_date(
'{?Pm-@beginDate}', 'MM/dd/yyyy')
AND to_date(
'{?Pm-@endDate}', 'MM/dd/yyyy')
AND fa.activity_result_id IS NOT NULL
AND fa.activity_def_id = 10
AND fa.activity_status_id = 3
AND fa.primary_value like '%Annual Site Inspection%'
THEN 1 ELSE 0 END) AS completed,
(CASE WHEN fa.activity_result_id NOT IN (2, 5)
AND fa.activity_closed_date between to_date(
'{?Pm-@beginDate} ', 'MM/dd/yyyy')
AND to_date(
'{?Pm-@endDate}', 'MM/dd/yyyy')
AND fa.activity_result_id IS NOT NULL
AND fa.activity_def_id = 10
AND fa.activity_status_id = 3
AND fa.primary_value like '%Annual Site Inspection%'
THEN 1 ELSE 0 END) AS inCompliance,
(CASE WHEN fa.activity_result_id IN (2, 5)
AND fa.activity_closed_date between to_date('
{?Pm-@beginDate} ', 'MM/dd/yyyy')
AND to_date('
{?Pm-@endDate}', 'MM/dd/yyyy')
AND fa.activity_result_id IS NOT NULL
AND fa.activity_def_id = 10
AND fa.activity_status_id = 3
AND fa.primary_value like '%Annual Site Inspection%'
THEN 1 ELSE 0 END) AS outOfCompliance,
(SELECT COUNT(*) FROM stcm_snap.facilities f
WHERE f.fsc2_fac_stat_id LIKE 'OPEN%'
AND fa.district_id = f.oc2_office_id
) AS facilityCount,
(SELECT COUNT(*) FROM stcm_snap.facilities f
WHERE f.fsc2_fac_stat_id LIKE 'OPEN%'
AND f.cc_county_id = fa.county_id) AS facilityCount1,
cc.county_id AS countyId, cc.name AS countyName,fa.district_id,fa.district_name
FROM stcm_snap.mv_facility_activities fa, bis_lib_snap.county_codes cc,
stcm_snap.county_district_codes cdc
WHERE cc.county_id = cdc.cc_county_id
AND fa.county_id(+) = cc.county_id
AND cc.county_id NOT IN (0, 99)