troubleshooting Question

ReportSQLQuerry_SumingQtyShipped

Avatar of sam15
sam15 asked on
Oracle DatabaseSQL
14 Comments1 Solution423 ViewsLast Modified:
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);
ASKER CERTIFIED SOLUTION
subratabiswas

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 14 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 14 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros