sqlsql-servercommon-table-expressionderived-table

How do I create a common table expression with two derived tables


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.


Solution

  • 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;