Auto increment function in SQL Server

ASP Free Forums Sponsor:

January 11th, 2010, 02:37 AM

papegowda_bj

Registered User

Join Date: Nov 2009

Posts: 12

Time spent in forums: 2 h 13 m 37 sec
Reputation Power: 0

Query - General - Auto increment function in SQL Server

Auto increment function in SQL Server

I had a table named as “Asset_Register” and it contains following columns
• STATUS
• JURISD
• SAM
• FHNUM
• FHTXT

I need to update the FHNUM column using Auto increment function based on SAM, JURISED and STATUS columns. When ever the new record is inserted or if any changes are made in the SAM, JURISED and STATUS columns.

The table looks like:

STATUS JURISD SAM FHNUM FHTXT
1 530 55 1 055001
0 530 55 0 NULL
1 430 55 0 NULL
1 530 55 2 055002
1 530 55 120 055120
1 530 55 75 055075
1 530 55 0 NULL
1 530 55 0 NULL
1 530 65 0 NULL
0 530 55 0 NULL

The function should select the records if it meets the following condition
(FHNUM IS NULL OR FHNUM = 0) And (STATUS = 1 OR STATUS = 2) AND JURISD = 530.

Example:
STATUS JURISD SAM FHNUM FHTXT
1 530 55 0 NULL
1 530 55 0 NULL
1 530 65 0 NULL

And get max value from the FHNUM based on the SAM field and assign the next number to FHNUM.

Example:
• The 120 value is the highest in ‘SAM’ 55. So the selected records should assign with the next value i.e. 121
• The ‘SAM’ 65 does not contain only one record in it so it should assign with 1

Can any one please help?

January 14th, 2010, 02:51 PM

June7

Moderator

Join Date: Apr 2009

Location: The Great Land

Posts: 3,989

Time spent in forums: 1 Month 2 Weeks 4 Days 23 h 21 m 7 sec
Reputation Power: 634

You have the logic described, attempt code and test it. If you have problems, post specific question and the code for analysis.

Please clarify statement:
"The ‘SAM’ 65 does not contain only one record in it so it should assign with 1"
I do see only one record for SAM 65.

April 27th, 2010, 07:33 AM

sakthi.tnj

Contributing User

Join Date: Sep 2009

Posts: 39

Time spent in forums: 16 h 27 m 6 sec
Reputation Power: 8

Re: Auto increment function in SQL Server

Hi,

I hope that the below SQL query will be useful for you

Code:


Declare @FHNUM int , @jursid int
set @jursid = 530
If Exists(select sam from test2 where jursid  = @Jursid and [Sam] in (select MAX(Sam)from test2 where (FHNUM  is NULL or FHNUM =0)and ([Status]=1 or [status] = 2)and Jursid = @Jursid) group by Sam having COUNT(sam)>1)
     begin
		Set @FHNUM = (Select MAX(FHNUM) from test2 where jursid =@Jursid and [Sam] in (select sam from test2 where jursid  = @Jursid and (FHNUM is not NULL and FHNUM != 0) and [Sam] in (select MAX(Sam)from test2 where (FHNUM  is NULL or FHNUM =0)and ([Status]=1 or [status] = 2)and Jursid = @Jursid)))
		update top(1) test2 set FHNUM = @FHNUM+1 where [Sam] in (select top 1 sam from test2 where jursid  = @Jursid and (FHNUM is  NULL or FHNUM = 0)and [Sam] in (select MAX(Sam)from test2 where (FHNUM  is NULL or FHNUM =0)and ([Status]=1 or [status] = 2)and Jursid = @Jursid))
and (FHNUM is  NULL or FHNUM = 0) and ([Status]=1 or [status] = 2)

       end
else
      begin
		update test2 set FHNUM = 1 where [Sam] in (select sam from test2 where jursid  = @Jursid and [Sam] in (select MAX(Sam)from test2 where (FHNUM  is NULL or FHNUM =0)and ([Status]=1 or [status] = 2)and Jursid = @Jursid))
      end

Note: Test2 is a table Name

Thanks & Regards
SakthiMeenakshi.S

Quote:

Originally Posted by papegowda_bj