My Table structure & data-
CREATE TABLE BRAND_OFFERS
(
ID NUMBER(15,0)
, NAME VARCHAR2(80 CHAR)
, OPEN DATE
, CLOSE DATE
, ENROLLED DATE
);
-- expired offers
insert into BRAND_OFFERS values(1, 'Holiday1', date'2024-03-20', date'2024-03-30', date'2024-03-22');
insert into BRAND_OFFERS values(2, 'Holiday2', date'2024-03-20', date'2024-03-25', null);
insert into BRAND_OFFERS values(3, 'Holiday3', date'2024-04-25', date'2024-04-30', date'2024-04-27');
insert into BRAND_OFFERS values(4, 'Holiday4', date'2024-05-21', date'2024-05-26', date'2024-05-23');
insert into BRAND_OFFERS values(5, 'Holiday5', date'2024-05-22', date'2024-05-25', null);
-- active offers
insert into BRAND_OFFERS values(6, 'Holiday6', date'2024-05-15', date'2024-06-10', date'2024-05-26');
insert into BRAND_OFFERS values(7, 'Holiday7', date'2024-05-20', date'2024-06-10', date'2024-05-21');
insert into BRAND_OFFERS values(8, 'Holiday8', date'2024-05-20', date'2024-06-10', null);
insert into BRAND_OFFERS values(9, 'Holiday9', date'2024-05-20', date'2024-06-10', null);
insert into BRAND_OFFERS values(10, 'Holiday10', date'2024-05-20', date'2024-06-10', date'2024-05-22');
-- future offers
insert into BRAND_OFFERS values(11, 'Holiday11', date'2024-05-30', date'2024-06-05', null);
insert into BRAND_OFFERS values(12, 'Holiday12', date'2024-06-01', date'2024-06-05', null);
insert into BRAND_OFFERS values(13, 'Holiday13', date'2024-06-01', date'2024-06-08', null);
Requirement- Basically, the Festival Offers are divided into below categories:
I need to display the values from BRAND_OFFERS table, Ordered By as per above categories. First Expired, then Active, then Future offers.
Oracle Query to achieve this-
with initial_op as (
select id, name,
OPEN as open_date,
CLOSE as closure_date,
ENROLLED as enroll_date,
case
when CLOSE < sysdate
then 'Offer Expired'
when OPEN > sysdate
then 'Offer Future'
when ENROLLED is not null
then 'Offer Active and Enrolled'
else 'Offer Active but yet to Enroll'
end offer_status,
case
when CLOSE < sysdate
then 1
when ENROLLED is not null
then 2
else 3
end seq
from BRAND_OFFERS ),
x as (select * from initial_op where seq = 1
order by closure_date ),
y as (select * from initial_op where seq = 2
order by enroll_date ),
z as (select * from initial_op where seq = 3
order by open_date)
select * from x
union all
select * from y
union all
select * from z;
This query gives me the desired output. The same query, instead of multiple with & union all, I tried with order by (multiple) case when... but not getting the correct order.
--not working
order by
case when CLOSE < sysdate then CLOSE
when ENROLLED is not null then ENROLLED
else OPEN
end
Can the working query be optimized further?
As this currently stands, you're likely to end up with three passes through the table, ie
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 13 | 2886 | 12 (25)| 00:00:01 |
| 1 | UNION-ALL | | | | | |
| 2 | VIEW | | 5 | 1110 | 4 (25)| 00:00:01 |
| 3 | SORT ORDER BY | | 5 | 1010 | 4 (25)| 00:00:01 |
|* 4 | TABLE ACCESS FULL| BRAND_OFFERS | 5 | 1010 | 3 (0)| 00:00:01 |
| 5 | VIEW | | 3 | 666 | 4 (25)| 00:00:01 |
| 6 | SORT ORDER BY | | 3 | 606 | 4 (25)| 00:00:01 |
|* 7 | TABLE ACCESS FULL| BRAND_OFFERS | 3 | 606 | 3 (0)| 00:00:01 |
| 8 | VIEW | | 5 | 1110 | 4 (25)| 00:00:01 |
| 9 | SORT ORDER BY | | 5 | 1010 | 4 (25)| 00:00:01 |
|* 10 | TABLE ACCESS FULL| BRAND_OFFERS | 5 | 1010 | 3 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Whether not that is a good or bad thing depends highly on those predicates in the CASE (ie, you could fold them into the query if there were appropriate indexes etc). Assuming that is not the case, you can change your ORDER BY to drop this back to one pass on the table
SQL> with initial_op as (
2 select id, name,
3 OPEN as open_date,
4 CLOSE as closure_date,
5 ENROLLED as enroll_date,
6 case
7 when CLOSE < sysdate
8 then 'Offer Expired'
9 when OPEN > sysdate
10 then 'Offer Future'
11 when ENROLLED is not null
12 then 'Offer Active and Enrolled'
13 else 'Offer Active but yet to Enroll'
14 end offer_status,
15 case
16 when CLOSE < sysdate
17 then 1
18 when ENROLLED is not null
19 then 2
20 else 3
21 end seq
22 from BRAND_OFFERS )
23 select *
24 from initial_op
25 order by seq,
26 case
27 when seq = 1 then closure_date
28 when seq = 2 then enroll_date
29 when seq = 2 then open_date
30 end;
ID NAME OPEN_DATE CLOSURE_D ENROLL_DA OFFER_STATUS SEQ
---- -------------------- --------- --------- --------- ------------------------------ ----------
2 Holiday2 20-MAR-24 25-MAR-24 Offer Expired 1
1 Holiday1 20-MAR-24 30-MAR-24 22-MAR-24 Offer Expired 1
3 Holiday3 25-APR-24 30-APR-24 27-APR-24 Offer Expired 1
5 Holiday5 22-MAY-24 25-MAY-24 Offer Expired 1
4 Holiday4 21-MAY-24 26-MAY-24 23-MAY-24 Offer Expired 1
7 Holiday7 20-MAY-24 10-JUN-24 21-MAY-24 Offer Active and Enrolled 2
10 Holiday10 20-MAY-24 10-JUN-24 22-MAY-24 Offer Active and Enrolled 2
6 Holiday6 15-MAY-24 10-JUN-24 26-MAY-24 Offer Active and Enrolled 2
13 Holiday13 01-JUN-24 08-JUN-24 Offer Future 3
8 Holiday8 20-MAY-24 10-JUN-24 Offer Active but yet to Enroll 3
9 Holiday9 20-MAY-24 10-JUN-24 Offer Active but yet to Enroll 3
11 Holiday11 30-MAY-24 05-JUN-24 Offer Future 3
12 Holiday12 01-JUN-24 05-JUN-24 Offer Future 3