question

siera_gld avatar image
siera_gld asked

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))
joinssubqueryouter-join
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Matt Whitfield avatar image
Matt Whitfield answered
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];
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

ThomasRushton avatar image ThomasRushton ♦♦ commented ·
Hmm. I forgot to document that assumption!
0 Likes 0 ·
TimothyAWiseman avatar image
TimothyAWiseman answered
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.
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Håkan Winther avatar image
Håkan Winther answered
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
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

siera_gld avatar image siera_gld commented ·
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
0 Likes 0 ·
ThomasRushton avatar image
ThomasRushton answered
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...
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

siera_gld avatar image
siera_gld answered
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
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Håkan Winther avatar image Håkan Winther commented ·
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.
0 Likes 0 ·
siera_gld avatar image
siera_gld answered
here is my attempt - all in a join condition - i cannnot get the partition to work in a subquery LEFT JOIN GEPRS_PRICE.dbo.T_PRC last_inc ON ref.EM_ITEM_NUM = last_inc.EM_ITEM_NUM AND bsppt.PRC_TYP_ID = last_inc.PRC_TYP_ID AND last_inc.PRC_CURR_CD IN ('C', 'H') AND last_inc.PRC = (SELECT max(max_prc.PRC) FROM GEPRS_PRICE.dbo.T_PRC max_prc WHERE last_inc.EM_ITEM_NUM = max_prc.EM_ITEM_NUM AND last_inc.PRC_TYP_ID = max_prc.PRC_TYP_ID AND max_prc.PRC_CURR_CD IN ('C', 'H') ) AND last_inc.PRC_EFF_DT = (SELECT MIN(max_prc.PRC_EFF_DT) FROM GEPRS_PRICE.dbo.T_PRC max_prc WHERE last_inc.EM_ITEM_NUM = max_prc.EM_ITEM_NUM AND last_inc.PRC_TYP_ID = max_prc.PRC_TYP_ID AND max_prc.PRC_CURR_CD IN ('C', 'H') )
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.