January 7, 2015 at 12:55 pm
Hi,
I’m trying to write a query that will select closest value to #TestValue.Amount without go over #SM.GMAdj value
Outcome of the query should be something like
PercRate Amount Code GMAdj Payment
-------------- ----------- ---------------------------------
6.00 172715 13 171314.36 900
7.00 200200 18 200299.00 500
Thank you for your help
Tables are:
SELECT PercRate, Amount, Code
INTO #TestValues
FROM (
SELECT 6.00, 172715, 13 UNION ALL
SELECT 6.50, 172716, 13 UNION ALL
SELECT 7.00, 181351, 13 UNION ALL
SELECT 7.50, 190419, 13 UNION ALL
SELECT 6.00, 200000, 18 UNION ALL
SELECT 6.50, 200100, 18 UNION ALL
SELECT 7.00, 200200, 18 UNION ALL
SELECT 7.50, 200300, 18 UNION ALL
SELECT 6.00, 0, 21 UNION ALL
SELECT 6.50, 1, 21 UNION ALL
) d ( PercRate, Amount, Code);
SELECT GMAdj, Payment, Code
INTO #SM
FROM (
SELECT 171314.36, 909, 13 UNION ALL
SELECT 200299, 500, 18
) d ( GMAdj, Payment, Code);
SELECT * from #TestValues
SELECT * from #SM
January 7, 2015 at 2:00 pm
Solved
January 14, 2015 at 4:04 pm
How did you solve it? Might help someone else that reads this thread.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
January 27, 2015 at 9:13 pm
Try the following Script
SELECT PercRate, Amount, Code
INTO #TestValues
FROM (
SELECT 6.00 PercRate , 172715 Amount , 13 Code UNION ALL
SELECT 6.50, 172716, 13 UNION ALL
SELECT 7.00, 181351, 13 UNION ALL
SELECT 7.50, 190419, 13 UNION ALL
SELECT 6.00, 200000, 18 UNION ALL
SELECT 6.50, 200100, 18 UNION ALL
SELECT 7.00, 200200, 18 UNION ALL
SELECT 7.50, 200300, 18 UNION ALL
SELECT 6.00, 0, 21 UNION ALL
SELECT 6.50, 1, 21
) d
SELECT GMAdj, Payment, Code
INTO #SM
FROM (
SELECT 171314.36, 909, 13 UNION ALL
SELECT 200299, 500, 18
) d ( GMAdj, Payment, Code);
SELECT
PercRate, Amount, Code,GMAdj,Payment
FROM
(
SELECT
PercRate, Amount, #TestValues.Code,GMAdj,Payment,ROW_NUMBER() OVER(Partition By GMAdj Order by ABS((GMAdj-Amount)) ASC) AS RID
from
#TestValues
INNER JOIN #SM ON #SM.Code = #TestValues.Code
)X
WHERE RID = 1
DROP TABLE #TestValues
DROP TABLE #SM
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply