'SDAAAKLL001' expected result

Question

I want, before inserting into table #Containerno ,check Contno against (Level_Four_ID and Loc_ID column) ,if value exist against (Level_Four_ID and Loc_ID column) then add 1 else SDAAAKLL001,from table #Containerno

create table  #ConCatagory (Cat_ID int ,Cat_Name varchar(50),Alias_Name  varchar(50)) 

 Insert into #ConCatagory values (1,'Akber','AAA')


  create table  #tbl_location (Loc_ID int ,Loc_Name varchar(50),Alias_Name  varchar(50)) 

 Insert into #tbl_location values (47,'Internal','SD')

 create table  #tbl_Account_L_Four (Level_Four_ID int ,Level_Four_Name varchar(50),Sup_code  varchar(50),Cat_ID int) 

 Insert into #tbl_Account_L_Four values (2111006  ,'Kend Lal','KLL',1)

 Create table #Containerno  (CID int,Contno varchar(50),Level_Four_ID int,Loc_ID int)

 Insert into #Containerno values (1111,'SDAAAKLL001',2111006  ,47)

Output

SDAAAKLL001
SD =will take from #tbl_location column Alias_Name.
AAA = will take from #ConCatagory column Alias_Name
KLL =will take from tbl_Account_L_Four column Sup_code
001 = will be auto generated

I tried below query which is not giving me output when i pass Loc_ID parameter

 select concat(c.Alias_Name,t.Sup_code,  L.Alias_Name,
 format(isnull(cast(substring(n.Contno,  len(c.Alias_Name) +len(t.Sup_code) + len(L.Alias_Name) + 1, len(n.Contno)) as int), 0) + 1, '000')) as Barcode
 from #tbl_Account_L_Four t
 left outer join #ConCatagory c on c.Cat_ID = t.Cat_ID
 left outer join #Containerno n on n.Level_Four_ID = t.Level_Four_ID 
 left outer  join #tbl_location L on L.Loc_ID=n.Loc_ID and n.Contno like concat( c.Alias_Name,t.Sup_code, L.Alias_Name, '[0-11]%') 

 where t.Level_Four_ID =  2111006  and L.Loc_ID=47

Answer 1

Check one of possible solutions:

select 
   concat(p, format(isnull(max(try_cast(stuff(n.Contno, 1, len(p), '') as int)), 0) + 1, '000')) as NewContno
from #ConCatagory c
inner join #tbl_Account_L_Four a on a.Cat_ID = c.Cat_ID
cross join #tbl_location L
cross apply (values (concat(L.Alias_Name, c.Alias_Name, a.Sup_code))) P(p)
left join #Containerno n on n.Level_Four_ID = a.Level_Four_ID and n.Loc_ID = L.Loc_ID and left(n.Contno, len(p)) = p
where a.Level_Four_ID = 2111006 and L.Loc_ID = 47
group by p

Answer 2

Hi @Analyst_SQL
The reason why your query not give you output is: you have written the wrong sequence of string concat.
This is the wrong sequence of Concat in your query: concat( c.Alias_Name,t.Sup_code, L.Alias_Name) and it gives you
Obviously,there is no data like 'AAAKLLSD%',and that's why you got nothing from your query.
So, what you need to do is just modify it to the true sequence: concat(L.Alias_Name,c.Alias_Name,t.Sup_code) both in the WHERE clause and SELECT clause.
Check this:

select concat( L.Alias_Name,c.Alias_Name,t.Sup_code  
              ,format(isnull(cast(substring(n.Contno,  len(c.Alias_Name) +len(t.Sup_code) + len(L.Alias_Name) + 1, len(n.Contno)) as int), 0) + 1, '000')) as Barcode  
from #tbl_Account_L_Four t  
left outer join #ConCatagory c on c.Cat_ID = t.Cat_ID  
left outer join #Containerno n on n.Level_Four_ID = t.Level_Four_ID   
left outer  join #tbl_location L on L.Loc_ID=n.Loc_ID and n.Contno like concat(L.Alias_Name,c.Alias_Name,t.Sup_code,'[0-11]%')     
where t.Level_Four_ID =  2111006  and L.Loc_ID=47  

Output:

Best regards,
LiHong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.