Do note I am a novice (about 8 months exposure to Oracle Sql - so be gentle please :)
I have the following scenario
Table A containing:
Wave_No, Wave_Desc, Wave_Date
Table B containing:
Wave_No, Order_No, Order_Date, Order_Status
Table C containing:
Order_No, Line_No, Ship_qty, Order_Qty
Notes:
1. Table A contains many Waves
2. Each Wave can have many Orders with multiple Order Dates and Statuses
3. Order Status can be Picking, Complete
4. Following is sql:
select z.*
from
(Select
to_char(trunc(A.WAVE_DATE),'DD/MM/YY') As WAVE_DATE
, A.WAVE_NUM WAVE_NUM
, A.WAVE_DES WAVE_DESC
,sum(C.ord_qty) ORD_QTY
,SUM(case when B.ORD_STS = 'COMPLETE' then C.ship_qty else 0 end) CTN_PICKED
,SUM(case when B.ORD_STS = 'PICKING' then C.ship_qty) else 0 end) CTN_TO_BE_PICKED
, to_char(trunc(min(b.ord_date)),'DD/MM/YY') oldest_pick_order_date
, trunc(sysdate) - trunc(min(b.ord_date)) oldest_pick_order_age
From WAVE_tbl A
join ORD_Htbl B on A.COMP = B.COMP and A.ORD_NO = B.ORD_NO
join ORD_Dtbl C on C.COMP = B.COMP and C.ORD_NO = B.ORD_NO
Where A.COMP = 'ABC' and C.ORD_QTY > 0
and b.cust = 'CustomerA'
--and b.ord_date = (select least(ord_date) from ORD_htbl x
-- where x.comp=b.comp and x.ord_no=b.ord_no
-- and x.ord_sts = 'PICKING')
group by trunc(A.WAVE_DATE), A.WAVE_NUM,A.WAVE_DES
) z
Question: I need the report to summarise by Wave, wave description, sum of Order Qty and Cartons Picked and Cartons to be Picked, but I also want to know the oldest 'unconfirmed order' and the age (number of days from that order date to current date.
How do I get the oldest order_date for an order that is in status PICKING?
Current code returns the oldest date without testing the order status and there are older orders that are COMPLETE and therefore I want to bypass them and find the oldest PICKING Order.
I tried the commented out code, but that is insisting I have ord_date in the group by which then affects my results (I get multiple rows for the same wave, with a break on every different order date :(
Any help appreciated.
You probably want to do something like you have in the SUMs: check the status inside MIN, only returning the date if the status is PICKING.
e.g.:
min ( case when B.ORD_STS = 'PICKING' then b.ord_date end )
If this doesn't help, then please post a complete test case for your data, including:
create table for all tables
some data in the form of insert into for these tables
the output you expect based on these data