--Using the below as a model -- Write a query that calculates a running total qt
ID: 3594757 • Letter: #
Question
--Using the below as a model
-- Write a query that calculates a running total qty
-- for customer (ID - 5) ONLY and month using subqueries
-- Tables involved: TSQL2012 database, Sales.CustOrders view
-- Desired output:
custid ordermonth qty runqty
----------- ----------------------- ----------- -----------
5 2006-08-01 00:00:00.000 126 126
5 2006-12-01 00:00:00.000 43 169
5 2007-02-01 00:00:00.000 84 253
5 2007-05-01 00:00:00.000 87 340
5 2007-06-01 00:00:00.000 87 427
5 2007-08-01 00:00:00.000 52 479
5 2007-09-01 00:00:00.000 65 544
5 2007-10-01 00:00:00.000 35 579
5 2007-11-01 00:00:00.000 61 640
5 2007-12-01 00:00:00.000 10 650
5 2008-01-01 00:00:00.000 167 817
5 2008-02-01 00:00:00.000 128 945
5 2008-03-01 00:00:00.000 56 1001
(13 row(s) affected)
--------------------------------------------------------------------------------------------Model-------------------------------------------------------------------------------------
-- Write a query that calculates a running total qty
-- for each customer and month using subqueries
-- Tables involved: TSQLV4 database, Sales.CustOrders view
-- Desired output:
custid ordermonth qty runqty
----------- ----------------------- ----------- -----------
1 2015-08-01 00:00:00.000 38 38
1 2015-10-01 00:00:00.000 41 79
1 2016-01-01 00:00:00.000 17 96
1 2016-03-01 00:00:00.000 18 114
1 2016-04-01 00:00:00.000 60 174
2 2014-09-01 00:00:00.000 6 6
2 2015-08-01 00:00:00.000 18 24
2 2015-11-01 00:00:00.000 10 34
2 2016-03-01 00:00:00.000 29 63
3 2014-11-01 00:00:00.000 24 24
3 2015-04-01 00:00:00.000 30 54
3 2015-05-01 00:00:00.000 80 134
3 2015-06-01 00:00:00.000 83 217
3 2015-09-01 00:00:00.000 102 319
3 2016-01-01 00:00:00.000 40 359
...
(636 row(s) affected)
-- Solution
SELECT custid, ordermonth, qty,
(SELECT SUM(O2.qty)
FROM Sales.CustOrders AS O2
WHERE O2.custid = O1.custid
AND O2.ordermonth <= O1.ordermonth) AS runqty
FROM Sales.CustOrders AS O1
ORDER BY custid, ordermonth;
Explanation / Answer
Output:
custid ordermonth qty runqty 5 8/1/06 0:00 126 126 5 12/1/06 0:00 43 169 5 2/1/07 0:00 84 253 5 5/1/07 0:00 87 340 5 6/1/07 0:00 87 427 5 8/1/07 0:00 52 479 5 9/1/07 0:00 65 544 5 10/1/07 0:00 35 579 5 11/1/07 0:00 61 640 5 12/1/07 0:00 10 650 5 1/1/08 0:00 167 817 5 2/1/08 0:00 128 945 5 3/1/08 0:00 56 1001Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.