January 4, 2017 at 3:37 pm
Given the following table and data:
declare @t table
(
FeeTable int,
OffSet int,
Amount decimal(13,2)
);
insert @t (FeeTable, OffSet, Amount) values
(10,4,20.00),
(10,3,65.00),
(10,2,90.00),
(10,1,135.00),
(10,0,175.00),
(11,4,10.00),
(11,3,25.00),
(11,2,30.00),
(11,1,435.00),
(11,0,575.00);
It is trivial to return the correct row when the offset that is passed in is between 0 and 4 for a given FeeTable. I am having a hard time trying to return the largest offset in the range when the passed in offset is > 4.
Thanks!
__________________________________________________________________________________________________________
How to Post to get the most: http://www.sqlservercentral.com/articles/Best+Practices/61537/
January 5, 2017 at 8:43 am
I'm taking a guess here, not sure if I completely understand the question, but if you just want for one specific FeeTable:
SELECT TOP 1 OffSet, Amount
FROM @t
WHERE FeeTable = 10
AND OffSet <= 5
ORDER BY OffSet DESC;
for all FeeTables:
WITH getrank AS
(SELECT FeeTable, OffSet, Amount, ROW_NUMBER() OVER (PARTITION BY FeeTable ORDER BY Offset DESC) AS ranking
FROM @t
WHERE OffSet <= 5)
SELECT FeeTable, OffSet, Amount
FROM getrank WHERE ranking = 1;
January 5, 2017 at 1:04 pm
Chris Harshman (1/5/2017)
I'm taking a guess here, not sure if I completely understand the question, but if you just want for one specific FeeTable:
SELECT TOP 1 OffSet, Amount
FROM @t
WHERE FeeTable = 10
AND OffSet <= 5
ORDER BY OffSet DESC;
That works well. I was missing the TOP and ORDER By clause!
Thank-you!
__________________________________________________________________________________________________________
How to Post to get the most: http://www.sqlservercentral.com/articles/Best+Practices/61537/
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply