On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.
Course Of The Month
3 days, 2 hours left to enrollBecome a Premium Member and unlock a new, free course in leading technologies each month.
Solved
ReportSQLQuerry_SumingQtyS
Posted on 2011-03-07
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);
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);
[X]
Welcome to Experts Exchange
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
- Help others & share knowledge
- Earn cash & points
- Learn & ask questions
-
6
-
6
- +1
14 Comments
You can go with Reporting aggregate functions in oracle which gives you the result for yearly , monthly and quarterly report like to get the details on sales or stocks quantity report:
check this link hope this will give all what you are trying to get:
http://www.orafusion.com/art_anlytc.htm
check this link hope this will give all what you are trying to get:
http://www.orafusion.com/art_anlytc.htm
with joined_table as (select stock_items.stock_number, shipment.shipment_date, shipped_items.quantity_shi
from shipment, shipped_items, stock_items
where shipped_items.shipment_id = shipment.shipment_id
and stock_items.stock_number = shipped_items.stock_number
one_month_sum as (select stock_number, sum(quantity_shipped) as qty
from joined_table
where shipment_date > add_months(sysdate, -1)
group by stock_number),
six_month_sum as (select stock_number, sum(quantity_shipped) as qty
from joined_table
where shipment_date > add_months(sysdate, -6)
group by stock_number),
one_year_sum as (select stock_number, sum(quantity_shipped) as qty
from joined_table
where shipment_date > add_months(sysdate, -12)
group by stock_number),
two_year_sum as (select stock_number, sum(quantity_shipped) as qty
from joined_table
where shipment_date > add_months(sysdate, -24)
group by stock_number),
five_year_sum as (select stock_number, sum(quantity_shipped) as qty
from joined_table
where shipment_date > add_months(sysdate, -60)
group by stock_number),
ten_year_sum as (select stock_number, sum(quantity_shipped) as qty
from joined_table
where shipment_date > add_months(sysdate, -120)
group by stock_number)
select stock_items.stock_number,
sum(one_month_sum.qty) as one_month_sum,
sum(six_month_sum.qty) as six_month_sum,
sum(one_year_sum.qty) as one_year_sum,
sum(two_year_sum.qty) as two_year_sum,
sum(five_year_sum.qty) as five_year_sum,
sum(ten_year_sum.qty) as ten_year_sum
from stock_items, one_month_sum, six_month_sum, one_year_sum, two_year_sum, five_year_sum, ten_year_sum
where one_month_sum.stock_number
and six_month_sum.stock_number
and one_year_sum.stock_number(
and two_year_sum.stock_number(
and five_year_sum.stock_number
and ten_year_sum.stock_number(
from shipment, shipped_items, stock_items
where shipped_items.shipment_id = shipment.shipment_id
and stock_items.stock_number = shipped_items.stock_number
one_month_sum as (select stock_number, sum(quantity_shipped) as qty
from joined_table
where shipment_date > add_months(sysdate, -1)
group by stock_number),
six_month_sum as (select stock_number, sum(quantity_shipped) as qty
from joined_table
where shipment_date > add_months(sysdate, -6)
group by stock_number),
one_year_sum as (select stock_number, sum(quantity_shipped) as qty
from joined_table
where shipment_date > add_months(sysdate, -12)
group by stock_number),
two_year_sum as (select stock_number, sum(quantity_shipped) as qty
from joined_table
where shipment_date > add_months(sysdate, -24)
group by stock_number),
five_year_sum as (select stock_number, sum(quantity_shipped) as qty
from joined_table
where shipment_date > add_months(sysdate, -60)
group by stock_number),
ten_year_sum as (select stock_number, sum(quantity_shipped) as qty
from joined_table
where shipment_date > add_months(sysdate, -120)
group by stock_number)
select stock_items.stock_number,
sum(one_month_sum.qty) as one_month_sum,
sum(six_month_sum.qty) as six_month_sum,
sum(one_year_sum.qty) as one_year_sum,
sum(two_year_sum.qty) as two_year_sum,
sum(five_year_sum.qty) as five_year_sum,
sum(ten_year_sum.qty) as ten_year_sum
from stock_items, one_month_sum, six_month_sum, one_year_sum, two_year_sum, five_year_sum, ten_year_sum
where one_month_sum.stock_number
and six_month_sum.stock_number
and one_year_sum.stock_number(
and two_year_sum.stock_number(
and five_year_sum.stock_number
and ten_year_sum.stock_number(
Active 4 days ago
is this ansi sql above. we use regular sql and joins in all applications. can you rewrite using normal sql.
Active 4 days ago
kind of stange. i thought it is more of the ansi format we dont use.
Is this doing subqueries with full scan for each subquery? what index columns would help here.
Is this doing subqueries with full scan for each subquery? what index columns would help here.
Resultset from the 1st sub-query shold be used in all the subsequent ones. Tuning this query will be a separate activity, but can be started by creating index on the columns used in the WHERE clauses and the GROUP BY clause. Try to see the effect of the indices by executing sql trace.
Active 4 days ago
Query works fine. I tested it.
just a quick would you use SYSDATE or TRUNC(SYDATE) to go for full days instead of partials?
Also, do you ecommend to use CREATED_DATE for record in case user made a data entry error in shipment date.
just a quick would you use SYSDATE or TRUNC(SYDATE) to go for full days instead of partials?
Also, do you ecommend to use CREATED_DATE for record in case user made a data entry error in shipment date.
Yes, applying TRUNC function will return data for the few extra hours to include the period from the beginning of the day. FYI, by default TRUNC would round the time to the beginning of the day, but there are different (optional) formatting options to round it on different period (MONTH, YEAR etc.)
Whether to use CREATED_DATE or SHIPMENT_DATE, will depend on the application. It is possible that CREATED_DATE is maintained programatically and is free from any human data entry error. From that point of view, it appears preferable. But it is also possible that the data is being entered in batch or by a data entry operation at a time different from the actual shipment time. In that scenario, if someone wants to track the shipment by the actual time of shipping, he may not be interested in the CREATED_DATE. We are talking of two times, one when the shipping actually took place, and the other when the shipping record was entered into the system. Depending on the application, there may or may not be significant gap between them. Some users may ignore that gap, while others may be interested in one or the other. I guess, both the dates are maintained considering utility of both of them.
Whether to use CREATED_DATE or SHIPMENT_DATE, will depend on the application. It is possible that CREATED_DATE is maintained programatically and is free from any human data entry error. From that point of view, it appears preferable. But it is also possible that the data is being entered in batch or by a data entry operation at a time different from the actual shipment time. In that scenario, if someone wants to track the shipment by the actual time of shipping, he may not be interested in the CREATED_DATE. We are talking of two times, one when the shipping actually took place, and the other when the shipping record was entered into the system. Depending on the application, there may or may not be significant gap between them. Some users may ignore that gap, while others may be interested in one or the other. I guess, both the dates are maintained considering utility of both of them.
Active 4 days ago
TRUNC(SYSDATE) will exclude all transaction that happened today. It will stop all activity at yesterday midnight. so when users run report today 10 times it will yield same result. I think this is cleaner instead of using SYSDATE which will use part of today and part of the day 30 or 60 days ago.
do you agree?
The only think is i would need to create an a function based index INDEX on TRUNC(SHIPMENT_DATE).
we currently dont have any validation on shipment date nor any batch job. It is safer to use created_date which is automatically entered by system.
I am not sure why you used GROUP BY in your last query for result. The tables were grouped in your query for each before. is there anything i missed.
select stock_items.stock_number,
sum(one_month_sum.qty) as one_month_sum,
sum(six_month_sum.qty) as six_month_sum,
sum(one_year_sum.qty) as one_year_sum,
sum(two_year_sum.qty) as two_year_sum,
sum(five_year_sum.qty) as five_year_sum,
sum(ten_year_sum.qty) as ten_year_sum
from stock_items, one_month_sum, six_month_sum, one_year_sum, two_year_sum, five_year_sum, ten_year_sum
do you agree?
The only think is i would need to create an a function based index INDEX on TRUNC(SHIPMENT_DATE).
we currently dont have any validation on shipment date nor any batch job. It is safer to use created_date which is automatically entered by system.
I am not sure why you used GROUP BY in your last query for result. The tables were grouped in your query for each before. is there anything i missed.
select stock_items.stock_number,
sum(one_month_sum.qty) as one_month_sum,
sum(six_month_sum.qty) as six_month_sum,
sum(one_year_sum.qty) as one_year_sum,
sum(two_year_sum.qty) as two_year_sum,
sum(five_year_sum.qty) as five_year_sum,
sum(ten_year_sum.qty) as ten_year_sum
from stock_items, one_month_sum, six_month_sum, one_year_sum, two_year_sum, five_year_sum, ten_year_sum
1) Last GROUP BY is required because the query is using AGGREGATE function
2) Effect of TRUNC on duration may change depending on where the function is being applied (before applying other functions like ADD_MONTHS etc, or after the function call).
2) Effect of TRUNC on duration may change depending on where the function is being applied (before applying other functions like ADD_MONTHS etc, or after the function call).
Active 4 days ago
The reason i asked you about the aggregate is i have a few other columns from STOCK_ITEMS table to select like STOCK_DESCRIPTIO, UNITS, etc.
I do not want to select those in the aggregate function and group by them.
I am thinking to add another subquery and join that.
I have to test this but my thinking is that you already have aggregates in ONE_MONTH_SUM, SIX_MONTH_SUM. there should be one stock number, quantity.
So when you join it tothe main stock items fields it should be ONE to ONE join.
I do not want to select those in the aggregate function and group by them.
I am thinking to add another subquery and join that.
I have to test this but my thinking is that you already have aggregates in ONE_MONTH_SUM, SIX_MONTH_SUM. there should be one stock number, quantity.
So when you join it tothe main stock items fields it should be ONE to ONE join.
Featured Post
ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.
One of a set of tools we're offering as a way to say thank you for being a part of the community.
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
How to Unravel a Tricky Query
Introduction
If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them. …
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.
Suggested Courses
Course of the Month3 days, 2 hours left to enroll
Earn Certification
Certified Information Systems Auditor (CISA)
$99.00$89.10
696 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.