STOCK_ITEMS
NAME type
------------------------------------------------------------------------
STOCK_NUMBER VARCHAR2(20)
.......
SHIPMENT
Name Null? Type
----------------------------------------- -------- ----------------------------
SHIPMENT_ID NOT NULL NUMBER(10)
ORDER_NO NUMBER(10)
SHIPMENT_DATE DATE
SHIP_FROM_ORG VARCHAR2(8)
SHIP_TO_ORG VARCHAR2(8)
...
SHIPPED_ITEMS
Name Null? Type
----------------------------------------- -------- ----------------------------
SHIPMENT_ID NOT NULL NUMBER(10)
STOCK_NUMBER NOT NULL VARCHAR2(20)
ITEM_NUMBER NOT NULL NUMBER(2)
QUANTITY_SHIPPED NUMBER(8)
SHIPCODE VARCHAR2(1)
created_date DATE
I have the above 3 tables.
I need to report on 1 month, 6 month, 1 year, 2 year, 5 year and 10 year usage for each stock item. This is the total quantity shipped in each period. How would you write the SQL for that. Would you use subquery for each calculation and include that in main query like this or that would be slow and it better to do the whole clculations in one scan using CASE.
select stock_number, (query1) 1_mon, (query2) 6_mon, (query3) 1_yr, (query 4) 2_yr, (query5) 5_yr from stock_item;
query1 = select sum(quantity_shipped) from shipped_item a WHERE created date between trunc(sysdate) and trunc(sysdate-30);
mlmcc
Zone Advisor