w3resource

AdventureWorks Database: Return the orders that have sales on or after December 2011

SQL Query - AdventureWorks: Exercise-183 with Solution

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

Click to view Full table

Sample Solution:

SELECT salesorderid,
    DATE_TRUNC('month', orderdate) AS MonthOrderOccurred,
    salespersonid,
    customerid,
    subtotal,
    SUM(subtotal) OVER (
        PARTITION BY customerid ORDER BY orderdate,
            salesorderid ROWS UNBOUNDED PRECEDING
        ) AS RunningTotal,
    orderdate AS ActualOrderDate
FROM Sales.salesorderheader
WHERE salespersonid IS NOT NULL
    AND DATE_TRUNC('month', orderdate) >= '2011-12-01'

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.



Follow us on Facebook and Twitter for latest update.