How to find the nearest values in a Sql table   2012 R2

steven
steven used Ask the Experts™
on
We have a warehouse that has many locations in the database.  The locations are varchar values as below:
10A1,10A2,10A3,10A4,10A5,10A6,10A7,10A8,10B1,10B2,10B3,10B4,10B5,10B6  ETC.

We want to find the nearest location to say 10A7, we started with the below but it doesn't work:



declare @loc nvarchar(20)
set @loc = '10A7'


SELECT TOP 5 loc  
         FROM   location_mst
              where   loc <= @loc
             and description = 'pa1'
         --ORDER  BY  loc ASC
             union all
            SELECT TOP 5  loc
         FROM   location_mst
           where   loc >= @loc
              and description = 'pa1'
         ORDER  BY loc DESC


Does anyone know of a good way to get the results, we are looking for.  The table houses about 3500 locations all the same naming convention.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
How do you define "nearest"?

From the SQL you posted, it looks like you want the 5 before and 5 after.  You say it didn't work but didn't say what was wrong with it.

What are you looking to get?

You can easily get the one before and after with:
with cte as (
	select
		loc,
		lag(loc) over(order by loc asc) prev_loc,
		lead(loc) over(order by loc asc) next_loc
	from location_mst
)
select loc, prev_loc, next_loc
from cte where loc='10A7'
;

Open in new window

Qlemo"Batchelor", Developer and EE Topic Advisor
Top Expert 2015

Commented:
Both lag and lead allow for a "distance" (in rows), so you could even create columns with 1, 2, 3 ... previous or following rows.
Scott PletcherSenior DBA
Most Valuable Expert 2018
Top Expert 2014

Commented:
Comparison code is going to be very complex -- and thus, time consuming -- on its own.

What is the relative difference between A7 and A8 vs between A8 and B1?  
What is the relative difference between A and B and 10 and 11?

To me, the easiest way would be to pre-calculate each location's relative location, then you could simply take the lowest ABS difference from the adjoining locations.  

Another way would be pre-calculate a weight of each element of the locations -- the first 2, the middle byte 3, and the last byte.  Then again use a calc at comparison time to get the smallest absolute difference.  

If you can answer the initial qs, I can work up code to compute a difference real-time, but it would perform somewhat slower (at least) than pre-computed values.
Become a Certified Penetration Testing Engineer

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

stevendeveloper

Author

Commented:
We need to find the closest location that has no qty.   The query didn't work because it pulled the below


9J8
9J7
9J6
9J5
9J4
10A5
10A4
10A3
10A2
10A1
we needed 10a6 and up
Scott PletcherSenior DBA
Most Valuable Expert 2018
Top Expert 2014

Commented:
Here's the start of the code, but at this point I'm not sure how to weight differences and each of the elements of the loc.

Btw, if the loc is always 4 standard chars, why is the definition of the column given as nvarchar(20) rather than char(4)??


DROP TABLE IF EXISTS dbo.location_mst;

CREATE TABLE dbo.location_mst ( loc nvarchar(20) NOT NULL PRIMARY KEY, description varchar(1000) NULL);
INSERT INTO dbo.location_mst
VALUES('10A1','pa1'),('10A2','pa1'),('10A3','pa1'),('10A4','pa1'),('10A5','pa1'),('10A6','pa1'),('10A7','pa1'),('10A8','pa1'),('10B1','pa1'),('10B2','pa1'),('10B3','pa1'),('10B4','pa1'),('10B5','pa1'),('10B6','pa1');

DECLARE @loc nvarchar(20)
SET @loc = '10A7'

SELECT *
FROM (
    SELECT TOP (1) loc
    FROM location_mst
    WHERE  loc <= @loc
       AND description = 'pa1'
    ORDER BY loc DESC
) AS low
CROSS JOIN (
    SELECT TOP (1) loc  
    FROM location_mst
    WHERE  loc > @loc
       AND description = 'pa1'
    ORDER BY loc
) AS high
CROSS APPLY (
    SELECT
        ABS(CAST(LEFT(low.loc, 2) AS tinyint) - CAST(LEFT(@loc, 2) AS tinyint)) AS low_left2_diff,
        ABS(CAST(LEFT(high.loc, 2) AS tinyint) - CAST(LEFT(@loc, 2) AS tinyint)) AS high_left2_diff
) AS comparisons_01
CROSS APPLY (
    SELECT
        ABS(ASCII(SUBSTRING(low.loc, 3, 1)) -
            ASCII(SUBSTRING(@loc, 3, 1))) AS low_char3_diff,
        ABS(ASCII(SUBSTRING(high.loc, 3, 1)) -
            ASCII(SUBSTRING(@loc, 3, 1))) AS high_char3_diff
) AS comparisons_02
Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
>>We need to find the closest location that has no qty.   The query didn't work because it pulled the below


Sorry but that doesn't mean anything to me.  I don't see a qty in the original question and I don't know the difference between 10A5 and 10A4.

>>we needed 10a6 and up

How do you define this?  You know your data and what it means.  We don't.
stevendeveloper

Author

Commented:
So there are location in our warehouse each location identified (attached).   We wish to find a empty location closest to next location we need to pick from.  The warehouse is filled with cantilever racking and each location is in the database as the attached. Does this help?
locations.xlsx
Scott PletcherSenior DBA
Most Valuable Expert 2018
Top Expert 2014

Commented:
No.  

Is 10B1 closer to 10A8 or 10B2?

Is 10J7 closer to 10J8 or 11A1?

You have to realize that we have NO knowledge of your warehouse or your data.  How could we possibly have any?
Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
What Scot said:  No.

You now mention "empty location" which I assume goes with the qty comment above.  I still don't see anything like that in your data.

As far as location, I assume your warehouse is similar to shelving in Sams Club or Costco.

10A1 is aisle 10 Rack A shelf 1 for example.

What if the "closest" empty location is across the aisle in Aisle 9 assuming 9A1 is directly across the aisle?  How do you plan on taking that into account?
stevendeveloper

Author

Commented:
I wish we did that but we don't we start from 3A1 (beginning of the warehouse)  so it should group by number, letter, number.
Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
That doesn't help the unanswered questions so I don't see how we can help with the SQL other than what we already posted.

You know your data and what you are asking may seem obvious to you but we know nothing about what you are talking about.

To me it sounds like "It's simple.  Take an orange, add three protons and two neutrons, expose to 10 rads of radiation for 8 microseconds and you can easily see, it becomes a Poodle".
stevendeveloper

Author

Commented:
Ok then thanks
Top Expert 2016
Commented:
let's say you have a aisles of racks (10-100)
Each aisle has a set of racks (A-Z)
Each rack has a slot 1-50
Create a table that is a 3x3 matrix that maps the aisle/rack/slot #
First search the rack to see if it has any open slots if so return the empty slot or you could check for all empty slots and use this as your starting point.
you may want to display a map of open slots as sometimes a visual search of a map can be faster than a computer search.

if your aisles are long then it may be better to search the aisle first.
Add 1 to the starting aisle location .. no empty slots
subtract 1 from the starting aisle location ..no empty slot
increment the search to add/subtract one aisle until you find an empty slot

The problem is that A1Z may be closer to C2Z than to B1A
stevendeveloper

Author

Commented:
Thanks to all.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial