steven
asked on
How to find the nearest values in a Sql table 2012 R2
We have a warehouse that has many locations in the database. The locations are varchar values as below:
10A1,10A2,10A3,10A4,10A5,1 0A6,10A7,1 0A8,10B1,1 0B2,10B3,1 0B4,10B5,1 0B6 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.
10A1,10A2,10A3,10A4,10A5,1
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.
Both lag and lead allow for a "distance" (in rows), so you could even create columns with 1, 2, 3 ... previous or following rows.
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.
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.
ASKER
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
9J8
9J7
9J6
9J5
9J4
10A5
10A4
10A3
10A2
10A1
we needed 10a6 and up
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'),('10A 2','pa1'), ('10A3','p a1'),('10A 4','pa1'), ('10A5','p a1'),('10A 6','pa1'), ('10A7','p a1'),('10A 8','pa1'), ('10B1','p a1'),('10B 2','pa1'), ('10B3','p a1'),('10B 4','pa1'), ('10B5','p a1'),('10B 6','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.lo c, 3, 1)) -
ASCII(SUBSTRING(@loc, 3, 1))) AS low_char3_diff,
ABS(ASCII(SUBSTRING(high.l oc, 3, 1)) -
ASCII(SUBSTRING(@loc, 3, 1))) AS high_char3_diff
) AS comparisons_02
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'),('10A
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.lo
ASCII(SUBSTRING(@loc, 3, 1))) AS low_char3_diff,
ABS(ASCII(SUBSTRING(high.l
ASCII(SUBSTRING(@loc, 3, 1))) AS high_char3_diff
) AS comparisons_02
>>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.
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.
ASKER
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
locations.xlsx
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?
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?
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?
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?
ASKER
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.
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".
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".
ASKER
Ok then thanks
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks to all.
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:
Open in new window