What's the easiest way to select a single record/value from the n-th group? The group is determined by a material and it's price(prices can change). I need to find the first date of the last and the last date of the next to last material-price-groups. So i want to know when exactly a price changed.
I've tried following query to get the first date of the current(last) price which can return the wrong date if that price was used before:
DECLARE @material VARCHAR(20)
SET @material = '1271-4303'
SELECT TOP 1 Claim_Submitted_Date
FROM tabdata
WHERE Material = @material
AND Price = (SELECT TOP 1 Price FROM tabdata t2
WHERE Material = @material
ORDER BY Claim_Submitted_Date DESC)
ORDER BY Claim_Submitted_Date ASC
This also only returns the last, how do i get the previous? So the date when the previous price was used last/first?
I have simplified my schema and created this sql-fiddle with sample-data. Here in chronological order. So the row with ID=7 is what i need since it's has the next-to-last price with the latest date.
ID CLAIM_SUBMITTED_DATE MATERIAL PRICE
5 December, 04 2013 12:33:00+0000 1271-4303 20
4 December, 03 2013 12:33:00+0000 1271-4303 20 <-- current
3 November, 17 2013 10:13:00+0000 1271-4846 40
7 November, 08 2013 12:16:00+0000 1271-4303 18 <-- last(desired)
2 October, 17 2013 09:13:00+0000 1271-4303 18
1 September, 17 2013 08:13:00+0000 1271-4303 10
8 September, 16 2013 12:15:00+0000 1271-4303 17
6 June, 23 2013 14:22:00+0000 1271-4303 18
9 January, 11 2013 12:22:10+0000 1271-4303 20 <-- a problem since this is older than the desired but will be returned by my simply sub-query approach above
Is it even possible to parametrize this value, so the nthLatestPriceGroup
if i want to know the 3rd last price-date? Note that the query sits in a scalar-valued-function.
Edit: Many thanks to all. But unfortunately a simple ROW_NUMBER
seems not to help here since i'm trying to get the row with the most recent price before the current price for a given material. So GROUP BY
/PARTITION BY material,price
includes rows with the same price that don't belong to the last recent material-price group.
Consider that a price can change from
Date Price Comment
5 months ago 20 original price, note that this is the same as the curent which causes my query to fail!
3 months ago 18 price has changed, i might need the first and last date
2 months ago 20 price has changed, i might need the first and last date
1 month ago 18 previous price, i need the oldest and newest dates
NOW 20 current price, i need the first/oldest date from this group
So i want the date of the most recent row of the last 20-group, the oldest 20-group is irrelevant. So i must somehow group by consecutive prices since a price can repeat after it has already changed.
So actually i only need the most recent Claim_Submitted_Date
from the price-group that starts with 1 month ago ... previous price
in the list above which is the date until the previous price was valid. The other informations listed in the comments are just nice to have(the nthLatestPriceGroup
sub-question). That's the row with ID=7
in the sample data above. By the way, the oldest row of this price-group would be the one with ID=2
(October, 17) and not ID=6
(June, 23) even if the latter is older. There was a different price(10) after. That's the reason why i can't use simple ranking functions.
You will need to use the windowed function ROWNUMBER in a subquery,...
something like this will get you there:
ROW_NUMBER() OVER(PARTITION BY Price ORDER BY Claim_Submitted_Date DESC) AS Row
Here's the update based on your fiddle:
DECLARE @material VARCHAR(20)
SET @material = '1271-4303'
SELECT * FROM
(
SELECT *,
ROW_NUMBER() OVER(PARTITION BY Material ORDER BY Claim_Submitted_Date ASC) AS rn
FROM tabdata t2
WHERE Material = @material
) res
WHERE rn=2
If idData is incremental(and therefore chronological) you could use this:
SELECT * FROM
(
SELECT *,
ROW_NUMBER() OVER(PARTITION BY Material ORDER BY idData DESC) AS rn
FROM tabdata t2
WHERE Material = @material
) res
Looking at your latest requirements we could all be over thinking it(if I understand you correctly):
DECLARE @MATERIAL AS VARCHAR(9)
SET @MATERIAL = '1271-4303'
SELECT TOP 1 *
FROM tabdata t2
WHERE Material = @material
AND PRICE <> ( SELECT TOP 1 Price
FROM tabdata
WHERE Material = @material
ORDER BY CLAIM_SUBMITTED_DATE desc)
ORDER BY CLAIM_SUBMITTED_DATE desc
--results
idData Claim_Submitted_Date Material Price
7 2013-11-08 12:16:00.000 1271-4303 18
Here's a fiddle based on this.
Try this
DECLARE @material VARCHAR(20), @Nth INT
SET @material = '1271-4303'
SET @Nth = 2
;with CTE1 ([idData],[Claim_Submitted_Date], [Material], [Price], Rn)
as
(
SELECT *,
DENSE_RANK() OVER(ORDER BY PRICE DESC) AS rn
FROM tabdata
WHERE Material = @material
)
,
CTE2 ([idData], [Material], [Price], LastDate)
AS(
SELECT [idData], [Material], [Price], MAX([Claim_Submitted_Date])
FROM CTE1
WHERE rn = @Nth
GROUP BY [idData], [Material], [Price]
)
SELECT Top 1 [idData], [Material], [Price], LastDate
FROM CTE2
ORDER BY LastDate DESC
Result Set
idData Material Price LastDate
7 1271-4303 18 2013-11-08 12:16:00.000
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With