Sql server® 2012 t-sql fundamentals

Multiple references

Another problematic aspect of derived tables stems from the fact that derived tables are defined in 

the FROM clause of the outer query and not prior to the outer query. As far as the FROM clause of 

the outer query is concerned, the derived table doesn’t exist yet; therefore, if you need to refer to 

multiple instances of the derived table, you can’t. Instead, you have to define multiple derived tables 

based on the same query. The query in Listing 5-3 provides an example.

LISTING 5-3

  Multiple Derived Tables Based on the Same Query

SELECT Cur.orderyear,  

  Cur.numcusts AS curnumcusts, Prv.numcusts AS prvnumcusts, 

  Cur.numcusts - Prv.numcusts AS growth 

FROM (SELECT YEAR(orderdate) AS orderyear, 

        COUNT(DISTINCT custid) AS numcusts 

      FROM Sales.Orders 

      GROUP BY YEAR(orderdate)) AS Cur 

  LEFT OUTER JOIN 

     (SELECT YEAR(orderdate) AS orderyear, 

        COUNT(DISTINCT custid) AS numcusts 

      FROM Sales.Orders 

      GROUP BY YEAR(orderdate)) AS Prv 

    ON Cur.orderyear = Prv.orderyear + 1;

www.it-ebooks.info

---