/* In the below example we define CTE by name "a". CTE statement retrives
sales statistics for all items in year 1999. The CTE is then followed by a
select calling it, that displayes results from "a" adding to every itemcode
item title */
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
)
SELECT a.ItemCode, b.ItemName, a.Sale1999, a.Qnt1999
FROM a INNER JOIN dbo.Items AS b
ON a.ItemCode = b.ItemCode;
/* the same effect can be achieved by using well known nested-query syntax */
SELECT a.ItemCode, b.ItemName, a.Sale1999, a.Qnt1999
FROM
(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)
AS a
INNER JOIN dbo.Items AS b
ON a.ItemCode = b.ItemCode;
/* but CTE advocates says that it is easier to code and review nested queries
in CTE way */
|