I am very new to the database and queries. I am not sure if this is very basic. Please help me to find a solution.
I have two tables – subscription and customer with primary keys as subscription_id and customer_id respectively. the customer_id is foreign key pointing to customer table customer_id column. Below is a sample of table and its data:
subscription
subscription_id customer_id subscription_start_date subscription_end_date subscription_status 1001 1 1-JAN-2020 31-DEC-2020 PENDING 1002 2 1-JUN-2020 31-MAY-2021 PENDING 1003 3 4-JUL-2020 3-JUL-2021 ARCHIVED 1004 4 2-APR-2020 1-APR-2021 PENDING 1005 5 3-APR-2020 2-APR-2021 ARCHIVED 1006 6 21-JAN-2020 20-JAN-2021 PENDING 1007 7 22-JAN-2020 21-JAN-2021 PENDING
Customer
customer_id membership_type membership_start_date membership_status 1 GOLD 1-JAN-2020 ACTIVE 2 PLATINUM 1-JUN-2020 ACTIVE 3 PLATINUM 5-JUL-2020 PROCESSING 4 GOLD 2-APR-2020 PROCESSING 5 GOLD 3-APR-2020 ACTIVE 6 GOLD 21-JAN-2020 PROCESSING 7 GOLD 22-JAN-2019 EXPIRED
I want to query all the subscriptions which satisfies following two criteria
- subscription_satus is pending and membership_type is GOLD or PLATINUM and membership_status is ACTIVE.(1001,1002)
- subscription_status is archived only when customer membership_type is PLATINUM and the membership_start_date is between subscription_start_date and subscription_end_date and membership_status is PROCESING(1003)
So we should get 1001, 1002 records under criteria 1. and 1003 under criteria 2
Advertisement
Answer
Query should look like this; lines #1 – 21 represent sample data, you don’t type that. Query you do need begins at line #22 and contains two parts of the WHERE clause: the first satisfies the first condition, while the second satisfies the second condition.
Note that sample MEMBERSHIP_START_DATE for curstomer 3 is 02-JUL-2020 which is not between subscription start (4-JUL-2020) and end (3-JUL-2021) dates, so – 1003 isn’t part of the result set. Modify membership start date to e.g. 22-JUL-2020 and it will be.
SQL> with 2 subscription (subscription_id, customer_id, subscription_start_date, 3 subscription_end_date, subscription_status) as 4 (select 1001, 1, date '2020-01-01', date '2020-12-31', 'PENDING' from dual union all 5 select 1002, 2, date '2020-06-01', date '2021-05-31', 'PENDING' from dual union all 6 select 1003, 3, date '2020-07-04', date '2021-07-03', 'ARCHIVED' from dual union all 7 select 1004, 4, date '2020-04-02', date '2021-04-01', 'PENDING' from dual union all 8 select 1005, 5, date '2020-04-03', date '2021-04-02', 'ARCHIVED' from dual union all 9 select 1006, 6, date '2020-01-21', date '2021-01-20', 'PENDING' from dual union all 10 select 1007, 7, date '2020-01-22', date '2021-01-21', 'PENDING' from dual 11 ), 12 customer (customer_id, membership_type, membership_start_date, 13 membership_status) as 14 (select 1, 'GOLD' , date '2020-01-01', 'ACTIVE' from dual union all 15 select 2, 'PLATINUM', date '2020-06-01', 'ACTIVE' from dual union all 16 select 3, 'PLATINUM', date '2020-07-02', 'PROCESSING' from dual union all 17 select 4, 'GOLD' , date '2020-04-02', 'PROCESSING' from dual union all 18 select 5, 'GOLD' , date '2020-04-03', 'ACTIVE' from dual union all 19 select 6, 'GOLD' , date '2020-01-21', 'PROCESSING' from dual union all 20 select 7, 'GOLD' , date '2019-01-22', 'EXPIRED' from dual 21 )
22 select s.subscription_id
23 from subscription s join customer c on c.customer_id = s.customer_id
24 where ( s.subscription_status = 'PENDING'
25 and c.membership_type in ('GOLD', 'PLATINUM')
26 and c.membership_status = 'ACTIVE'
27 )
28 or
29 ( s.subscription_status = 'ARCHIVED'
30 and c.membership_type = 'PLATINUM'
31 and c.membership_start_date between s.subscription_start_date
32 and s.subscription_end_date
33 and c.membership_status = 'PROCESSING'
34 )
35 /
SUBSCRIPTION_ID
---------------
1001
1002
SQL>