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

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.