Hello Experts,
Oracle DB version : Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64 bit Production
Table Tracking
CREATE TABLE tracking (awb number(10) primary_key,dispatch_date date);
Sample Data
AWB DISPATCH_DATE
1 20-JUN-2017
2 21-JUL-2017
3 31-JUL-2017
4 21-AUG-2017
5 21-APR-2020
6 21-MAY-2020
Table Tracking_item
CREATE TABLE tracking_item (item number(8),awb number(10));
Sample Data
IITEM AWB
1001 1
1001 4
1002 4
1002 6
1003 2
1004 5
Table Items
CREATE TABLE items (item number(8) primary_key, item_desc varchar2(100));
Sample Data
ITEM ITEM_DESC
1001 Shirts
1002 Socks
1003 Trousers
1004 Belts
1005 T-Shirts
1006 Handkerchief
Requirement:
Pick items from items table for
If there is no tracking information present in the tracking table.
If tracking information is available then the dispatch_date (of all AWB's for that item) must be greater than 12 months. Even if one AWB is less than 12 months then dont pick it.
Desired Output
ITEM
1001
1003
1005
1006
My effort
select track_item.item
from (
select ti.item item,
max(tk.dispatch_date) dis_date
from tracking tk,
tracking_item ti
where tk.awb = ti.awb
group by ti.item
) track_item
,items it
where months_between(sysdate,track_item.dispatch_date) > 12
and items.item = track_item.item(+);
Thank You