Building Sequence no

Question

Post reply

Building Sequence no

RinoaHeartily

Old Hand

Points: 320
More actions
April 26, 2004 at 1:53 am

#111058

Can soemone show me how to do this in an UDF.

say i want to have a list of records such as these...
Cust No   Tel No     Seq No
100          1234      1
100          1235      2
200          3333      1
200          2233      2
200          2345      3
300          4444      1

The sequence will be for a group of same cust no but unique Tel No and will recycle back to 1 on the next group of cust no..

Pls help me!! Thanks

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Frank Kalis SSC Guru Points: 111183 More actions · Answer 1

Quick and dirty, this should work

select Cust_No, Tel_No,

(SELECT COUNT(*)

FROM

WHERE Cust_No = s.Cust_No

AND Tel_No <= s.Tel_No) AS Seq_No

FROM s

This might also be interesting

http://support.microsoft.com/default.aspx?scid=http://support.microsoft.com:80/support/kb/articles/q186/1/33.asp&NoWebContent=1

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

RinoaHeartily Old Hand Points: 320 More actions · Answer 2

RinoaHeartily

Old Hand

Points: 320

April 26, 2004 at 2:35 am

#504196

hey hey.. it works. Thanks man!!

roberthamilton Ten Centuries Points: 1002 More actions · Answer 3

I'd suggest modifying as follows :

select Cust_No, Tel_No,

(SELECT COUNT(*) + 1

FROM

WHERE Cust_No = s.Cust_No

AND Tel_No s.Tel_No) AS Seq_No

FROM s

This will give the next available sequence number, rather than the last used, and will allow for telephone numbers which are not always added in ascending order

Coach James Ten Centuries Points: 1272 More actions · Answer 4

/* CODE BETTER, FUNCTION BETTER */

-- Your code should read like it runs

-- FIRST make sure you have an index like

CREATE INDEX <tablename>_001 ON <tablename> (Cus_No,Tel_No);

-- SECOND create UDF(from above examples SP's)

-- to use for calculating your Seq_No

-- for each new record as they are added

CREATE FUNCTION getSeq(@Cus_No integer, @Tel_No integer)

RETURNS integer

AS

BEGIN

DECLARE @Seq_No integer

SELECT @Seq_No = count(*)

FROM <tablename> (NOLOCK INDEX=<tablename>_001)

WHERE Cus_No = @Cus_No

AND Tel_No <= @Tel_No

RETURN @Seq_No

END

-- Ha, now you can use it in your query:

SELECT *, getSeq(Cus_No,Tel_No) FROM <tablename>

-- or use this function as a

-- YES! a computed column to your <tablename>

-- Alter table with a calculated column

ALTER TABLE <tablename>

ADD Seq_No AS getSeq (Cus_No,Tel_No)

Regards,

Coach James

Frank Kalis SSC Guru Points: 111183 More actions · Answer 5

Just out of curiosity, James.

Why a UDF?

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Coach James Ten Centuries Points: 1272 More actions · Answer 6

I'm suggesting a UDF as CALCULATED COLUMN

--- see last line ---

Regards,

Coach James