SQL improving
Hi,
Could some one help improve below query. DB -11.2.0.3
SELECT ap.agency_id,
ap.vac_ref,
kc.kpi_id,
SUM(CASE
WHEN (( ( SYSDATE - ap.application_status_date ) > ( CASE
WHEN af.type_id = 1 THEN Nvl(
ak.custom_orange_days_medical
+ ak.custom_green_days_medical,
kc.default_orange_days_medical
+ kc.default_green_days_medical
)
ELSE Nvl(ak.custom_orange_days
+ ak.custom_green_days, kc.default_orange_days
+ kc.default_green_days
)
END ) )) THEN 1
ELSE 0
END) AS kpi_overdue_count
FROM application ap
inner join kpi_criteria kc
ON ap.application_status_id = kc.application_status_id
left outer
Could some one help improve below query. DB -11.2.0.3
SELECT ap.agency_id,
ap.vac_ref,
kc.kpi_id,
SUM(CASE
WHEN (( ( SYSDATE - ap.application_status_date ) > ( CASE
WHEN af.type_id = 1 THEN Nvl(
ak.custom_orange_days_medical
+ ak.custom_green_days_medical,
kc.default_orange_days_medical
+ kc.default_green_days_medical
)
ELSE Nvl(ak.custom_orange_days
+ ak.custom_green_days, kc.default_orange_days
+ kc.default_green_days
)
END ) )) THEN 1
ELSE 0
END) AS kpi_overdue_count
FROM application ap
inner join kpi_criteria kc
ON ap.application_status_id = kc.application_status_id
left outer
1