TSQL -> Multiple Common Table Expressions

Multiple Common Table Expressions can be used instead of multiple nested queries.


/* In the below example we define two CTE "a" and "b" one after another. "a" retrives sales statistics for all items in year 1999. "b" adds item-titles to result set of "a" and filters it using @substr parameter. Final select displays records ordered by DESC Sales Quantity */

DECLARE @substr NVarchar(50);
SET @substr = 'Philips';

WITH a AS
(
   SELECT t1.ItemCode, SUM(t1.LineTotal) AS Sale1999,
   SUM(t1.Qnt) AS Qnt1999
   FROM dbo.InvLines AS t1 INNER JOIN dbo.Invoice AS t2
   ON t1.DocKey = t2.DocKey
   WHERE Year(t2.DocDate)=1999
   GROUP BY t1.ItemCode
),
b AS
(
   SELECT a.ItemCode, it.ItemName, a.Qnt1999,
   it.Price AS CtlgPrice
   FROM a INNER JOIN dbo.Items AS it
   ON a.ItemCode = it.ItemCode
   WHERE CHARINDEX(@substr,it.ItemName) <> 0
)
SELECT b.ItemCode, b.ItemName, b.Qnt1999
FROM b
ORDER BY b.Qnt1999 DESC



Query results will came as following:
ItemCodeItemNameQnt1999
5003298Philips DVP5140 Multiformat DVD Player ..590
5003258Philips DVP642 DivX-Certified Progressive ..426


sqlexamples.info