Iterative query without using CTE (or cursors!)

Question

I have some data in a table that looks similar to this:

Item    Date         Price
A       8/29/2012    $3
B       8/29/2012    $23
C       8/29/2012    $10
A       8/30/2012    $4
B       8/30/2012    $25
C       8/30/2012    $11
A       8/31/2012    $3
B       8/31/2012    $22
C       8/31/2012    $8
A       9/01/2012    $3
B       9/01/2012    $26
C       9/01/2012    $9
A       9/02/2012    $3
B       9/02/2012    $24
C       9/02/2012    $9

I need to write a query that identifies that price for A hasn't changed since 8/30/2012, price for item C hasn't changed since 9/01/2012, and to return the number of days elapsed for both (we're looking for items with non-moving prices). I can't use CTE, or cursors, or separately created temp tables (select into, etc) because of the limitations of the web report tool this sql needs to run in. I can only use a 'basic' single pass select query (subqueries will work though). Does anyone have any cunning ideas on how to achieve this??

My first attempt was to group by Item and Price, where price is the same as the latest price, having count > 2, identifying the min date and doing a datediff between min date and getdate. However this just identifies the first instance of that price, it doesn't take into account any subsequent rows that might have a different price. Hope that makes sense.

Answer 1

I've given both types of ages and account for items that exist only once in the table (they don't have an old date). Please let us know how close this is.

UPDATE: I had to correct the date calculation in "PriceAgeToNow", and I've also tried to filter out records that are have only had a new price for 1 day. Here is the SQL Fiddle.

-- Get the age of the current price
select *
    , datediff(d, c.OldDate, getdate()) as PriceAgeToNow
    , datediff(d, c.OldDate, c.NewDate) as PriceAgeToNewestDate
from (
    select *
        -- Get max date of former price
        , isnull(
            (select max(Date) from PricingTable where Item = b.Item and Date < b.NewDate and Price != b.NewPrice), b.NewDate
        ) as OldDate
    from (
        -- Get current price
        select *
            , (select Price from PricingTable where Item = a.Item and Date = a.NewDate) as NewPrice
        from (
            -- Get current date
            select Item
                , max(Date) as NewDate
            from PricingTable
            group by Item
        ) a
    ) b
) c
-- Attempt to filter out price changes that have only lasted 1 day
where datediff(d, c.OldDate, c.NewDate) > 1

Answer 2

I have a preference for using windows functions where they are most appropriate. And, in this case, there is an interesting trick for finding sequences of things that are the same. Enumerate them (with row_number) over all the data, partitioned by item and ordered by date. And then enumerate them, over all the data, partitioned by item and price and ordered by date.

For A, you would get

A 8/29 $3 1 1
A 8/30 $4 2 1
A 8/31 $3 3 2
A 9/1  $3 4 3
A 9/3  $3 5 4

The difference between the last two columns is constant, for any sequence of prices that remain constant in the data. We can then use this to find when that sequence begins. The following query takes this approach:

select item, price, (id_seqnum - ipd_seqnum) as groupid, min(date) as mindate
from (select p.*,
             row_number() over (partition by item order by date) id_seqnum,
             row_number() over (partition by item, price order by date) as ipd_seqnum,
             max(date) over (partition by item) as maxdate
      from prices p
     ) t
group by item, price, (id_seqnum - ipd_seqnum)
having max(date) = max(maxdate)

It also find the maximum date for each item, and then chooses the grouping that has the maximum date.