AdventureWorks Database: Determine the discount price for the salesorderid 46672

SQL Query - AdventureWorks: Exercise-84 with Solution

84. From the following table write a SQL query to determine the discount price for the salesorderid 46672. Calculate only those orders with discounts of more than.02 percent. Return productid, UnitPrice, UnitPriceDiscount, and DiscountPrice (UnitPrice*UnitPriceDiscount ).

salesorderid|salesorderdetailid|carriertrackingnumber|orderqty|productid|specialofferid|unitprice|unitpricediscount|rowguid                             |modifieddate           |
------------+------------------+---------------------+--------+---------+--------------+---------+-----------------+------------------------------------+-----------------------+
       43659|                 1|4911-403C-98         |       1|      776|             1| 2024.994|                0|b207c96d-d9e6-402b-8470-2cc176c42283|2011-05-31 00:00:00.000|
       43659|                 2|4911-403C-98         |       3|      777|             1| 2024.994|                0|7abb600d-1e77-41be-9fe5-b9142cfc08fa|2011-05-31 00:00:00.000|
       43659|                 3|4911-403C-98         |       1|      778|             1| 2024.994|                0|475cf8c6-49f6-486e-b0ad-afc6a50cdd2f|2011-05-31 00:00:00.000|
       43659|                 4|4911-403C-98         |       1|      771|             1| 2039.994|                0|04c4de91-5815-45d6-8670-f462719fbce3|2011-05-31 00:00:00.000|
       43659|                 5|4911-403C-98         |       1|      772|             1| 2039.994|                0|5a74c7d2-e641-438e-a7ac-37bf23280301|2011-05-31 00:00:00.000|
       43659|                 6|4911-403C-98         |       2|      773|             1| 2039.994|                0|ce472532-a4c0-45ba-816e-eefd3fd848b3|2011-05-31 00:00:00.000|
       43659|                 7|4911-403C-98         |       1|      774|             1| 2039.994|                0|80667840-f962-4ee3-96e0-aeca108e0d4f|2011-05-31 00:00:00.000|
       43659|                 8|4911-403C-98         |       3|      714|             1|  28.8404|                0|e9d54907-e7b7-4969-80d9-76ba69f8a836|2011-05-31 00:00:00.000|
       43659|                 9|4911-403C-98         |       1|      716|             1|  28.8404|                0|aa542630-bdcd-4ce5-89a0-c1bf82747725|2011-05-31 00:00:00.000|
       43659|                10|4911-403C-98         |       6|      709|             1|      5.7|                0|ac769034-3c2f-495c-a5a7-3b71cdb25d4e|2011-05-31 00:00:00.000|
       43659|                11|4911-403C-98         |       2|      712|             1|   5.1865|                0|06a66921-6b9f-4199-a912-ddafd383472b|2011-05-31 00:00:00.000|
       43659|                12|4911-403C-98         |       4|      711|             1|  20.1865|                0|0e371ee3-253e-4bb0-b813-83cf4224f972|2011-05-31 00:00:00.000|
	   -- more --

Click to view Full table

Sample Solution:

-- Selecting the product ID, unit price, unit price discount, and calculated discount price
SELECT productid, UnitPrice, UnitPriceDiscount,  
       CAST(ROUND(UnitPrice * UnitPriceDiscount, 0) AS int) AS DiscountPrice  

-- From the sales schema's SalesOrderDetail table
FROM sales.salesorderdetail  

-- Filtering the results to include only rows where the sales order ID is 46672
-- and the unit price discount is greater than 0.02
WHERE SalesOrderid = 46672   
      AND UnitPriceDiscount > .02;

Explanation:

• The SQL query retrieves data from the SalesOrderDetail table within the sales schema.
• It selects the product ID, unit price, unit price discount, and the calculated discount price.
• The ROUND() function is used to round the result of multiplying the unit price by the unit price discount to the nearest integer.
• The CAST() function is used to convert the rounded discount price to an integer.
• The WHERE clause filters the results to include only rows where the sales order ID is 46672 and the unit price discount is greater than 0.02.

Sample Output:

productid|unitprice|unitpricediscount|discountprice|
---------+---------+-----------------+-------------+
      712|   4.7543|             0.05|            0|
      707|  16.8221|              0.1|            2|
      711|  16.8221|              0.1|            2|
      762|  234.897|              0.3|           70|
      854|  41.2445|             0.05|            2|
      708|  16.8221|              0.1|            2|

SQL AdventureWorks Editor:

Practice Online

Contribute your code and comments through Disqus.

Previous: Convert the Name column to a char(16) column.
Next: Find average vacation hours,and total sick leave hours the vice president used.