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.