sql - How to generate 5 digit increment number based on Zone? -

- 

 out put : if north "n00001                                         n00002                         if south "s00001                                         s00002

here tried code

  declare @zone varchar(20),@zoneid int,@id varchar(10)   set @zone = 's'   select @zoneid = cast(isnull(max(cast(replace(idno,@zone,'') numeric))+1,'00000') varchar) memberprofiles left(idno,1) = @zone  set @id = @zone+cast(@zoneid varchar)     select @id

but every time getting "s1" need "s00001"

how can generate zone wise number generation

this question "closed duplicate" candidate. but, there several flaws, think it's worth answer:

  1. in database seem have column "idno" leading character marking zone. if true, should - if ever possible - change design. number , zone mark should reside in 2 columns. combination of them presentation issue

  2. your left(idno,1) perform badly (read "sargability"). if there's index on idno, should better off idno ' + @zone + '%'

  3. are sure, in "memberprofiles" there's 1 row where clause true? if not, number expect in "@zoneid" after select?

  4. your cast(isnull(max(cast(replace(idno,@zone,'') numeric))+1,'00000') ... replaces leading "s" nothing, hoping there number left. you'll highest number (ok, answers point 3, still - uhm - hacky), still expect "null" you'd return "00000". cries better design loudly :-)

  5. you should try "set based" thinking, rather "procedural" thinking...

try this

create table #memberprofile(idno varchar(100),othercolumn varchar(100)); insert #memberprofile values('n3','test north 3'),('s24','test south 24'),('n14','test north 14')  declare @zone varchar(20)='n';  select *       ,zonecode + replace(str(number,5),' ','0') yournewpaddedcode     #memberprofile cross apply (     select left(idno,1) zonecode           ,cast(substring(idno,2,1000) int) number  ) idno_in_parts zonecode=@zone; go  --clean --drop table #memberprofile

the result

idno    othercolumn  zonecode   number  yournewpaddedcode n3      test north 3    n        3      n00003 n14     test north 14   n       14      n00014

Comments

Post a Comment

Popular posts from this blog

javascript - Laravel datatable invalid JSON response -

-
everything work fine on code below fetch user logs: js: <script> $(document).ready(function () { var table =$('#systemlogs').datatable({ responsive: true, processing: true, serverside: true, "language": { "url": "/datatables/media/plug-in/persian.json" }, ajax: '{!! url('/admin/systemlogs/data/systemlogsdatatable') !!}', columns: [ { data: 'name', name: 'name' }, { data: 'message_text', name: 'message_text' }, { data: 'remote_addr', name: 'remote_addr' }, { data: 'log_created_at', name: 'management_logs.created_at' }, { data: 'log_updated_at', name: 'management_logs.updated_at' }, ] }); }); </script> routes: route::get('/systemlogs/data/systemlogsdatatable','systemlogscontrolle...
Read more

java - Exception in thread "main" org.springframework.context.ApplicationContextException: Unable to start embedded container; -

-
i getting error when run spring application: exception in thread "main" org.springframework.context.applicationcontextexception: unable start embedded container; nested exception org.springframework.context.applicationcontextexception: unable start embeddedwebapplicationcontext due missing embeddedservletcontainerfactory bean. @ org.springframework.boot.context.embedded.embeddedwebapplicationcontext.onrefresh(embeddedwebapplicationcontext.java:124) @ org.springframework.context.support.abstractapplicationcontext.refresh(abstractapplicationcontext.java:476) @ org.springframework.boot.context.embedded.embeddedwebapplicationcontext.refresh(embeddedwebapplicationcontext.java:109) @ org.springframework.boot.springapplication.refresh(springapplication.java:691) @ org.springframework.boot.springapplication.run(springapplication.java:320) @ org.springframework.boot.springapplication.run(springapplication.java:952) @ org.springframework.boot.springa...
Read more

sql server 2008 - My Sql Code Get An Error Of Msg 245, Level 16, State 1, Line 1 Conversion failed when converting the varchar value '8:45 AM' to data type int -

-
msg 245, level 16, state 1, line 1 conversion failed when converting varchar value '8:45 am' data type int. script declare @specimenid bigint = 0, @script varchar(max); declare @starttime int = (select convert(int,substring(start_time,0,charindex(':', start_time)) - 12) appt appt._id=112601) --select @starttime set @script = 'select distinct ptlast_name + '', '' + pt.first_name patient_name, dbo.formatdate(pt.dob,''mm/dd/yyyy'') dob, (convert(varchar, (datediff(yy, dob, appt_date) - case when (month(dob) > month(appt_date)) or (month(dob) = month(appt_date) , day(dob) > day(appt_date)) 1 else 0 end)) + ''/'' + convert(varchar, (datediff(m, dateadd(yy, (datediff(yy, dob, appt_date) - case when (month(dob) > month(appt_date)) or (month(dob) = month(appt_date) , day(dob) > day(appt_date)) 1 else 0 end), d...
Read more