CODEWITHSUNDEEP
sqlt-sqlderived
Chris
Chris

Reputation: 7611

Scope of derived tables

I have a fairly complex SQL query, where we'll need to return a number of columns, and each represents a different row from the table. All the derived tables need to be filtered by a value, to bring back only ones for that account. The following works great:

SELECT CurrentBalance.Value,
       CurrentBalance.Customer,
       Debt30Balance.Value    AS Expr1,
       Debt30Balance.Customer AS Expr2,
       Debt60Balance.Value    AS Expr3,
       Debt60Balance.Customer AS Expr4,
       Debt90Balance.Value    AS Expr5,
       Debt90Balance.Customer AS Expr6,
       WIPCurrent.Value       AS Expr7,
       WIPCurrent.Customer    AS Expr8,
       WIP30Days.Value        AS Expr9,
       WIP30Days.Customer     AS Expr10,
       WIP60Days.Value        AS Expr11,
       WIP60Days.Customer     AS Expr12,
       WIP90Days.Value        AS Expr13,
       WIP90Days.Customer     AS Expr14
FROM   (SELECT TOP (1) Value,
                       Customer
        FROM   DebtBreakdown
        WHERE  ( Customer = @CustomerID )
               AND ( Type = 0 )
        ORDER  BY Timestamp DESC) AS CurrentBalance
       INNER JOIN (SELECT TOP (1) Value,
                                  Customer
                   FROM   DebtBreakdown AS DebtBreakdown_7
                   WHERE  ( Customer = @CustomerID )
                          AND ( Type = 1 )
                   ORDER  BY Timestamp DESC) AS Debt30Balance
         ON CurrentBalance.Customer = Debt30Balance.Customer
       INNER JOIN (SELECT TOP (1) Value,
                                  Customer
                   FROM   DebtBreakdown AS DebtBreakdown_6
                   WHERE  ( Customer = @CustomerID )
                          AND ( Type = 2 )
                   ORDER  BY Timestamp DESC) AS Debt60Balance
         ON Debt30Balance.Customer = Debt60Balance.Customer
       INNER JOIN (SELECT TOP (1) Value,
                                  Customer
                   FROM   DebtBreakdown AS DebtBreakdown_5
                   WHERE  ( Customer = @CustomerID )
                          AND ( Type = 3 )
                   ORDER  BY Timestamp DESC) AS Debt90Balance
         ON Debt60Balance.Customer = Debt90Balance.Customer
       INNER JOIN (SELECT TOP (1) Value,
                                  Customer
                   FROM   DebtBreakdown AS DebtBreakdown_4
                   WHERE  ( Customer = @CustomerID )
                          AND ( Type = 4 )
                   ORDER  BY Timestamp DESC) AS WIPCurrent
         ON Debt90Balance.Customer = WIPCurrent.Customer
       INNER JOIN (SELECT TOP (1) Value,
                                  Customer
                   FROM   DebtBreakdown AS DebtBreakdown_3
                   WHERE  ( Customer = @CustomerID )
                          AND ( Type = 5 )
                   ORDER  BY Timestamp DESC) AS WIP30Days
         ON WIPCurrent.Customer = WIP30Days.Customer
       INNER JOIN (SELECT TOP (1) Value,
                                  Customer
                   FROM   DebtBreakdown AS DebtBreakdown_2
                   WHERE  ( Customer = @CustomerID )
                          AND ( Type = 6 )
                   ORDER  BY Timestamp DESC) AS WIP60Days
         ON WIP30Days.Customer = WIP60Days.Customer
       INNER JOIN (SELECT TOP (1) Value,
                                  Customer
                   FROM   DebtBreakdown AS DebtBreakdown_1
                   WHERE  ( Customer = @CustomerID )
                          AND ( Type = 7 )
                   ORDER  BY Timestamp DESC) AS WIP90Days
         ON WIP60Days.Customer = WIP90Days.Customer

But, I need to be able to filter on something other than the given parameter. Basically, what I want to do is select the Customer record, given a parameter such as the Name of the customer, select the ID then use that for the derived tables. I've tried with Join's but the derived tables are not in the scope of any joins.

Any ideas?

Upvotes: 0

Views: 275

Answers (1)

Martin Smith
Martin Smith

Reputation: 453327

If you are on at least SQL Server 2005 (as seems likely from the bracketed TOP expression) you can use Common Table Expressions instead of derived tables. 

/*Any previous statement must be terminated with a semi colon*/
WITH CurrentBalance
     AS (SELECT TOP (1) Value,
                        Customer
         FROM   DebtBreakdown
         WHERE  ( Customer = @CustomerID )
                AND ( Type = 0 )
         ORDER  BY Timestamp DESC), /*Comma delimit CTE definitions*/
     Debt30Balance
     AS (SELECT TOP (1) Value,
                        Customer
         FROM   DebtBreakdown AS DebtBreakdown_7
         WHERE  ( Customer = @CustomerID )
                AND ( Type = 1 )
         ORDER  BY Timestamp DESC)
        /* ETC ETC */

You can reference CTEs multiple times in the same query.

Upvotes: 0

Related Questions