Geek asks and answers

Iterative query without using CTE (or cursors!)

I have some data in a table that looks something like this:

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

I need to write a query that identifies that the price for A has not changed since 08/30/2012, the price for item C has not changed since 01/09/2012, and return the number of days that have passed for both (we are looking for items with non-moving prices). I cannot use CTE, or cursors, or separately created temporary tables (select, etc.) due to the limitations of the web report tool that sql must execute. I can only use the "basic" request for selecting a single pass (subqueries will work, though). Does anyone have any clever ideas on how to achieve this?

, Item Price, , , having count > 2, . , . , .

+5
3

, ( ). , , .

: "PriceAgeToNow", , 1 . 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
+2

* : *

?

SELECT ct.Item,
    ct.Price,
    datediff(day, max(yt.[Date]), ct.[Date]) AS ChangeDays
FROM
    (SELECT Item, max(Date) as LastDate FROM YourTable GROUP BY Item) maxdata
    INNER JOIN YourTable ct on ct.Item = maxdata.Item and ct.[Date] = maxdata.LastDate
    INNER JOIN YourTable yt on yt.Item = ct.Item and yt.[Date] < ct.[Date] and yt.Price <> ct.Price
GROUP BY ct.Item, ct.Price, ct.[Date]
+2

Windows, . , . ( row_number) , . , .

A

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

, , . , , . :

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)

, .

+2

More articles:


All Articles