how to calculate master/detail table discount as value

Question

hi

I have a master table columns like InvoiceID,InvoiceDate,Discount%,InvoiceTotal & detail table columns like ProductID,Qty,Price,Discount%,ExtendedPrice. Now I want to calculate both discount percentage as value in amount. I have the following query

SELECT     InvoiceID, SUM(CONVERT(money, (Price * Qty) * (1 - Discount) / 100) * 100) AS ExtendedPrice
FROM         dbo.[InvoiceDetail]
GROUP BY InvoiceID

which is just subtract detail discount only from ExtendedPrice but I want to Subtract both discount from the totalInvoiceAmount in order to get netSalesValue.

Answer 1

You can do additional calculation the following way:

WITH CTE AS 
(SELECT     InvoiceID, SUM(CONVERT(money, (Price * Qty) * (1 - Discount) / 100) * 100) AS ExtendedPrice
FROM         dbo.[InvoiceDetail]
GROUP BY InvoiceID)

SELECT ......
FROM CTE INNER JOIN InvoiceMaster IM
           ON CTE.InvoiceID = IM.InvoiceID

---

Kalman Toth Database & OLAP Architect IPAD SELECT Query Video Tutorial 3.5 Hours
New Book / Kindle: Exam 70-461 Bootcamp: Querying Microsoft SQL Server 2012

Answer 2

Please post DDL, so that people do not have to guess what the keys, constraints, Declarative Referential Integrity, data types, etc. in your schema are. Learn how to follow ISO-11179 data element naming conventions and formatting rules (you did not). Temporal data should use ISO-8601 formats. Code should be in Standard SQL as much as possible and not local dialect. 

This is minimal polite behavior on SQL forums. Now we have to guess at everything and do your typing. 

Do not use % in a data element name. It is disallowed by Unicode and ISO Standards. The term “master” is not part of RDBMS; it is from mag tape files and network databases. Here is the usual skeleton you did not post. 

CREATE TABLE Orders
(invoice_nbr CHAR(10) NOT NULL PRIMARY KEY,
 invoice_date DATE NOT NULL,
 discount_percent DECIMAL (5,4) DEFAULT 0.00 NOT NULL,
 ..);

We never put the invoice total in the orders! This is design flaw is called a non-normal form redundancy. Read Tom Johnston's articles on it. Next, MONEY is a proprietary data type that does not do correct math. Google it. Multiplication and division do not work which will screw up your discounts. 

CREATE TABLE Order_Details 
(invoice_nbr CHAR(10) NOT NULL 
   REFERENCES Orders(invoice_nbr)
 product_id CHAR(10) NOT NULL,
  PRIMARY KEY (invoice_nbr, product_id)
 order_qty INTEGER NOT NULL,
 unit_price DECIMAL (10,2) NOT NULL,
   CHECK (unit_price >= 0.00),
 ..); 

First, compute the total invoice amounts, then do the discounts

WITH Invoice_Totals
AS 
(SELECT invoice_nbr, SUM(order_qty * unit_price) AS invoice_amt_tot
  FROM Order_Details
 GROUP BY invoice_nbr)

SELECT T1.invoice_nbr, T1.invoice_amt_tot, 
        (T1.invoice_amt_tot *(1.00 – T2.discount_percent))
        AS discount_amt
  FROM Invoice_Totals AS T1, Invoice_Totals AS T2; 

---

--CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL