January 8, 2015 at 12:02 pm
Hi,
I’m trying to write a query that will select closest value to Amount field in #TestValue 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 8, 2015 at 1:46 pm
legeboka (1/8/2015)
Hi,I’m trying to write a query that will select closest value to Amount field in #TestValue 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
I don't understand. You say "without go over #SM.GMAdj value" but one of your result lines is
6.00 ----172715 -----13 -------171314.36 ------900
and 172715 is clearly over 171214.36 .
Maybe if the #SM.GMAdj is over all Amounts in the #TestValues table the the row with the smallest Amount is to be selected although it is over the GMAdj value ? ??
Tom
January 8, 2015 at 3:54 pm
SELECT sm.*, tv_lookup.Amount AS tv_Amount, tv_lookup.PercRate AS tv_PercRate, tv_lookup.Code AS tv_Code
FROM #SM sm
OUTER APPLY (
SELECT TOP (1) *
FROM #TestValues tv
WHERE
tv.Amount <= sm.GMAdj
ORDER BY tv.Amount DESC
) AS tv_lookup
SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply