Auto Increment Sql Query [Resolved]

Posted by Ankitsrist under Sql Server on 1/30/2014 | Points: 10 | Views : 1158 | Status : [Member] | Replies : 3

Write New Post |

Search Forums | Answered

Resolved Posts |

Un Answered Posts |

Forums Home

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

[Resolved]

Reply | Reply with Attachment

Alert Moderator

Responses

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

0	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

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

0	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

0	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

Latest Posts