April 15, 2009 at 6:19 pm
Guys,
I have a table in which the sequence is populated from the application and not from the identity property of the SQL Server. For example
Empid LastName
1 smith
2 doe
4 bell
For a given input of empid I want to find out what numbers are missing for empid in the above example if I provide the range of empid between 1 and 4, the missing empid should be 3. Is there any effective way to build a function to find out missing number in sequence column
Any suggestion and inputs would help
Thanks
April 15, 2009 at 8:19 pm
create a table valued function for the sequence number and select from the sequence generator by doing aleft join
CREATE FUNCTION [dbo].[fSequenceGenerator]
(
@StartSequence INT,
@EndSequence INT
)
RETURNS @OutPutTable TABLE
(
SequenceNumber INT
)
AS
BEGIN
WHILE @StartSequence < = @EndSequence
BEGIN
INSERT INTO @OutPutTable(SequenceNumber)
SELECT @StartSequence
SET @StartSequence = @StartSequence + 1
END
RETURN
END
--Now select the sequence Number that is missing by doing a left join
select
X.SequenceNumber
from
[dbo].[fSequenceGenerator](@startSeq,@endSeq) X
left join
@table a
on a.empid = X.SequenceNumber
where a.empid is null
April 15, 2009 at 10:47 pm
Select N from (Select Row_Number() Over (Order By Object_ID,Column_ID) N from sys.columns) tblTally
where N Not in (Select EMPID from tblEMP )
AND N
between 1 and 15 -- Range
The concept is from the Tally Table.
http://www.sqlservercentral.com/articles/TSQL/62867/
Artif Sheikh
April 15, 2009 at 11:21 pm
When I query this against my database I get only 14997 from sys.columns, what If my empid is 15000? my seq generation fails in this scenario. Is there a better way of doing this?
Sys.Columns returns a row for each column of an object that has columns, such as views or tables.
April 16, 2009 at 12:18 am
Select N from (Select Row_Number() Over (Order By a.Object_ID,a.Column_ID) N
from sys.columns a
Cross Join sys.columns b
Cross Join sys.columns c
) tblTally
where N Not in (Select Empid from tblEMP )
AND N
between 1 and 15 --- Range
As I said earliar, the concept is from Tally Table. It can be created by many ways, on the fly.
Atif SHeikh
April 16, 2009 at 12:47 am
Thank you that was useful.
April 20, 2009 at 5:46 am
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply