SET STATISTICS IO, TIME ON
SET SHOWPLAN ON
-- Report all Products with Duration of Status 2
-- Subquery

SELECT  p.ProductId,
        --p.Description,
        StartDateTime = ps.DateTime,
        EndDateTime   = (
        SELECT ISNULL( MIN( DateTime ), "2079.06.06" ) 
            FROM ProductStatus subquery 
            WHERE ProductId = p.ProductId 
            AND DateTime > ps.DateTime 
            )
    FROM  Product       p,
          ProductStatus ps
    WHERE p.ProductId = ps.ProductId
    AND   ps.StatusCode = 2

----------------
-- Query Plan --
----------------

QUERY PLAN FOR STATEMENT 2 (at line 5).


    STEP 1
        The type of query is SELECT.

	10 operator(s) under root

       |ROOT:EMIT Operator (VA = 10)
       |
       |   |SQFILTER Operator (VA = 9) has 2 children.
       |   |
       |   |   |NESTED LOOP JOIN Operator (VA = 2) (Join Type: Inner Join)
       |   |   |
       |   |   |   |SCAN Operator (VA = 0)
       |   |   |   |  FROM TABLE
       |   |   |   |  Product
       |   |   |   |  p
       |   |   |   |  Table Scan.
       |   |   |   |  Forward Scan.
       |   |   |   |  Positioning at start of table.
       |   |   |   |  Using I/O Size 8 Kbytes for data pages.
       |   |   |   |  With LRU Buffer Replacement Strategy for data pages.
       |   |   |
       |   |   |   |SCAN Operator (VA = 1)
       |   |   |   |  FROM TABLE
       |   |   |   |  ProductStatus
       |   |   |   |  ps
       |   |   |   |  Using Clustered Index.
       |   |   |   |  Index : UC_Prod_Dtm
       |   |   |   |  Forward Scan.
       |   |   |   |  Positioning by key.
       |   |   |   |  Keys are:
       |   |   |   |    ProductId ASC
       |   |   |   |  Using I/O Size 2 Kbytes for data pages.
       |   |   |   |  With LRU Buffer Replacement Strategy for data pages.
       |   |
       |   |  Run subquery 1 (at nesting level 1).
       |   |
       |   |  QUERY PLAN FOR SUBQUERY 1 (at nesting level 1 and at line 8).
       |   |
       |   |   Correlated Subquery.
       |   |   Subquery under an EXPRESSION predicate.
       |   |
       |   |   |SEQUENCER Operator (VA = 8) has 2 children.
       |   |   |
       |   |   |   |SCALAR AGGREGATE Operator (VA = 4)
       |   |   |   |  Evaluate Ungrouped MINIMUM AGGREGATE.
       |   |   |   |
       |   |   |   |   |SCAN Operator (VA = 3)
       |   |   |   |   |  FROM TABLE
       |   |   |   |   |  ProductStatus
       |   |   |   |   |  subquery
       |   |   |   |   |  Using Clustered Index.
       |   |   |   |   |  Index : UC_Prod_Dtm
       |   |   |   |   |  Forward Scan.
       |   |   |   |   |  Positioning by key.
       |   |   |   |   |  Keys are:
       |   |   |   |   |    ProductId ASC
       |   |   |   |   |    DateTime ASC
       |   |   |   |   |  Using I/O Size 2 Kbytes for data pages.
       |   |   |   |   |  With LRU Buffer Replacement Strategy for data pages.
       |   |   |
       |   |   |   |SCALAR AGGREGATE Operator (VA = 7)
       |   |   |   |  Evaluate Ungrouped ONCE AGGREGATE.
       |   |   |   |
       |   |   |   |   |RESTRICT Operator (VA = 6)(0)(0)(0)(3)(0)
       |   |   |   |   |
       |   |   |   |   |   |SCALAR Operator (VA = 5) 
       |   |
       |   |  END OF QUERY PLAN FOR SUBQUERY 1.

---------------------
-- Execution Stats --
---------------------

Parse and Compile Time 1.
Adaptive Server cpu time: 100 ms.
Total writes for this command: 0
 
Execution Time 0.
Adaptive Server cpu time: 0 ms.  Adaptive Server elapsed time: 0 ms.
Table: Product       (p)        scan count 1,      logical reads: (regular=2000   apf=0 total=2000),   physical reads: (regular=0 apf=0 total=0), apf IOs used=0
Table: ProductStatus (ps)       scan count 20000,  logical reads: (regular=97287  apf=0 total=97287),  physical reads: (regular=0 apf=0 total=0), apf IOs used=0
Table: ProductStatus (subquery) scan count 200000, logical reads: (regular=913894 apf=0 total=913894), physical reads: (regular=0 apf=0 total=0), apf IOs used=0
Total writes for this command: 0
 
Execution Time 83.
Adaptive Server cpu time: 8300 ms.  Adaptive Server elapsed time: 12760 ms.
Total writes for this command: 0