Auto Increment Sql Query [Resolved]

Posted by Ankitsrist under Sql Server on 1/30/2014 | Points: 10 | Views : 931 | Status : [Member] | Replies : 3
hello,
i am working on case monitoring system in which i need to make query which will automatic generate case no. +1 to the previous case entered. Please have a look on my query

  select  'A/' +right(cast(year(GETDATE()) as CHAR(4)), 2)+'/'+ + cast(max(right((CaseNo), 2) )+1 as varchar(50))as caseno from tbl_RecordRequisition



it is working fine but after case no. 100 it is not incrementing to 101.

please help




Responses

Posted by: Bandi on: 1/30/2014 [Member] [MVP] Platinum | Points: 50

Up
0
Down

Resolved
Assumed that Case Number format as A/XX/ + CaseNo. So higjlighted part is of 5 length. Take STUFF( CaseNo, 1, 5, '') and then convert it to integer. Later increment it by +1

select  'A/'+right(cast(year(GETDATE()) as CHAR(4)), 2)+'/'+ 
cast(
max( cast(stuff(CaseNo, 1, 5, '') as int) )+1
as varchar(50))as caseno from tbl_RecordRequisition


Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Ankitsrist, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Ankitsrist on: 1/31/2014 [Member] Starter | Points: 25

Up
0
Down
Oh wow it's done.....thanks a lot :)

Ankitsrist, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Bandi on: 1/31/2014 [Member] [MVP] Platinum | Points: 25

Up
0
Down
Welcome :)

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Ankitsrist, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response