none
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.
    Saturday, December 21, 2013 10:27 AM

Answers

  • 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


    Saturday, December 21, 2013 12:56 PM
    Moderator
  • 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

    Monday, December 23, 2013 12:58 AM

All replies

  • 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


    Saturday, December 21, 2013 12:56 PM
    Moderator
  • 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

    Monday, December 23, 2013 12:58 AM