Sql server® 2012 t-sql fundamentals

 

CHAPTER 4

 

Subqueries

 

 155

8

When I need to solve querying problems, I often find it useful to rephrase the original request in a 

more technical way so that it will be more convenient to translate the request to a T-SQL query. To 

solve the current exercise, you can first try to express the request “return a running total quantity for 

each customer and month” differently—in a more technical manner. For each customer, return the 

customer ID, month, the sum of the quantity for that month, and the sum of all months less than or 

equal to the current month. The rephrased request can be translated to the following T-SQL query 

quite literally.

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;

www.it-ebooks.info

---

www.it-ebooks.info

---