So I tried to create this CTE with two derived tables. My first derived table will display account numbers, account descriptions for account numbers within the 500 range. The second derived table in the CTE - should list account numbers, invoice totals and invoice id’s that have an Invoice total greater than 1000.
Now I started with creating those two tables separately to make sure I know what I'm doing. They worked like this:
SELECT AccountNo,
AccountDescription
FROM Accounts
WHERE AccountNo BETWEEN 500 and 599
SELECT Accounts.AccountNo,
Invoices.InvoiceTotal,
Invoices.InvoiceID
FROM Accounts
JOIN InvoiceLineItems
ON Accounts.AccountNo = InvoiceLineItems.AccountNo
JOIN Invoices
ON InvoiceLineItems.InvoiceID = Invoices.InvoiceID
WHERE Invoices.InvoiceTotal >= 1000
Now when I tried to turn them into a CTE, my best try looks like this:
WITH Accounts500to599 AS
(SELECT AccountNo,
AccountDescription
FROM Accounts
WHERE AccountNo BETWEEN 500 and 599
ORDER BY Accounts.AccountNo),
InvoicesAbove1000 AS
(SELECT Accounts.AccountNo,
Invoices.InvoiceTotal,
Invoices.InvoiceID
FROM Accounts
JOIN InvoiceLineItems
ON Accounts.AccountNo = InvoiceLineItems.AccountNo
JOIN Invoices
ON InvoiceLineItems.InvoiceID = Invoices.InvoiceID
WHERE Invoices.InvoiceTotal >= 1000
ORDER BY Accounts.AccountNo)
Now my final goal is to show accounts with invoices larger than the average invoice total, which should be simple enough, but I still have a lot to learn when it comes to creating these CTE's.
Any advice would be appreciated. Thank you.
As a bit of an expansion to seanb's answer, here's how you might attempt to get your invoices greater than the invoices average. Take a look at the WHERE
.
;WITH Accounts500to599 AS (
SELECT
AccountNo,
AccountDescription
FROM AS Accounts
WHERE
AccountNo BETWEEN 500 and 599
),
InvoicesAbove1000 AS (
SELECT
Accounts.AccountNo,
Invoices.InvoiceTotal,
Invoices.InvoiceID
FROM Accounts
JOIN InvoiceLineItems
ON Accounts.AccountNo = InvoiceLineItems.AccountNo
JOIN Invoices
ON InvoiceLineItems.InvoiceID = Invoices.InvoiceID
WHERE
Invoices.InvoiceTotal >= 1000
)
SELECT
Accounts500to599.AccountNo,
Accounts500to599.AccountDescription,
InvoicesAbove1000.InvoiceID,
InvoicesAbove1000.InvoiceTotal
FROM Accounts500to599
INNER JOIN InvoicesAbove1000
ON Accounts500to599.AccountNo = InvoicesAbove1000.AccountNo
WHERE InvoicesAbove1000.InvoiceTotal > (
SELECT
AVG( InvoiceTotal )
FROM Accounts AS a
INNER JOIN InvoiceLineItems l
ON a.AccountNo = l.AccountNo
INNER JOIN Invoices i
ON l.InvoiceID = i.InvoiceID
WHERE
a.AccountNo BETWEEN 500 AND 599
AND i.InvoiceTotal > 1000
)
ORDER BY
Accounts500to599.AccountNo;