Oracle 23c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

select * from V$VERSION;


BANNER
BANNER_FULL
BANNER_LEGACY
CON_ID


Oracle Database 23c Free, Release 23.0.0.0.0 - Developer-Release
Oracle Database 23c Free, Release 23.0.0.0.0 - Developer-Release
Version 23.2.0.0.0
Oracle Database 23c Free, Release 23.0.0.0.0 - Developer-Release
0


CREATE TABLE test (
    id NUMBER PRIMARY KEY,
    reg_con NUMBER,
    tagg_reading NUMBER,
    read_date DATE
);

INSERT ALL
    INTO test (id, reg_con, tagg_reading, read_date) VALUES (1, 1, 10, TO_DATE('2024-06-20', 'YYYY-MM-DD'))
    INTO test (id, reg_con, tagg_reading, read_date) VALUES (2, 4, 20, TO_DATE('2024-04-10', 'YYYY-MM-DD'))
    INTO test (id, reg_con, tagg_reading, read_date) VALUES (3, 4, 30, TO_DATE('2024-08-15', 'YYYY-MM-DD'))
    INTO test (id, reg_con, tagg_reading, read_date) VALUES (4, 4, 40, TO_DATE('2024-12-25', 'YYYY-MM-DD'))
    INTO test (id, reg_con, tagg_reading, read_date) VALUES (5, 3, 50, TO_DATE('2024-10-05', 'YYYY-MM-DD'))
SELECT 1 FROM DUAL;

5 rows affected

select * from test;


ID
REG_CON
TAGG_READING
READ_DATE


1
1
10
20-JUN-24

2
4
20
10-APR-24

3
4
30
15-AUG-24

4
4
40
25-DEC-24

5
3
50
05-OCT-24


SELECT id, reg_con, tagg_reading, read_date,
CASE
    WHEN reg_con * tagg_reading < 60 
         AND TO_CHAR(read_date, 'MM-DD') BETWEEN '06-15' AND '10-15' THEN 'Y'
    WHEN reg_con * tagg_reading < 90 
         AND (TO_CHAR(read_date, 'MM-DD') BETWEEN '10-16' AND '12-31' OR TO_CHAR(read_date, 'MM-DD') BETWEEN '01-01' AND '06-14') THEN 'Y'
    ELSE 'N'
END AS Keep_Flag
FROM test;



ID
REG_CON
TAGG_READING
READ_DATE
KEEP_FLAG


1
1
10
20-JUN-24
Y

2
4
20
10-APR-24
Y

3
4
30
15-AUG-24
N

4
4
40
25-DEC-24
N

5
3
50
05-OCT-24
N


SELECT id, reg_con, tagg_reading, read_date,
  to_char(read_date, 'MM-DD') to_char_read_date,
CASE
   WHEN reg_con * tagg_reading <60 and to_char(read_date, 'MM-DD') BETWEEN '06-15' AND '10-15' then 'Y'
   WHEN reg_con * tagg_reading <90 and to_char(read_date, 'MM-DD') BETWEEN '10-16' AND '06-14' then 'Y'
ELSE 'N'
END as Keep_Flag
FROM test;


ID
REG_CON
TAGG_READING
READ_DATE
TO_CHAR_READ_DATE
KEEP_FLAG


1
1
10
20-JUN-24
06-20
Y

2
4
20
10-APR-24
04-10
N

3
4
30
15-AUG-24
08-15
N

4
4
40
25-DEC-24
12-25
N

5
3
50
05-OCT-24
10-05
N

ID	REG_CON	TAGG_READING	READ_DATE
1	1	10	20-JUN-24
2	4	20	10-APR-24
3	4	30	15-AUG-24
4	4	40	25-DEC-24
5	3	50	05-OCT-24

ID	REG_CON	TAGG_READING	READ_DATE	TO_CHAR_READ_DATE	KEEP_FLAG
1	1	10	20-JUN-24	06-20	Y
2	4	20	10-APR-24	04-10	N
3	4	30	15-AUG-24	08-15	N
4	4	40	25-DEC-24	12-25	N
5	3	50	05-OCT-24	10-05	N