Old Mares - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Old Mares

Author

Topic

elwoos
Master Smack Fu Yak Hacker

2052 Posts

Posted - 2006-09-13 : 06:37:23

I'm sure some of this must have been covered elsewhere but haven't found anything (yet!) that will do what I want. This is the old looking for gaps in a sequence stuff.

We have data of the form

annnn-yy

Where 'a' is 1-3 letters which determines the type of data and 'yy' is the last two digits of any given year and 'nnnn' is a 4 digit numerical sequence which is different for each type of data so for example we could have.

a0002-06
bc0123-05
bc0002-06
etc.

In addition the numerical sequence restarts when the yy changes
so on 31 Dec 2005 we may have

a4023-05

and then the next in that sequence would be
a0001-06 on 1st Jan 2006

There are two aspects to this really, the first is the normal question of finding gaps in the numerical sequence i.e. the nnnn

BUT (and this I suppose is the optional extra that gets the bonus points). The second question is (is it even possible!) to calculate any missing gaps between an arbitrary start code and an arbitrary end code

For example

If we have the data

a4320-05
a4321-05
a4323-05
a0002-06

then the results would be
a4322-05 and
a0001-06

Many thanks in advance

steve

-----------

Don't worry head. The computer will do all the thinking from now on.

Kristen
Test

22859 Posts

Posted - 2006-09-13 : 07:50:51

So: you gonna post some DDL and sample data before we all start writing that in unison!?

Ta

Kristen

nr
SQLTeam MVY

12543 Posts

Posted - 2006-09-13 : 07:53:36

v2000 or v2005

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

nr
SQLTeam MVY

12543 Posts

Posted - 2006-09-13 : 08:00:01

in v2000
select yr.yr + ints.i
from
(select i = right('000' + convert(varchar(3),i1.i + i2.i + i3.i + i4.i + i5.i + i6.i + i7.i + i8.i),3)
from
(select i = 0 union select 1) as i1 ,
(select i = 0 union select 2) as i2 ,
(select i = 0 union select 4) as i3 ,
(select i = 0 union select 8) as i4 ,
(select i = 0 union select 16) as i5 ,
(select i = 0 union select 32) as i6
(select i = 0 union select 64) as i7
(select i = 0 union select 128) as i8
) as ints
join (select yr = '-05' union all select '-07') yr
left outer join mytbl on yr.yr + ints.i = right(mytbl.id,len(mytbl.id) - patindex('%[0-9]%',mytbl.id + 1)
where mytbl.id is null
and ints.i <= '060' and ints.i >= '003'
order by ints.i

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-09-13 : 10:05:26

[code]-- Prepare test data
declare @test table (data varchar(20))

insert @test
select 'a4320-05' union all
select 'a4321-05' union all
select 'a4323-05' union all
select 'a0002-06' union all
select 'bc0123-05' union all
select 'bc0002-06'

-- do the work
select a.p + a.n + '-' + a.y Missing
from (
select c.p,
s.n,
c.y
from (
select distinct left(data, patindex('%[0-9]%', data) - 1) p,
right(data, len(data)- charindex('-', data)) y
from @test
) c
cross join (
select distinct right('0000' + cast(100 * m1.number + m2.number + 1 as varchar), 4) n
from master..spt_values m1
cross join master..spt_values m2
where m1.number between 0 and 99
and m2.number between 0 and 99
and 100 * m1.number + m2.number < 9999
) s
) a
left join (
select left(data, patindex('%[0-9]%', data) - 1) p,
max(substring(data, patindex('%[0-9]%', data), charindex('-', data) - patindex('%[0-9]%', data))) n,
right(data, len(data)- charindex('-', data)) y
from @test
group by left(data, patindex('%[0-9]%', data) - 1),
right(data, len(data)- charindex('-', data))
) o on o.p = a.p and o.y = a.y
where o.n > a.n
and o.n <> a.n
order by 1[/code]

Peter Larsson
Helsingborg, Sweden

elwoos
Master Smack Fu Yak Hacker

2052 Posts

Posted - 2006-09-14 : 03:44:07

quote:
v2000 or v2005

erm - 7!!

Actually (thankfully) I can use 2000 as that is where our reporting is done from now.

NR for some reason I had difficulty getting yours to work, I'm sure the fault is mine.

Peso, many thanks for your efforts, that works fine - I even understand some of it!

Thanks to all

steve

-----------

Don't worry head. The computer will do all the thinking from now on.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources