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 GMAdjPayment
-------------- ----------- ---------------------------------
6.00 172715 13171314.36900
7.00 200200 18200299.00500
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.
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