Retrieve closest upper values

Question

Hi and thanks in advance for your time.

I have to solve this specific problem. and I have following table of stamps

idStamp     idCustomer  uptoW       uptoD       price
----------- ----------- ----------- ----------- -----------
218         4           2200        690         205
218         4           1700        660         155        

I need to find a single row (if exist) that match 4 criteria: - idStamp - idCustomer - uptoW - uptoD

I have used

SELECT Distinct(UptoW) FROM [mytable]
WHERE uptoW=(SELECT MIN(uptoW)
FROM [mytable]
WHERE uptoW >= 1600-1 AND idStamp = 218 AND idCustomer = 4)

and it works for 3 criteria - idStamp - idCustomer - uptoW

But I can't figure how to implement the fourth matching criteria.

summarizing
Using following param (idStamp=218, idCustomer=4, Width=1600, Depth=640)
I expect to find the row with price 155
...but...
using param (idStamp=218, idCustomer=4, Width=1600, Depth=670)
I expect to find the row with price 205
If theres' no way to satisfy all criteria, I expect no rows.

-- edit 2013.12.13 --

idStamp     idCustomer  UptoW       UptoD       price      
----------- ----------- ----------- ----------- -----------
218         4           220         69          155        
218         4           170         100         205        
218         4           230         100         400        
218         4           180         90          345        
218         4           180         89          34         
218         4           179         90          32         
218         4           179         89          2343       

DECLARE @p_idStamp INT = 218
,@p_idCustomer INT = 4
,@UptoW INT = 160
,@UptoD INT = 89
,@UptoWmin int
,@UptoDmin int;

SELECT @UptoWmin = MIN(UptoW) FROM mytable<br>
WHERE UptoW =(SELECT MIN(UptoW) FROM mytable<br>
WHERE UptoW >= @UptoW-1 AND idStamp = 218 AND idCustomer = 4)<br>

SELECT @UptoDmin = MIN(UptoD) FROM mytable<br>
WHERE UptoD =(SELECT MIN(UptoD) FROM mytable<br>
WHERE UptoD >= @UptoD-1 AND idStamp = 218 AND idCustomer = 4)<br>

SELECT TOP(1) * FROM mytable<br>
WHERE UptoW >= @UptoWmin AND UptoD >= @UptoDmin<br>
ORDER BY UptoW, UptoD<br>

SELECT TOP(1) * FROM mytable<br>
WHERE UptoW >= @UptoWmin AND UptoD >= @UptoDmin<br>
ORDER BY UptoD, UptoW<br>

I have probably found a solution.
As you can see I first search the exact min values in the table then I compose a query that satisfy both params. I repeat the query inverting the order by clause, becouse results can differ.

Let me know if better solution can be adopted.

Answer 1

I believe one way of doing what you want is ordering the results by the exceeding area, and then taking the first row.

Something like that:

-- Test schema, based on S Koppenol answer:
CREATE TABLE stamps (
    [idStamp]    INT,
    [idCustomer] INT,
    [uptoW]      INT,
    [uptoD]      INT,
    [price]      MONEY
)

INSERT INTO stamps (
    [idStamp], [idCustomer], [uptoW], [uptoD], [price]
) VALUES
    (218, 4, 2200, 690, 205.0),
    (218, 4, 1700, 660, 155.0)
GO

-- Procedure to find the desired row:
CREATE PROCEDURE GetRow(
    @idStamp    INT,
    @idCustomer INT,
    @uptoW      INT,
    @uptoD      INT
) AS
BEGIN
    SELECT TOP 1
        T.idStamp,
        T.idCustomer,
        T.uptoW,
        T.uptoD,
        (T.uptoW * T.uptoD - @uptoW * @uptoD) AS DELTA_AREA
    FROM
        stamps T
    WHERE
        T.uptoW       >= @uptoW + 1
        AND T.uptoD   >= @uptoD + 1
        AND idStamp    = @idStamp
        AND idCustomer = @idCustomer
    ORDER BY
        DELTA_AREA
END
GO

-- Testing  
EXEC GetRow 218, 4, 1600, 640; 
EXEC GetRow 218, 4, 1700, 640;
EXEC GetRow 218, 4, 1600, 660;
EXEC GetRow 218, 4, 2200, 660;

And the testing results:

idStamp     idCustomer  uptoW       uptoD       DELTA_AREA
----------- ----------- ----------- ----------- -----------
218         4           1700        660         98000

(1 row(s) affected)

idStamp     idCustomer  uptoW       uptoD       DELTA_AREA
----------- ----------- ----------- ----------- -----------
218         4           2200        690         430000

(1 row(s) affected)

idStamp     idCustomer  uptoW       uptoD       DELTA_AREA
----------- ----------- ----------- ----------- -----------
218         4           2200        690         462000

(1 row(s) affected)

idStamp     idCustomer  uptoW       uptoD       DELTA_AREA
----------- ----------- ----------- ----------- -----------

(0 row(s) affected)

That way you'll surely get a record that is not only inside the requested limit, but also the nearest.

Answer 2

I'm not entirely sure about the desired end result, but I think this will come close. This will give you the record with the lowest uptoW (if any) that matches all criteria.

DECLARE @p_idStamp INT = 218
,@p_idCustomer INT = 4
,@p_uptoW INT = 1600
,@p_uptoD INT = 640;

SELECT TOP 1 *
FROM mytable
WHERE idStamp = @p_idStamp
AND idCustomer = @p_idCustomer
AND uptoW > @p_uptoW
AND uptoD > @p_uptoD
ORDER BY uptoW ASC;

Answer 3

You could add uptoD >= DESIRED_VALUE to the validation.

For example:

SELECT Distinct(UptoW) FROM [stamps]
WHERE uptoW=(SELECT MIN(uptoW)
FROM [stamps]
WHERE uptoW >= 1600-1 AND uptoD >= 640 AND idStamp = 218 AND idCustomer = 4);

This would give you:

UPTOW
1700

But you could simplify further and do:

SELECT MIN(uptoW) AS UPTOW
FROM [stamps]
WHERE uptoW >= 1600-1 
  AND uptoD >= 640 
  AND idStamp = 218 
  AND idCustomer = 4

sqlfiddle demo