Oracle Database query help
- or -
Post a project like this£15(approx. $19)
- Posted:
- Proposals: 14
- Remote
- #989834
- Expired
Freelancer (WordPress specialists (PHP, HTML, CSS, JS/jQuery)/Mobile Apps/Graphic Designer/)
Larkana
eBay & Amazon Listing Expert / Linnworks Expert /Ecommerce developer/ Magento / Woocommerce
Chuhar Jamali
105640611246813721475400368211571040881109385011048251119666113066811318151136340
Description
Experience Level: Expert
General information for the business: Store equipment
Database management system (DBMS): Oracle
Description of requirements/functionality: Im looking for assistance in trying to find out how to script the following out in Oracle. I am new to it and struggling to get the correct syntax.
Question 1)
I was looking for something like say the 5 customers who placed the largest number of orders in November, excluding customers with Ebay, Amazon or EPOS in their name.
We would like the total number of orders, the total number of sales items, the total ORDER_VALUE and the average values of each sales line and the average value of each order.
Result Columns - Customer_name, Total_Value, Order_count, AVG_Value_per_order, Total_lines, AVG Value Per Line
Questions 2)
Finally I’d like to see the total sales in November (ORDER_DATE in November) against total credits in November (CREDIT_DATE in November) for each un-withdrawn SALES_OFFICE, where the sales office has a credit or sale. Note; some sales offices will have credits and no sales and some will have sales and no credits.
The SALES_OFFICE for a sales or credit is held in the SALES_HEADER and CREDIT_NOTE header records.
Table SALES_POINT – contains a list of sales offices. The primary key is SALES_OFFICE, with a WITHDRAWN field of 0/1 and an OFFICE_NAME field as the descriptive name.
Customer credits are held in 2 tables,
Table CREDIT_NOTE – contains header level information on credits including the SALES_OFFICE and CREDIT_DATE. The primary key is CREDIT_NOTE_NUM
Table CREDIT_NOTE _ITEM – contains item / product level information on credits, including the QUANTITY and NET_PRICE of the items credited. The primary key is a combination of CREDIT_NOTE_NUM and CREDIT_NOTE_ITEM
Unlike the SALES_HEADER table, which contains the ORDER_VALUE, the total value of a customer return must be calculated as the sum of the item’s QUANTITY x NET_PRICE
Results Columns - Sales_office, Office_Name, Total_sales, Total_credits
-------------------------------------------------------------------------------------------------------------------------------------------------------
Data Structure
Table SALES_HEADER – contains header level information on sales orders like order date, order number, delivery address and order value. The primary key is a combination of the SALES_DOCUMENT_NUM and the ORDER_TYPE
Table SALES_ITEM – contains item / product level information on sales orders, including PRODUCT_CODE. The primary key is a combination of the SALES_DOCUMENT_NUM, SALES_ITEM_NUM and the ORDER_TYPE
Table PRODUCT – contains product level information, including the item description (LONG_DESCRIPTION_1) . The primary key is PRODUCT_CODE
Table CUSTOMER – Contains customer level information, including name (CUSTOMER_NAME) The primary key is CUSTOMER_ACCOUNT
Extra notes:
Database management system (DBMS): Oracle
Description of requirements/functionality: Im looking for assistance in trying to find out how to script the following out in Oracle. I am new to it and struggling to get the correct syntax.
Question 1)
I was looking for something like say the 5 customers who placed the largest number of orders in November, excluding customers with Ebay, Amazon or EPOS in their name.
We would like the total number of orders, the total number of sales items, the total ORDER_VALUE and the average values of each sales line and the average value of each order.
Result Columns - Customer_name, Total_Value, Order_count, AVG_Value_per_order, Total_lines, AVG Value Per Line
Questions 2)
Finally I’d like to see the total sales in November (ORDER_DATE in November) against total credits in November (CREDIT_DATE in November) for each un-withdrawn SALES_OFFICE, where the sales office has a credit or sale. Note; some sales offices will have credits and no sales and some will have sales and no credits.
The SALES_OFFICE for a sales or credit is held in the SALES_HEADER and CREDIT_NOTE header records.
Table SALES_POINT – contains a list of sales offices. The primary key is SALES_OFFICE, with a WITHDRAWN field of 0/1 and an OFFICE_NAME field as the descriptive name.
Customer credits are held in 2 tables,
Table CREDIT_NOTE – contains header level information on credits including the SALES_OFFICE and CREDIT_DATE. The primary key is CREDIT_NOTE_NUM
Table CREDIT_NOTE _ITEM – contains item / product level information on credits, including the QUANTITY and NET_PRICE of the items credited. The primary key is a combination of CREDIT_NOTE_NUM and CREDIT_NOTE_ITEM
Unlike the SALES_HEADER table, which contains the ORDER_VALUE, the total value of a customer return must be calculated as the sum of the item’s QUANTITY x NET_PRICE
Results Columns - Sales_office, Office_Name, Total_sales, Total_credits
-------------------------------------------------------------------------------------------------------------------------------------------------------
Data Structure
Table SALES_HEADER – contains header level information on sales orders like order date, order number, delivery address and order value. The primary key is a combination of the SALES_DOCUMENT_NUM and the ORDER_TYPE
Table SALES_ITEM – contains item / product level information on sales orders, including PRODUCT_CODE. The primary key is a combination of the SALES_DOCUMENT_NUM, SALES_ITEM_NUM and the ORDER_TYPE
Table PRODUCT – contains product level information, including the item description (LONG_DESCRIPTION_1) . The primary key is PRODUCT_CODE
Table CUSTOMER – Contains customer level information, including name (CUSTOMER_NAME) The primary key is CUSTOMER_ACCOUNT
Extra notes:
Dean P.
100% (9)Projects Completed
10
Freelancers worked with
11
Projects awarded
28%
Last project
15 Dec 2015
United Kingdom
New Proposal
Login to your account and send a proposal now to get this project.
Log inClarification Board Ask a Question
-
There are no clarification messages.
We collect cookies to enable the proper functioning and security of our website, and to enhance your experience. By clicking on 'Accept All Cookies', you consent to the use of these cookies. You can change your 'Cookies Settings' at any time. For more information, please read ourCookie Policy
Cookie Settings
Accept All Cookies