-- Flat

SELECT  p.ProductId, 
        p.Description, 
        ps.DateFrom, 
        ps.DateTo 
    FROM Product p, 
         ProductStatus2 ps 
    WHERE p.ProductId = ps.ProductId 
    AND   ps.StatusCode = 2 
    AND   ( SYSDATE BETWEEN ps.DateFrom 
        AND   ps.DateTo
        )

-- Report all Products with Current State of 2

SELECT  ProductId,
        Description
    FROM  Product       p,
          ProductStatus ps
    WHERE p.ProductId = ps.ProductId  -- Join
    AND   StatusCode  = 2             -- Request
    AND   DateTime    = (             -- Current Status on the left ...
        SELECT MAX( DateTime )        -- Current Status row for outer Product
            FROM  ProductStatus ps_inner
            WHERE p.ProductId = ps_inner.ProductId
            )

-- For a report of the duration that each Product has been in a particular State: the DateTimeFrom is an available column, and defines the horizontal cut-off, a subset (we can exclude earlier rows); the DateTimeTo is the earliest of the subset of Product States.

SELECT               ProductId,
                     Description,
        [DateFrom] = DateTime,
        [DateTo]   = (
        SELECT MIN( DateTime )                      -- earliest in subset
            FROM  ProductStatus ps_inner
            WHERE p.ProductId = ps_inner.ProductId  -- our Product
            AND   ps_inner.DateTime > ps.DateTime   -- defines subset, cutoff
            )
    FROM  Product       p,
          ProductStatus ps
    WHERE p.ProductId = ps.ProductId 
    AND   StatusCode  = 2             -- Request