SQL (Oracle) query for sales by item

Question

I need help to build a ORACLE SQL query that can give me Sales by Item Name for each month. Here are the columns that are needed in query – Date (month), Item Name, Quantity Sold, Average Unit Price, Net Sales , Credit units

• Quantity Sold

  of an Item is the net quantity sold after credits for a particular item in that month; that is =SUM(Invoice Items. Quantity sold) – SUM(Credit Note Items. credit Quantity )

• Net Sales

  of an item in a particular month = SUM(Invoice Items.Total Amount) - SUM(Credit Note Items. Credit Amount)

• Credit units : it refers to total units of item(credit Quantity) that have been credited in that month

Below are tables:

Invoice Items (Invoice Date, Invoice ID, Product ID, Item Name, Quantity sold, Total Amount)

Inventory Adjustment (Date, Entity, EntityID, ItemID, Item Name, Quantity, Unit Price, Stock On Hand, Net Amount) This table holds unit price and quantity of an item when invoice or credit note for that item was generated. Entity can be 'Invoice' or 'CreditNote'. EntityID is Invoice ID or CreditNotes ID

Credit Notes (CreditNote Date, CreditNotes ID, Customer ID)

Credit Note Items (CreditNotes ID, Item ID, Item Name, credit Quantity, Credit Amount) Note: this is line item table for Credit Notes table. CreditNote Date is available in master table

So far, I have this query but I'm unable to figure out next step of joining with credit note and credit note items and getting Net Sales and Quantity Sold.

SELECT
         inv_items."Invoice Date" inv_date,
         inv_items."Product ID" inv_items_Prod_ID,
        inv_items.Item Name,
         AVG(IDR."Unit Price"),
         SUM(inv_items."Quantity sold") total_Qty_Sold,
         SUM(inv_items."Total Amount") inv_ItemsSale
FROM  "Invoice Items" inv_items 
JOIN "Inventory Adjustment" IDR ON IDR."EntityID"  = inv_items."Invoice ID"
     AND    IDR."ItemID"  = inv_items."Product ID"
     AND    IDR."Date"  = inv_items."Invoice Date"  
GROUP BY 1,
     2,
     3

Any help is much appreciated.

Answer 1

From your query and description it seems that invoice_id, product_id and date - are unique for invoice items.

So you need to do the same for credit note – (select cni.*, cn.CreditNote Date from Credit Notes cn, Credit Note Items cni where cni.CreditNotes ID = cn.CreditNotes ID) sub and then join this subquery to idr by the same 3 columns.

Also you better group by IDR.itemname, IDR.itemid and IDR.date

Answer 2

I prepared the script according your question requirments, but because of less information in your question, such as datatypes for the columns and their structure, I'm not sure, you can just try and let me know whether the code is ok:

SELECT 
         to_char (inv_items."Invoice Date", 'MONTH') inv_month,
         inv_items."Product ID" inv_items_Prod_ID,
         inv_items."Item Name" inv_items_name,
         AVG(IDR."Unit Price") avg_unit_price,
         SUM(inv_items."Quantity sold") total_Qty_Sold,
         SUM(inv_items."Total Amount") inv_ItemsSale,
        (SUM(inv_items."Quantity sold") - SUM(cni."credit Quantity")) Quantity_Sold,
        (SUM(inv_items."Total Amount") - SUM(cni."Credit Amount")) Credit_units  
FROM  "Invoice Items" inv_items 
JOIN "Inventory Adjustment" IDR 
     ON IDR."EntityID"  = inv_items."Invoice ID"
     AND    IDR."ItemID"  = inv_items."Product ID"
     AND    IDR."Date"  = inv_items."Invoice Date"
JOIN "Credit Notes" cn 
     ON IDR."EntityID" = cn."CreditNotes ID"
     AND IDR."Entity" = 'CreditNote'  --- filtering by only 'CreditNote'
JOIN "Credit Note Items" cni
     ON cn."CreditNotes ID" = cni."CreditNotes ID"
GROUP BY to_char (inv_items."Invoice Date", 'MONTH'), inv_items."Product ID", inv_items."Item Name";

Hope it will help you)