You must Sign In to post a response.
  • Category: SQL Server

    How to given employee id based on department

    Hi Friend

    I have one table field is employeeid this id is auto increment based on department given to different series of employeeid how to do this case

    ex: if employee dept is IT then employeeid start with 100001 series another employee dept HR then Empid start with 200001 series

    Thanks And Regards

    Kavin C
  • #753195
    Hi kavin,

    If you want that field is auto increment, i don't know how to implement. If you are looking that empid is manually increment then I will give you guidance how to achieve this.

    I assume you have a UI, when you enter employee details you are passing Department details. Based on the selected department you need to get the maximum number of that particular department and in your code you need to increment that by 1 and pass that incremented value to database.

    I will try if that is auto increment also, once i got any idea then I will get back to you..

    --------------------------------------------------------------------------------
    Give respect to your work, Instead of trying to impress your boss.

    N@veen
    Blog : http://naveens-dotnet.blogspot.in/

  • #753216
    Hi kavin,

    We can use After Insert Trigger for your requirement. Refer the below sql script for details

    CREATE TABLE [dbo].[Table_3](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [EmployeeID] [int] NULL,
    [Dep] [nchar](10) NULL
    ) ON [PRIMARY]

    GO


    ALTER TRIGGER trgafterinsert1
    ON [dbo].[table_3]
    FOR INSERT
    AS
    DECLARE @eid INT
    DECLARE @id INT;
    DECLARE @depname VARCHAR(100);
    DECLARE @audit_action VARCHAR(100);

    SELECT @id = i.id
    FROM inserted i;

    SELECT @depname = i.dep
    FROM inserted i;

    SELECT @eid = Max(employeeid)
    FROM [table_3]
    WHERE dep = @depname

    IF @eid IS NULL
    BEGIN
    SET @eid =CASE
    WHEN @depname = 'HR' THEN 20000
    WHEN @depname = 'IT' THEN 10000
    END
    END

    IF @depname = 'IT'
    BEGIN
    SET @eid=@eid + 1

    PRINT @eid
    END
    ELSE IF @depname = 'HR'
    BEGIN
    SET @eid=@eid + 1

    PRINT @eid
    END

    UPDATE [table_3]
    SET employeeid = @eid
    WHERE id = @id

    go

    INSERT INTO [table_3]
    (dep)
    VALUES ('IT')

    GO

    SELECT *
    FROM table_3

  • #753225
    Hi Kavin,

    You can solve this issue easily. Please refer below queries:

    1) SELECT ISNULL(MAX(convert(numeric,right(EmpID,len(EmpID)-3)))+1,100001) AS EmpID FROM Employee_Information WHERE DepartmentID = '" + DeptID + "';

    2) SELECT ISNULL(MAX(EmpID)+1,100001) AS EmpID FROM Employee_Information WHERE DepartmentID = '" + DeptID + "';

    Hope this will help you.

    Thanks,
    Ram Prasad

  • #753245
    Hi Kavin,

    Please refer below sample for your reference.



    /* Craete the following two tables*/


    CREATE TABLE [dbo].[transactions_table](
    [empid] [int] NULL,
    [Name] [varchar](30) NULL,
    [Age] [int] NULL,
    [Department] [varchar](20) NULL
    )

    CREATE TABLE [dbo].[department](
    [names] [nvarchar](10) NULL,
    [initial_values] [int] NULL
    )


    /*Insert the two values in [department] table */

    insert into [department] values('HR',1000)


    insert into [department] values('Sales',2000)

    go
    /*** Create this stored procedure and execute the proper input parameter ***/


    CREATE PROCEDURE [dbo].[Usp_ManualIdentity]

    @name varchar(30),@Age int ,@Department varchar(20)

    as

    DECLARE @VALUE INT
    BEGIN TRY
    BEGIN TRAN T1

    SELECT @VALUE=initial_Values FROM department WHERE NAMES=@Department
    SELECT @VALUE
    INSERT INTO dbo.transactions_table VALUES(@VALUE,@name,@Age,@Department)
    SET @VALUE=@VALUE+1
    UPDATE dbo.department SET initial_values=@VALUE WHERE NAMES=@Department
    COMMIT TRAN T1
    END TRY
    BEGIN CATCH
    ROLLBACK TRAN T1
    END CATCH
    SELECT * FROM dbo.transactions_table
    --SELECT * FROM department
    GO





    Implement your task by referring above sample code.

    Hope this will helpful to you...

    --------------------------------------------------------------------------------
    Give respect to your work, Instead of trying to impress your boss.

    N@veen
    Blog : http://naveens-dotnet.blogspot.in/


  • Sign In to post your comments