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
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.
- Weekly Trends
- Python Interview Questions and Answers: Comprehensive Guide
- Scala Exercises, Practice, Solution
- Kotlin Exercises practice with solution
- MongoDB Exercises, Practice, Solution
- SQL Exercises, Practice, Solution - JOINS
- Java Basic Programming Exercises
- SQL Subqueries
- Adventureworks Database Exercises
- C# Sharp Basic Exercises
- SQL COUNT() with distinct
- JavaScript String Exercises
- JavaScript HTML Form Validation
- Java Collection Exercises
- SQL COUNT() function
- SQL Inner Join