Trying to Fill in Missing Sequence

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 2008 Forums

Transact-SQL (2008)

Trying to Fill in Missing Sequence

Author

Topic

bconner
Starting Member

48 Posts

Posted - 2010-08-17 : 11:55:56

I have a table that store Accession data by Division
I am looking for a Query that will Show the Missing Number sequence by Division like below. Any help is greatly appreciated....

Division Accession Prefix Number Suffix

Dallas Lab NT10-101712-CV NT10 101710 CV
101711
101712
Dallas Lab NT10-101713-CV NT10 101713 CV
Dallas Lab NT10-101714-CV NT10 101714 CV
Dallas Lab NT10-101715-CV NT10 101715 CV
Dallas Lab NT10-101716-CV NT10 101716 CV
Dallas Lab NT10-101717-CV NT10 101717 CV
Dallas Lab NT10-101718-CV NT10 101718 CV
101719
101720
101721
101722
101723
101724
Dallas Lab NT10-101719-CV NT10 101725 CV
Dallas Lab ST10-2475-PS ST10 2475 PS
2476
2477
2478
2479
2480
2481
2482
2483
2484
2485
2486
Dallas Lab ST10-2487-PS ST10 2487 PS
2488
2489
2490
2491
2492
2493
2494
2495
2496
2497
Dallas Lab ST10-2498-PS ST10 2498 PS
Dallas Lab ST10-2499-PS ST10 2499 PS
Dallas Lab ST10-2500-PS ST10 2500 PS

Brian

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-08-17 : 12:00:49

sorry didnt get your output format. how does it have only single value in some rows? all others are blank or NULL?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

MSquared
Yak Posting Veteran

52 Posts

Posted - 2010-08-17 : 12:57:32

You can use a tally table. I've attached the code for creating a tally table on the fly, but it will probably be faster if it already exists.

declare @t table (division varchar(400), Div_Num int)

insert into @t
select 'Dallas Lab NT10', 101710 union all
select 'Dallas Lab NT10', 101713 union all
select 'Dallas Lab NT10', 101714 union all
select 'Dallas Lab NT10', 101715 union all
select 'Dallas Lab NT10', 101716 union all
select 'Dallas Lab NT10', 101717 union all
select 'Dallas Lab NT10', 101718 union all
select 'Dallas Lab ST10', 2475 union all
select 'Dallas Lab ST10', 2487 union all
select 'Dallas Lab ST10', 2498 union all
select 'Dallas Lab ST10', 2499 union all
select 'Dallas Lab ST10', 2500

Declare @tally table (i int)

;WITH tenRows AS (
SELECT i
FROM (
SELECT 1 AS [1], 2 AS [2], 3 AS [3], 4 AS [4], 5 AS [5],
6 AS [6], 7 AS [7], 8 AS

, 9 AS [9], 10 AS [10]
) AS p
UNPIVOT (i FOR numbers IN ([1],[2],[3],[4],[5],[6],[7],

,[9],[10]) ) as unpvt
),
thousandRows AS (
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) as n
FROM tenRows AS a
CROSS JOIN tenRows AS b
CROSS JOIN tenRows AS c
) ,
millionRows AS (
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS n
FROM thousandRows AS a
CROSS JOIN thousandRows AS b
)

insert into @tally
select n from millionRows
where n < (select Max(Div_num) from @t)

select c.Division, b.i Div_Num
from @tally b
inner join (select Division, min(Div_Num) MinDivNum, max(Div_Num) MaxDivNum
from @t
group by Division) a
on b.i between a.MinDivNum and a.MaxDivNum
left outer join @t c
on b.i = c.Div_Num

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources