Generate Number Bale_ID base on Bale_Type

akhter hussain 2,491 Reputation points
2021-11-22T12:25:57.2+00:00

I want to generate Bale_ID base on Bale type ,

Create table #tbl_Bale_Prd (Bale_ID varchar,Bale_Type int)

  ;WITH cte AS (SELECT 1 AS Bale_ID
                            UNION ALL
                            SELECT TOP (1) stuff(Bale_ID, 1, 3, '') + 1 AS Bale_ID
                            FROM     #tbl_Bale_Prd AS t
                            ORDER BY Bale_ID DESC)
      SELECT TOP (1)  CONCAT('BB-', Bale_ID)  AS Bale_ID
      FROM     cte
      ORDER BY Bale_ID DESC

Means that
Bale_type =1 then generate BB-10001
Bale_type =2 then generate BL-10001

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
8,592 questions
{count} votes

Accepted answer
  1. EchoLiu-MSFT 14,416 Reputation points
    2021-11-23T06:53:52.117+00:00

    151685-image.png
    Maybe like this?

        SELECT *,CASE WHEN Bale_type =1 THEN 'BB-10001'  
        WHEN Bale_type =2 THEN 'BL-10001' END Bale_ID  
        FROM #tbl_Bale_Prd  
    

    Regards,
    Echo


    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".

    No comments

0 additional answers

Sort by: Most helpful