user2772056 user2772056 - 19 days ago 8
SQL Question

return the closest value to value being searched

enter code here
I need to pair vehicles by type and then secondly by fuel consumption. the fuel consumptions may not match and in that event the closest is chosen. the same vehicle cannot be used to pair more than once. example of the type of data i'm talking about is below

create table #table1
(
vehicleid varchar(2),
typed varchar(5),
fuelconsumption int
)


create table #table2
(
vehicleid varchar(2),
typed varchar(5),
fuelconsumption int
)


INSERT INTO #table1 VALUES('x1','car',5);
INSERT INTO #table1 VALUES('x2','car',4);
INSERT INTO #table1 VALUES('x3','car',8);

INSERT INTO #table2 VALUES('b1','car',7);
INSERT INTO #table2 VALUES('b2','car',8);
INSERT INTO #table2 VALUES('b3','car',9);
INSERT INTO #table2 VALUES('b4','car',10);
INSERT INTO #table2 VALUES('b5','car',11);
INSERT INTO #table2 VALUES('b6','truck',15);
INSERT INTO #table2 VALUES('b7','truck',4);


which will return output like this:
enter image description here

Answer

While this may not be a full answer (still working out the details), if you start with this you will get a list of best matching vehicle IDs in ascending order:

    SELECT baseID = ibase.vehicleid,
           matchID = im.vehicleid,
           rowNum = ROW_NUMBER()
                    OVER (PARTITION BY ibase.vehicleid
                          ORDER BY ABS(ibase.fuelconsumption - im.fuelconsumption)) 
      FROM #table1 ibase
             INNER JOIN #table2 im ON ibase.typed = im.typed 

From here you should be able to loop through picking up "first" instances of the "b" vehicles

Full answer (note: I loathe cursors, but this works):

DROP TABLE #results
CREATE TABLE #results ( vehicleid1 VARCHAR(2), vehicleid2 VARCHAR(2), diff INT, rownum INT )

INSERT INTO #results ( vehicleid1, vehicleid2, diff, rownum ) 
SELECT baseID = ibase.vehicleid,
       matchID = im.vehicleid,
       diff = ABS(ibase.fuelconsumption - im.fuelconsumption),
       rowNum = ROW_NUMBER()
                    OVER (
                        PARTITION BY ibase.vehicleid
                        ORDER BY ABS(ibase.fuelconsumption - im.fuelconsumption))
  FROM #table1 ibase
       INNER JOIN #table2 im ON ibase.typed = im.typed 

-- The cursor ensures we get the real best match so we do not accidentally
-- assign a best match which is a _better_ match for another vehicle
DECLARE CUR_ CURSOR FOR SELECT vehicleid1 FROM #results ORDER BY diff
DECLARE @id VARCHAR(2)

OPEN CUR_
FETCH CUR_ INTO @id
WHILE @@FETCH_STATUS = 0
BEGIN

    -- Remove all matches other than the BEST match
    DELETE r
      FROM #results r
     WHERE r.vehicleid1 = @id
       AND r.rownum <> (SELECT MIN(ir.rownum) FROM #results ir WHERE ir.vehicleid1 = @id)

    -- Remove all other matches which use the remaining matched vehicle 
    DELETE r
      FROM #results r
     WHERE r.vehicleid1 <> @id
       AND r.vehicleid2 = (SELECT ir.vehicleid2 FROM #results ir where ir.vehicleid1 = @id)

    FETCH CUR_ INTO @id
END
CLOSE CUR_
DEALLOCATE CUR_

SELECT * FROM #results

Non-cursor solution:

DROP TABLE #results
CREATE TABLE #results ( vehicleid1 VARCHAR(2), vehicleid2 VARCHAR(2), diff INT, rownum INT )

INSERT INTO #results ( vehicleid1, vehicleid2, diff, rownum ) 
SELECT baseID = ibase.vehicleid,
       matchID = im.vehicleid,
       diff = ABS(ibase.fuelconsumption - im.fuelconsumption),
       rowNum = ROW_NUMBER()
                    OVER (
                        PARTITION BY ibase.vehicleid
                        ORDER BY ABS(ibase.fuelconsumption - im.fuelconsumption))
  FROM #table1 ibase
       INNER JOIN #table2 im ON ibase.typed = im.typed 

DECLARE @id VARCHAR(2)
WHILE EXISTS( SELECT vehicleid1 FROM #results GROUP BY vehicleid1 HAVING COUNT(*) > 1 )
BEGIN

    SELECT TOP 1 
           @id = r.vehicleid1
      FROM #results r
            INNER JOIN (SELECT i.vehicleid1 FROM #results i GROUP BY i.vehicleid1 HAVING COUNT(*) > 1) i
              ON r.vehicleid1 = i.vehicleid1
     ORDER BY r.diff

    -- Remove all matches other than the BEST match
    DELETE r
      FROM #results r
     WHERE r.vehicleid1 = @id
       AND r.rownum <> (SELECT MIN(ir.rownum) FROM #results ir WHERE ir.vehicleid1 = @id)

    -- Remove all other matches which use the remaining matched vehicle 
    DELETE r
      FROM #results r
     WHERE r.vehicleid1 <> @id
       AND r.vehicleid2 = (SELECT ir.vehicleid2 FROM #results ir where ir.vehicleid1 = @id)

END

SELECT * FROM #results
Comments