x

Subbquery Maximum Dates

I am trying to select in a join/subquery the last time an item had an increased price.

this is somewhat of a challenge for me - can anyone lend a hand?

create table #temp (

item varchar(5), 
price money,
prc_eff_dt datetime,
prc_end_dt datetime )

insert into #temp values('1234',' $10.00  ',' 1/1/2009','1/8/2009')
insert into #temp values('1234',' $11.00  ',' 1/9/2009','1/16/2009')
insert into #temp values('1234',' $12.00  ',' 1/17/2009','1/24/2009')
insert into #temp values('1234',' $13.00  ',' 1/25/2009','2/1/2009')
insert into #temp values('1234',' $14.00  ',' 2/2/2009','2/9/2009')
insert into #temp values('1234',' $15.00  ',' 2/10/2009','2/17/2009')
insert into #temp values('1234',' $14.00  ',' 2/18/2009','2/25/2009')
insert into #temp values('1234',' $13.00  ',' 2/26/2009','3/5/2009')
insert into #temp values('1234',' $12.00  ',' 3/6/2009','3/13/2009')
insert into #temp values('1234',' $11.00  ',' 3/14/2009','3/21/2009')
insert into #temp values('1234',' $10.00  ',' 3/22/2009','3/29/2009')
insert into #temp values('1234',' $9.00  ',' 3/30/2009','4/6/2009')
insert into #temp values('1234',' $8.00  ',' 4/7/2009','4/14/2009')
insert into #temp values('1234',' $7.00  ',' 4/15/2009','4/22/2009')
insert into #temp values('5678',' $10.00  ',' 1/1/2009','1/8/2009')
insert into #temp values('5678',' $11.00  ',' 1/9/2009','1/16/2009')
insert into #temp values('5678',' $12.00  ',' 1/17/2009','1/24/2009')
insert into #temp values('5678',' $13.00  ',' 1/25/2009','2/1/2009')
insert into #temp values('5678',' $14.00  ',' 2/2/2009','2/9/2009')
insert into #temp values('5678',' $15.00  ',' 2/10/2009','2/17/2009')
insert into #temp values('5678',' $14.00  ',' 2/18/2009','2/25/2009')
insert into #temp values('5678',' $13.00  ',' 2/26/2009','3/5/2009')
insert into #temp values('5678',' $12.00  ',' 3/6/2009','3/13/2009')
insert into #temp values('5678',' $11.00  ',' 3/14/2009','3/21/2009')
insert into #temp values('5678',' $10.00  ',' 3/22/2009','3/29/2009')
insert into #temp values('5678',' $9.00  ',' 3/30/2009','4/6/2009')
insert into #temp values('5678',' $8.00  ',' 4/7/2009','4/14/2009')
insert into #temp values('5678',' $7.00  ',' 4/15/2009','4/22/2009')

select * from #temp as all_prc
left join #temp as inc_dt
on all_prc.item = inc_dt.item
and all_prc.prc_eff_dt = (select(prc_eff_dt) from #temp
    having  max(price))
more ▼

asked Mar 25 '11 at 11:58 AM in Default

siera_gld gravatar image

siera_gld
1k 74 80 83

(comments are locked)
10|1200 characters needed characters left

6 answers: sort voted first

Ok, here are my takes, pretty similar. First one is valid if prc_eff_dt is always 1 day after the last prc_end_dt for the same item:

SELECT [priceRises].[item], MAX([prc_eff_dt]) AS LastPriceRiseDate FROM (
    SELECT [newPrices].[item],
           [newPrices].[prc_eff_dt]
      FROM #temp AS [newPrices] LEFT OUTER JOIN
           #temp AS [oldPrices]
        ON [oldPrices].[item] = [newPrices].[item]
       AND [oldPrices].[prc_end_dt] = DATEADD(d, -1, [newPrices].[prc_eff_dt])
     WHERE [newPrices].[price] > [oldPrices].[price]
) priceRises
GROUP BY [priceRises].[item];

And the second, which does away with that assumption by projecting a sequence over them:

