4

I have a table name: my_table

Column: ID, name, department (department is a drop down list as below:

AOD
CRM
RMD

Now I want to that when auto increment will generated it will depend on department as like below.

If department AOD then ID will generated as below:

AOD00001
AOD00002
AOD00003

If department CRM then ID will generated as below:

CRM00001
CRM00002
CRM00003

If department RMD then ID will generated as below:

RMD00001
RMD00002
RMD00003

or

AOD00001
CRM00002
AOD00003
RMD00004
CRM00005
3

I'd suggest either to use an additional table or by adding a new counter field to your current Departments table.

Then build an stored procedure or use a BEFORE INSERT trigger, to quietly add the next ID every time a new record is inserted.

CREATE TABLE [dbo].[DepartmentSeq]
(
    [department]        varchar(10)     NOT NULL,
    [sequence]          int             NOT NULL,

    CONSTRAINT [PK_DepartmentSeq] 
        PRIMARY KEY CLUSTERED ([department] ASC)
) 
ON [PRIMARY];
GO

--= SP [sp_DeparmentSeq_next]
--------------------------------------------------
CREATE PROCEDURE [dbo].[sp_DeparmentSeq_next]
(
@department varchar(10),
@next_sequence int OUT
)
AS
BEGIN

    DECLARE @sequence int;
    SET @sequence= 0;
    SET @next_sequence = 0;

    BEGIN TRY

        SELECT   @sequence = [sequence]
        FROM     [dbo].[DepartmentSeq] WITH (UPDLOCK)
        WHERE    [department] = @department;

        IF @@ROWCOUNT > 0
        BEGIN 
            SET @sequence= @sequence + 1;

            UPDATE [dbo].[DepartmentSeq]
            SET    sequence = @sequence
            WHERE  [department] = @department;
        END
        ELSE
        BEGIN
            SET @sequence = 1;

            INSERT INTO [dbo].[DepartmentSeq] (department, sequence)
            VALUES      (@department, @sequence);
        END

        SET @next_sequence = @sequence;

    END TRY
    BEGIN CATCH

        DECLARE @en INT, @es INT, @ep SYSNAME, @el INT, @em NVARCHAR(2048);
        SET     @en = ERROR_NUMBER();
        SET     @es = ERROR_SEVERITY();
        SET     @ep = ERROR_PROCEDURE()
        SET     @el = ERROR_LINE();
        SET     @em = ERROR_MESSAGE();
        -- EXEC [dbo].[vsp_log_errors_insert] @ep, @en, @em, @es, @el;
        RAISERROR(@em, @es, @el);
        RETURN @en;

    END CATCH

    RETURN 0;

END
GO

As a second option, if there is a limited number of departments and depending on which version of SQL Server you use, you can use a SEQUENCE for each department.

2

I suggest using @McNets method to achieve your goal but, i liked to provide another way to achieve it.

Workaround Using CTE

you can use common table expressions to generate the id's you are looking for with a similar query

WITH CTE_1 AS (SELECT * , ROW_NUMBER() OVER(PARTITION BY [department] ORDER BY [ID]) as rcount FROM #TblTemp)
SELECT [department] + RIGHT('00000' + CAST(rcount as varchar(5)), 5) FROM CTE_1

Test

--Initialize Table

CREATE TABLE #TblTemp(ID INT IDENTITY(1,1), [name] VARCHAR(50), department VARCHAR(3))

INSERT INTO #TblTemp([name],department) 

SELECT 'aa','AOD'
UNION ALL
SELECT 'bb','AOD'
UNION ALL
SELECT 'cc','CRM'
UNION ALL
SELECT 'dd','CRM'
UNION ALL
SELECT 'ee','AOD'
UNION ALL
SELECT 'ff','RMD'
UNION ALL
SELECT 'gg','CRM'

Select * From #TblTemp;

--Generate Id using CTE
    WITH CTE_1 AS (SELECT * , ROW_NUMBER() OVER(PARTITION BY [department] ORDER BY [ID]) as rcount FROM #TblTemp)
    SELECT [name] , [department] + RIGHT('00000' + CAST(rcount as varchar(5)), 5) as [NewID] FROM CTE_1

Results

enter image description here

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.