AdventureWorks Database: Return the orders that have sales on or after December 2011
183. From the following table write a query in SQL to return the orders that have sales on or after December 2011. Return salesorderid, MonthOrderOccurred, salespersonid, customerid, subtotal, Running Total, and actual order date.
Sample table: sales.salesorderheader
salesorderid|revisionnumber|orderdate |duedate |shipdate |status|onlineorderflag|purchaseordernumber|accountnumber |customerid|salespersonid|territoryid|billtoaddressid|shiptoaddressid|shipmethodid|creditcardid|creditcardapprovalcode|currencyrateid|subtotal |taxamt |freight |totaldue |comment|rowguid |modifieddate |
------------+--------------+-----------------------+-----------------------+-----------------------+------+---------------+-------------------+--------------+----------+-------------+-----------+---------------+---------------+------------+------------+----------------------+--------------+-----------+----------+---------+-----------+-------+------------------------------------+-----------------------+
43659| 8|2011-05-31 00:00:00.000|2011-06-12 00:00:00.000|2011-06-07 00:00:00.000| 5|false |PO522145787 |10-4020-000676| 29825| 279| 5| 985| 985| 5| 16281|105041Vi84182 | | 20565.6206| 1971.5149| 616.0984| 23153.2339| |79b65321-39ca-4115-9cba-8fe0903e12e6|2011-06-07 00:00:00.000|
43660| 8|2011-05-31 00:00:00.000|2011-06-12 00:00:00.000|2011-06-07 00:00:00.000| 5|false |PO18850127500 |10-4020-000117| 29672| 279| 5| 921| 921| 5| 5618|115213Vi29411 | | 1294.2529| 124.2483| 38.8276| 1457.3288| |738dc42d-d03b-48a1-9822-f95a67ea7389|2011-06-07 00:00:00.000|
43661| 8|2011-05-31 00:00:00.000|2011-06-12 00:00:00.000|2011-06-07 00:00:00.000| 5|false |PO18473189620 |10-4020-000442| 29734| 282| 6| 517| 517| 5| 1346|85274Vi6854 | 4| 32726.4786| 3153.7696| 985.553| 36865.8012| |d91b9131-18a4-4a11-bc3a-90b6f53e9d74|2011-06-07 00:00:00.000|
43662| 8|2011-05-31 00:00:00.000|2011-06-12 00:00:00.000|2011-06-07 00:00:00.000| 5|false |PO18444174044 |10-4020-000227| 29994| 282| 6| 482| 482| 5| 10456|125295Vi53935 | 4| 28832.5289| 2775.1646| 867.2389| 32474.9324| |4a1ecfc0-cc3a-4740-b028-1c50bb48711c|2011-06-07 00:00:00.000|
43663| 8|2011-05-31 00:00:00.000|2011-06-12 00:00:00.000|2011-06-07 00:00:00.000| 5|false |PO18009186470 |10-4020-000510| 29565| 276| 4| 1073| 1073| 5| 4322|45303Vi22691 | | 419.4589| 40.2681| 12.5838| 472.3108| |9b1e7a40-6ae0-4ad3-811c-a64951857c4b|2011-06-07 00:00:00.000|
43664| 8|2011-05-31 00:00:00.000|2011-06-12 00:00:00.000|2011-06-07 00:00:00.000| 5|false |PO16617121983 |10-4020-000397| 29898| 280| 1| 876| 876| 5| 806|95555Vi4081 | | 24432.6088| 2344.9921| 732.81| 27510.4109| |22a8a5da-8c22-42ad-9241-839489b6ef0d|2011-06-07 00:00:00.000|
43665| 8|2011-05-31 00:00:00.000|2011-06-12 00:00:00.000|2011-06-07 00:00:00.000| 5|false |PO16588191572 |10-4020-000146| 29580| 283| 1| 849| 849| 5| 15232|35568Vi78804 | | 14352.7713| 1375.9427| 429.9821| 16158.6961| |5602c304-853c-43d7-9e79-76e320d476cf|2011-06-07 00:00:00.000|
-- more --
Sample Solution:
-- Selecting specific columns along with additional calculated columns
SELECT salesorderid,
-- Truncating the orderdate to the month level and aliasing it as MonthOrderOccurred
DATE_TRUNC('month', orderdate) AS MonthOrderOccurred,
salespersonid,
customerid,
subtotal,
-- Calculating the running total of subtotals for each customer, ordered by orderdate and salesorderid
-- The running total is calculated using the SUM window function with the PARTITION BY clause
-- The ROWS UNBOUNDED PRECEDING clause specifies that the summation starts from the beginning of the partition
-- The ORDER BY clause defines the order within the partition
SUM(subtotal) OVER (
PARTITION BY customerid ORDER BY orderdate,
salesorderid ROWS UNBOUNDED PRECEDING
) AS RunningTotal,
-- Including the actual order date as it is
orderdate AS ActualOrderDate
-- Selecting from the Salesorderheader table
FROM Sales.salesorderheader
-- Filtering out records where salespersonid is not null and orderdate is on or after December 2011
WHERE salespersonid IS NOT NULL
AND DATE_TRUNC('month', orderdate) >= '2011-12-01';
Explanation:
- This SQL query retrieves data from the sales order header table with additional calculations for a running total.
- Comments are added to explain each part of the query for better understanding and maintenance.
- Here's a breakdown of what the query does:
- It selects specific columns from the sales order header table.
- It truncates the orderdate to the month level and aliases it as MonthOrderOccurred.
- It includes the salespersonid, customerid, subtotal, and actual order date columns.
- It calculates the running total of subtotals for each customer using the SUM window function.
- The running total is partitioned by customerid and ordered by orderdate and salesorderid.
- The running total calculation starts from the beginning of the partition due to the ROWS UNBOUNDED PRECEDING clause.
- It filters out records where the salespersonid is not null and the orderdate is on or after December 2011.
Sample Output:
salesorderid|monthorderoccurred |salespersonid|customerid|subtotal |runningtotal|actualorderdate |
------------+-----------------------+-------------+----------+-----------+------------+-----------------------+
45579|2012-01-01 00:00:00.000| 279| 29484| 4079.988| 4079.988|2012-01-29 00:00:00.000|
46389|2012-04-01 00:00:00.000| 279| 29484| 1104.9968| 5184.9848|2012-04-30 00:00:00.000|
47454|2012-07-01 00:00:00.000| 279| 29484| 27429.5294| 32614.5142|2012-07-31 00:00:00.000|
48395|2012-10-01 00:00:00.000| 279| 29484| 32562.6538| 65177.1680|2012-10-30 00:00:00.000|
49495|2013-01-01 00:00:00.000| 279| 29484| 24232.7654| 89409.9334|2013-01-28 00:00:00.000|
50756|2013-04-01 00:00:00.000| 279| 29484| 37643.0609| 127052.9943|2013-04-30 00:00:00.000|
53459|2013-07-01 00:00:00.000| 276| 29485| 29133.471| 29133.471|2013-07-31 00:00:00.000|
58907|2013-10-01 00:00:00.000| 276| 29485| 28413.4274| 57546.8984|2013-10-30 00:00:00.000|
65157|2014-01-01 00:00:00.000| 276| 29485| 22021.782| 79568.6804|2014-01-29 00:00:00.000|
71782|2014-05-01 00:00:00.000| 276| 29485| 33319.986| 112888.6664|2014-05-01 00:00:00.000|
45550|2012-01-01 00:00:00.000| 277| 29486| 54979.9007| 54979.9007|2012-01-29 00:00:00.000|
...
SQL AdventureWorks Editor:
Practice Online
Contribute your code and comments through Disqus.
Previous: Return a truncated date with 4 months added to the orderdate.
Next: Repeat the 0 character four times before productnumber.
What is the difficulty level of this exercise?
Test your Programming skills with w3resource's quiz.