WITH priceSequence (item, price, effectiveDate, sequenceNumber) AS
(SELECT [item], [price], [prc_eff_dt], ROW_NUMBER() OVER (PARTITION BY [item] ORDER BY [prc_eff_dt]) FROM #temp)
SELECT [priceRises].[item], MAX([effectiveDate]) AS LastPriceRiseDate FROM (
    SELECT [newPrices].[item],
           [newPrices].[effectiveDate]
      FROM priceSequence AS [newPrices] LEFT OUTER JOIN
           priceSequence AS [oldPrices]
        ON [oldPrices].[item] = [newPrices].[item]
       AND [oldPrices].[sequenceNumber] = [newPrices].[sequenceNumber] - 1
     WHERE [newPrices].[price] > [oldPrices].[price]
) priceRises
GROUP BY [priceRises].[item];
more ▼

answered Mar 26 '11 at 07:21 AM

Matt Whitfield gravatar image

Matt Whitfield ♦♦
29.4k 61 65 87

Hmm. I forgot to document that assumption!
Mar 26 '11 at 07:27 AM ThomasRushton ♦
(comments are locked)
10|1200 characters needed characters left

Your title and your actual question seem to be a little at odds. If you want the row with the highest price for that item, then this should work:

select * 
from #temp t1
where not exists
(select * 
from #temp t2
where t1.item = t2.item
and t1.price < t2.price)

If you want the last time the price was increased then you may want something like:

with cte1 as (
--This will return every time there is a price increase

    select * 
    from #temp t1
    where exists 
    --make sure this is an increase
    --so the very last one to happen has to 
    (
       select * 
       from #temp t2
       where t1.item = t2.item
       and t1.price > t2.price
       and t1.prc_eff_dt > t2.prc_eff_dt 
       --can't be anything in between
       and not exists 
       (
         select *
         from #temp t3
         where t3.item = t1.item
         and t3.prc_eff_dt  < t1.prc_eff_dt  and 
          t3.prc_eff_dt  > t2.prc_eff_dt 
          )
    )
)

--This gets the latest price increase
select * 
from cte1 t1
where not exists
(select * 
from cte1 t2
where t1.item = t2.item
and t1.prc_eff_dt < t2.prc_eff_dt)
Of course, in the sample data you provided, these will each return exactly the same results.
more ▼

answered Mar 25 '11 at 12:55 PM

TimothyAWiseman gravatar image

TimothyAWiseman
15.5k 19 22 32

(comments are locked)
10|1200 characters needed characters left

This seems to me to be the sort of thing where a nice handy ROW_NUMBER partitioned function would come in handy:

Replace your SELECT statement with the following:

;WITH OrderedPrice AS (    
SELECT item, price, prc_eff_dt, prc_end_dt, 
       ROW_NUMBER() OVER(PARTITION BY item ORDER BY price desc) AS RowNum
FROM #temp)
SELECT item, price, prc_eff_dt, prc_end_dt FROM OrderedPrice WHERE RowNum = 1

will return the following:

item   price  prc_eff_dt               prc_end_dt
1234   15.00  2009-02-10 00:00:00.000  2009-02-17 00:00:00.000
5678   15.00  2009-02-10 00:00:00.000  2009-02-17 00:00:00.000

If, on the other hand, you want the most recent set of prices, then you would replace the

...PARTITION BY item ORDER BY price DESC...

with

....PARTITION BY item ORDER BY prc_eff_dt DESC...

oh, and if you want the most recent maximum price (in case something has had two goes at the highest price), then you would do:

....PARTITION BY item ORDER BY price DESC, prc_eff_dt DESC...

OK, so as you want the most recent price rise, then things are a little more complicated.

;WITH OrderedPrice AS (    
SELECT item, price, prc_eff_dt, prc_end_dt, ROW_NUMBER() OVER(PARTITION by item ORDER BY prc_eff_dt desc) AS RowNum
FROM #temp),
PriceHikes AS (
SELECT op1.item, op1.price, op1.prc_eff_dt, op1.prc_end_dt, ROW_NUMBER() OVER(PARTITION BY op1.item ORDER BY op1.prc_eff_dt DESC) AS RowNum
FROM OrderedPrice op1 LEFT JOIN OrderedPrice op2 ON op1.item = op2.item AND op1.prc_eff_dt = DATEADD(DAY, 1, op2.prc_end_dt)
WHERE op1.price > op2.price
)
SELECT item, price, prc_eff_dt, prc_end_dt
FROM [PriceHikes]
WHERE RowNum = 1

NB: Any errors are entirely due to lack of sleep; however, I believe this does show the way forward for your problem...

Also, it's worth noting that given your sample data, this returns the same set of records as the query that returns the highest prices...
more ▼

answered Mar 25 '11 at 01:24 PM

ThomasRushton gravatar image

ThomasRushton ♦
33.4k 14 20 44

(comments are locked)
10|1200 characters needed characters left

This will give you the record for the highest price, but that doesn't mean that it's the last time the price was raised.

;with cte
AS
(
    select 
    item,
    maxPrice = MAX(Price)
    from #temp
    group by item
)
select t.* from cte c
inner join #temp t
on c.item=t.item
and c.maxPrice=t.price

The second example will give you the last time the price was updated, but I am not sure it's the most efficient, but at least it's not using any cursor.

;with cte1
AS
(
    select 
    *,
    rownum = row_number() over(partition by item order by prc_eff_dt)
    from #temp
), cte2
as(
select t.item, maxrownum= max(t.rownum) from cte1 c
inner join cte1 t
    on c.item = t.item
    and c.rownum +1= t.rownum 
    where c.price <t.price
group by t.item
)
select c1.* from cte1 c1 inner join cte2 c2
on c1.item=c2.item
and c1.rownum=c2.maxrownum
more ▼

answered Mar 25 '11 at 12:57 PM

Håkan Winther gravatar image

Håkan Winther
15.5k 33 37 48

Hakan - you are correct - i do need the last time the price was raised, not necessarily the max price.

the other challenge i am facing in this example - i can have multiple date entries with the same price. so item prc date 1234 10 1/1/10 1234 11 1/2/10 1234 11 1/9/10 1234 11 1/31/10 this query is nested and the standard for this company seems to be indexed temp tables vs cte's
Mar 25 '11 at 01:37 PM siera_gld
(comments are locked)
10|1200 characters needed characters left

Hakan - you are correct - i do need the last time the price was raised, not necessarily the max price.

the other challenge i am facing in this example - i can have multiple date entries with the same price. so

item prc date 
1234 10 1/1/10 
1234 11 1/2/10 
1234 11 1/9/10 
1234 11 1/31/10 

this query is nested and the standard for this company seems to be indexed temp tables vs cte's

more ▼

answered Mar 25 '11 at 01:38 PM

siera_gld gravatar image

siera_gld
1k 74 80 83

My second query should be able to handle dates with the same price, because it only selects records where the next record in the date sequence have a higher price. When the price is the same, the record will be excluded. My code is neat, but I am not sure about the performance impact on large datasets. If you try it, let me know how it performs.
Mar 25 '11 at 11:39 PM Håkan Winther
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x111
x18
x11

asked: Mar 25 '11 at 11:58 AM

Seen: 1079 times

Last Updated: Mar 25 '11 at 01:27 PM