/* 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
|