Maybe one of you SQL gurus can help me out
-
I need to query a list of numbers and find out how much space is between them. e.g.:
1
10, 8 blanks
12, 1 blank
17, 4 blanks
They are changing our Account numbers at work from numbers with implied meaning to just straight numbers from here on out, but they don't want to renumber existing accounts, so they asked me to find an unused block of numbers, and I'm not coming up with a good way to do it just in T-SQL.
-
-- Prepare sample data
DECLARE @Sample TABLE (Number INT)
INSERT @Sample
SELECT 1 UNION ALL
SELECT 10 UNION ALL
SELECT 12 UNION ALL
SELECT 17
-- Try 1
SELECT LowLimit.Number + 1 AS FromNumber,
(
SELECT TOP 1 Number
FROM @Sample AS HighLimit
WHERE NOT EXISTS (
SELECT B.Number
FROM @Sample AS b
WHERE HighLimit.Number - 1 = B.Number
 
AND HighLimit.Number > LowLimit.Number
ORDER BY Number
 
- 1 AS ToIDFROM @Sample AS LowLimit
WHERE NOT EXISTS (
SELECT b.Number
FROM @Sample AS b
WHERE LowLimit.Number + 1 = B.Number
 
AND LowLimit.Number < (SELECT MAX(Number) FROM @Sample)
ORDER BY LowLimit.Number
-- Try 2
SELECT s.Number,
s.Number - (SELECT MAX(z.Number) FROM @Sample AS z WHERE z.Number < s.Number) - 1
FROM @Sample AS s
N 56°04'39.16"
E 12°55'05.25" -
Or this:
drop table MyTable
create table MyTable
( myid int
)
go
insert mytable select 1
insert mytable select 10
insert mytable select 12
insert mytable select 17
insert mytable select 21
select a.MyID
, (b.myid - a.myid - 1) 'blanks'
from MyTable a, MyTable B
where b.MyID = (select min(myid)
from MyTable c
where c.MyID > a.MyID)
blanks offset by 1, so 1 has 8 following blanks, 10 has 1, etc.
-
Sean
Something like this? Not tested, so syntax may not be perfect.
CREATE TABLE #Gaps (RowNo int IDENTITY(1,1) PRIMARY KEY CLUSTERED,
AccNo int, Blanks int)
INSERT INTO #Gaps (AccNo) SELECT AccNo FROM YourTable
UPDATE g1
SET Blanks = g1.AccNo - g2.AccNo - 1
FROM #Gaps g1 JOIN #Gaps g2
ON g1.RowNo = g2.RowNo - 1
SELECT AccNo, Blanks
FROM #Gaps
ORDER BY Blanks DESC
John
-
I was going to suggest John's method, however I'd recommend that you probably want to make sure that on the insert you use an ORDER BY clause to make sure that they're put in the correct ascending order.
-
Thanks guys, I did it John's way, with the added Order By Aaron suggested, and I changed this one line;
SET Blanks = g1.AccNo - g2.AccNo - 1
to
SET Blanks = Abs(g1.AccNo - g2.AccNo + 1)
to get a positive number of blanks.
Thanks alot everybody.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply