AdventureWorks Database: Determine the discount price for the salesorderid 46672

Last update on December 20 2024 11:58:03 (UTC/GMT +8 hours)

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 ).

Sample table: Sales.SalesOrderDetail

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.