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