add batch remove batch split batch comment selection show hidden batches hide batch highlight batch
db<>fiddle
donate feedback about
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table KPI (COMP NUMBER(2), PERIOD_START timestamp, EC NUMBER(8,2), mon NUMBER(8,2) );
insert into KPI
SELECT 2, current_timestamp - interval '2' day, 3.4, 500.6 from dual
UNION
SELECT 2, current_timestamp - interval '2' day , 100, 500.6 from dual
2 rows affected
SELECT COMP, DOW, Period_date, pct_ec, 'YES' as alert
FROM
(SELECT
COMP,
TO_CHAR(PERIOD_START, 'DAY') DOW,
trunc(PERIOD_START) Period_Date,
round((sum(EC) / sum(mon)) * 100, 2) Pct_EC
FROM
KPI
WHERE
trunc(period_start) >= trunc(sysdate -7)
and trunc(period_start) < trunc(sysdate)
GROUP BY
trunc(period_start),
TO_CHAR(PERIOD_START, 'DAY'),
COMP ) temp
WHERE
COMP in (2,3,4)
AND pct_ec >= decode( COMP, 2, 2.96, 3, 1.62, 4, 2.06, 0)
ORDER BY
period_date desc,
COMP

COMP DOW PERIOD_DATE PCT_EC ALERT
2 WEDNESDAY 07-APR-21 10.33 YES
SELECT
COMP,
TO_CHAR(PERIOD_START, 'DAY') DOW,
trunc(PERIOD_START) Period_Date,
round((sum(EC) / sum(mon)) * 100, 2) Pct_EC,
CASE WHEN COMP = 2
AND round((sum(EC) / sum(mon)) * 100, 2) >= 2.96 THEN 'YES' WHEN COMP = 3
AND round((sum(EC) / sum(mon)) * 100, 2) >= 1.62 THEN 'YES' WHEN COMP = 4
AND round((sum(EC) / sum(mon)) * 100, 2) >= 2.06 THEN 'YES' ELSE 'NO' END as ALERT
FROM
KPI
WHERE
trunc(period_start) >= trunc(sysdate -7)
and trunc(period_start) < trunc(sysdate)
GROUP BY
trunc(period_start),
TO_CHAR(PERIOD_START, 'DAY'),
COMP,
trunc(PERIOD_START)
ORDER BY
trunc(period_start) desc,
COMP,
trunc(PERIOD_START)
COMP DOW PERIOD_DATE PCT_EC ALERT
2 WEDNESDAY 07-APR-21 10.33 YES
SELECT
COMP,
TO_CHAR(PERIOD_START, 'DAY') DOW,
trunc(PERIOD_START) Period_Date,
round((sum(EC) / sum(mon)) * 100, 2) Pct_EC,
CASE WHEN COMP in (2,3,4) AND round((sum(EC) / sum(mon)) * 100, 2) >= decode( COMP, 2, 2.96, 3, 1.62, 4, 2.06, 0)
THEN 'YES' ELSE 'NO' END as ALERT
FROM
KPI
WHERE
trunc(period_start) >= trunc(sysdate -7)
and trunc(period_start) < trunc(sysdate)
GROUP BY
trunc(period_start),
TO_CHAR(PERIOD_START, 'DAY'),
COMP,
trunc(PERIOD_START)
HAVING
CASE WHEN COMP in (2,3,4) AND round((sum(EC) / sum(mon)) * 100, 2) >= decode( COMP, 2, 2.96, 3, 1.62, 4, 2.06, 0)
THEN 'YES' ELSE 'NO' END = 'YES'
ORDER BY
trunc(period_start) desc,
COMP,
trunc(PERIOD_START)

COMP DOW PERIOD_DATE PCT_EC ALERT
2 WEDNESDAY 07-APR-21 10.33 YES