Username increment by 1 if already exists in table

  • WITH x (ID,FirstName,LastName,UserName,UserNum) AS (

    SELECT e.ID,e.FirstName,e.LastName,LEFT(e.FirstName,t.N)+e.LastName,

    ROW_NUMBER() OVER (ORDER BY LEN(LEFT(e.FirstName,t.N)+e.LastName) ASC,LEFT(e.FirstName,t.N)+e.LastName ASC,ID ASC)

    FROM #Employee e

    JOIN master.dbo.Tally t ON t.N BETWEEN 1 AND LEN(e.FirstName)

    WHERE NOT EXISTS (SELECT * FROM #TEMP_USER u WHERE u.USERNAME = LEFT(e.FirstName,t.N)+e.LastName)

    ),

    y (ID,FirstName,LastName,UserNum,UserName,RowNum) AS (

    SELECT ID,FirstName,LastName,UserNum,UserName,

    ROW_NUMBER() OVER (PARTITION BY x.ID ORDER BY LEN(x.UserName) ASC)

    FROM x

    WHERE NOT EXISTS (SELECT * FROM x y WHERE y.UserName = x.UserName AND y.UserNum < x.UserNum)

    )

    SELECTID,FirstName,LastName,UserName

    FROMy

    WHERE RowNum = 1;

    Far away is close at hand in the images of elsewhere.
    Anon.

  • Here is a solution that works according to the current;-) requirements. It has a limitation of the first name being 30 characters or shorter, don't think that is going to be a problem.

    😎

    I hope you do recognize that there is a flaw in the current requirements as when the total number of instances of identical names reaches the character count in the first name, there will be a collision as the logic does not account for that!

    USE TEEST;

    GO

    SET NOCOUNT ON;

    IF OBJECT_ID(N'tempdb..#TEMP_USER') IS NOT NULL DROP TABLE #TEMP_USER;

    Create table #TEMP_USER

    (

    ID int identity (1,1) ,

    USERNAME nvarchar(50)

    );

    IF OBJECT_ID(N'tempdb..#Employee') IS NOT NULL DROP TABLE #Employee;

    Create table #Employee

    (

    ID int identity (1,1) ,

    FirstName varchar(40),

    LastName varchar(50)

    );

    insert into #Employee (FirstName,LastName)

    values('Abhas','Patil')

    ,('Aarav','Patil')

    ,('Sanjay','sutar')

    ,('Swati','jadhav')

    ,('varsha','mane');

    ;WITH BASE_DATA AS

    (

    SELECT

    E.ID

    ,nm.N

    ,SUBSTRING(E.FirstName,1,NM.N) + E.LastName AS USERNAME

    ,ROW_NUMBER() OVER

    (

    PARTITION BY SUBSTRING(E.FirstName,1,NM.N) + E.LastName

    ORDER BY E.ID

    ,NM.N

    ) AS BD_RID

    FROM #Employee E

    CROSS APPLY

    (

    SELECT N

    FROM (VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9),(10),

    (11),(12),(13),(14),(15),(16),(17),(18),(19),(20),

    (21),(22),(23),(24),(25),(26),(27),(28),(29),(30)

    ) X(N)

    ) NM(N)

    WHERE NM.N <= LEN(E.FirstName)

    AND SUBSTRING(E.FirstName,1,NM.N) + E.LastName NOT IN

    ( SELECT

    T.USERNAME

    FROM #TEMP_USER T)

    )

    ,UNIQUE_NEW_USERNAMES AS

    (

    SELECT

    BD.ID

    ,MIN(BD.N) MIN_N

    FROM BASE_DATA BD

    WHERE BD.BD_RID = 1

    GROUP BY BD.ID

    )

    INSERT INTO #TEMP_USER(USERNAME)

    SELECT

    BD.USERNAME

    FROM BASE_DATA BD

    INNER JOIN UNIQUE_NEW_USERNAMES UNU

    ON BD.ID = UNU.ID

    AND BD.N = UNU.MIN_N

    ;

    SELECT

    T.ID

    ,T.USERNAME

    FROM #TEMP_USER T;

    To test the code, we do three identical inserts

    USE TEEST;

    GO

    SET NOCOUNT ON;

    IF OBJECT_ID(N'tempdb..#TEMP_USER') IS NOT NULL DROP TABLE #TEMP_USER;

    Create table #TEMP_USER

    (

    ID int identity (1,1) ,

    USERNAME nvarchar(50)

    );

    IF OBJECT_ID(N'tempdb..#Employee') IS NOT NULL DROP TABLE #Employee;

    Create table #Employee

    (

    ID int identity (1,1) ,

    FirstName varchar(40),

    LastName varchar(50)

    );

    --FIRST INSERT

    RAISERROR ('----------------------------------------------------------------------------------------------

    -- FIRST INSERT

    ----------------------------------------------------------------------------------------------',0,0) WITH NOWAIT;

    insert into #Employee (FirstName,LastName)

    values('Abhas','Patil')

    ,('Aarav','Patil')

    ,('Sanjay','sutar')

    ,('Swati','jadhav')

    ,('varsha','mane');

    ;WITH BASE_DATA AS

    (

    SELECT

    E.ID

    ,nm.N

    ,SUBSTRING(E.FirstName,1,NM.N) + E.LastName AS USERNAME

    ,ROW_NUMBER() OVER

    (

    PARTITION BY SUBSTRING(E.FirstName,1,NM.N) + E.LastName

    ORDER BY E.ID

    ,NM.N

    ) AS BD_RID

    FROM #Employee E

    CROSS APPLY

    (

    SELECT N

    FROM (VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9),(10),

    (11),(12),(13),(14),(15),(16),(17),(18),(19),(20),

    (21),(22),(23),(24),(25),(26),(27),(28),(29),(30)

    ) X(N)

    ) NM(N)

    WHERE NM.N <= LEN(E.FirstName)

    AND SUBSTRING(E.FirstName,1,NM.N) + E.LastName NOT IN

    ( SELECT

    T.USERNAME

    FROM #TEMP_USER T)

    )

    ,UNIQUE_NEW_USERNAMES AS

    (

    SELECT

    BD.ID

    ,MIN(BD.N) MIN_N

    FROM BASE_DATA BD

    WHERE BD.BD_RID = 1

    GROUP BY BD.ID

    )

    INSERT INTO #TEMP_USER(USERNAME)

    SELECT

    BD.USERNAME

    FROM BASE_DATA BD

    INNER JOIN UNIQUE_NEW_USERNAMES UNU

    ON BD.ID = UNU.ID

    AND BD.N = UNU.MIN_N

    ;

    SELECT

    T.ID

    ,T.USERNAME

    FROM #TEMP_USER T;

    -- SECOND INSERT

    RAISERROR ('----------------------------------------------------------------------------------------------

    -- SECOND INSERT

    ----------------------------------------------------------------------------------------------',0,0) WITH NOWAIT;

    TRUNCATE TABLE #Employee;

    insert into #Employee (FirstName,LastName)

    values('Abhas','Patil')

    ,('Aarav','Patil')

    ,('Sanjay','sutar')

    ,('Swati','jadhav')

    ,('varsha','mane');

    ;WITH BASE_DATA AS

    (

    SELECT

    E.ID

    ,nm.N

    ,SUBSTRING(E.FirstName,1,NM.N) + E.LastName AS USERNAME

    ,ROW_NUMBER() OVER

    (

    PARTITION BY SUBSTRING(E.FirstName,1,NM.N) + E.LastName

    ORDER BY E.ID

    ,NM.N

    ) AS BD_RID

    FROM #Employee E

    CROSS APPLY

    (

    SELECT N

    FROM (VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9),(10),

    (11),(12),(13),(14),(15),(16),(17),(18),(19),(20),

    (21),(22),(23),(24),(25),(26),(27),(28),(29),(30)

    ) X(N)

    ) NM(N)

    WHERE NM.N <= LEN(E.FirstName)

    AND SUBSTRING(E.FirstName,1,NM.N) + E.LastName NOT IN

    ( SELECT

    T.USERNAME

    FROM #TEMP_USER T)

    )

    ,UNIQUE_NEW_USERNAMES AS

    (

    SELECT

    BD.ID

    ,MIN(BD.N) MIN_N

    FROM BASE_DATA BD

    WHERE BD.BD_RID = 1

    GROUP BY BD.ID

    )

    INSERT INTO #TEMP_USER(USERNAME)

    SELECT

    BD.USERNAME

    FROM BASE_DATA BD

    INNER JOIN UNIQUE_NEW_USERNAMES UNU

    ON BD.ID = UNU.ID

    AND BD.N = UNU.MIN_N

    ;

    SELECT

    T.ID

    ,T.USERNAME

    FROM #TEMP_USER T;

    -- THIRD INSERT

    RAISERROR ('----------------------------------------------------------------------------------------------

    -- THIRD INSERT

    ----------------------------------------------------------------------------------------------',0,0) WITH NOWAIT;

    TRUNCATE TABLE #Employee;

    insert into #Employee (FirstName,LastName)

    values('Abhas','Patil')

    ,('Aarav','Patil')

    ,('Sanjay','sutar')

    ,('Swati','jadhav')

    ,('varsha','mane');

    ;WITH BASE_DATA AS

    (

    SELECT

    E.ID

    ,nm.N

    ,SUBSTRING(E.FirstName,1,NM.N) + E.LastName AS USERNAME

    ,ROW_NUMBER() OVER

    (

    PARTITION BY SUBSTRING(E.FirstName,1,NM.N) + E.LastName

    ORDER BY E.ID

    ,NM.N

    ) AS BD_RID

    FROM #Employee E

    CROSS APPLY

    (

    SELECT N

    FROM (VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9),(10),

    (11),(12),(13),(14),(15),(16),(17),(18),(19),(20),

    (21),(22),(23),(24),(25),(26),(27),(28),(29),(30)

    ) X(N)

    ) NM(N)

    WHERE NM.N <= LEN(E.FirstName)

    AND SUBSTRING(E.FirstName,1,NM.N) + E.LastName NOT IN

    ( SELECT

    T.USERNAME

    FROM #TEMP_USER T)

    )

    ,UNIQUE_NEW_USERNAMES AS

    (

    SELECT

    BD.ID

    ,MIN(BD.N) MIN_N

    FROM BASE_DATA BD

    WHERE BD.BD_RID = 1

    GROUP BY BD.ID

    )

    INSERT INTO #TEMP_USER(USERNAME)

    SELECT

    BD.USERNAME

    FROM BASE_DATA BD

    INNER JOIN UNIQUE_NEW_USERNAMES UNU

    ON BD.ID = UNU.ID

    AND BD.N = UNU.MIN_N

    ;

    SELECT

    T.ID

    ,T.USERNAME

    FROM #TEMP_USER T;

    Output

    -----------------------

    -- FIRST INSERT

    -----------------------

    ID USERNAME

    ----------- -----------

    1 APatil

    2 AaPatil

    3 Ssutar

    4 Sjadhav

    5 vmane

    -----------------------

    -- SECOND INSERT

    -----------------------

    ID USERNAME

    ----------- -----------

    1 APatil

    2 AaPatil

    3 Ssutar

    4 Sjadhav

    5 vmane

    6 AbPatil

    7 AarPatil

    8 Sasutar

    9 Swjadhav

    10 vamane

    -----------------------

    -- THIRD INSERT

    -----------------------

    ID USERNAME

    ----------- -----------

    1 APatil

    2 AaPatil

    3 Ssutar

    4 Sjadhav

    5 vmane

    6 AbPatil

    7 AarPatil

    8 Sasutar

    9 Swjadhav

    10 vamane

    11 AbhPatil

    12 AaraPatil

    13 Sansutar

    14 Swajadhav

    15 varmane

  • David Burrows (11/7/2016)


    WITH x (ID,FirstName,LastName,UserName,UserNum) AS (

    SELECT e.ID,e.FirstName,e.LastName,LEFT(e.FirstName,t.N)+e.LastName,

    ROW_NUMBER() OVER (ORDER BY LEN(LEFT(e.FirstName,t.N)+e.LastName) ASC,LEFT(e.FirstName,t.N)+e.LastName ASC,ID ASC)

    FROM #Employee e

    JOIN master.dbo.Tally t ON t.N BETWEEN 1 AND LEN(e.FirstName)

    WHERE NOT EXISTS (SELECT * FROM #TEMP_USER u WHERE u.USERNAME = LEFT(e.FirstName,t.N)+e.LastName)

    ),

    y (ID,FirstName,LastName,UserNum,UserName,RowNum) AS (

    SELECT ID,FirstName,LastName,UserNum,UserName,

    ROW_NUMBER() OVER (PARTITION BY x.ID ORDER BY LEN(x.UserName) ASC)

    FROM x

    WHERE NOT EXISTS (SELECT * FROM x y WHERE y.UserName = x.UserName AND y.UserNum < x.UserNum)

    )

    SELECTID,FirstName,LastName,UserName

    FROMy

    WHERE RowNum = 1;

    This one works and returns exactly the same output as the code I posted. It is though "slightly" brute force and I would certainly recommend running it with OPTION (MAXDOP 1).

    😎

    For fun, here is a quick test setup

    RAISERROR ('----------------------------------------------------------------------------------------------

    -- EE

    ----------------------------------------------------------------------------------------------',0,0) WITH NOWAIT;

    SET STATISTICS IO,TIME ON;

    ;WITH BASE_DATA AS

    (

    SELECT

    E.ID

    ,nm.N

    ,SUBSTRING(E.FirstName,1,NM.N) + E.LastName AS USERNAME

    ,ROW_NUMBER() OVER

    (

    PARTITION BY SUBSTRING(E.FirstName,1,NM.N) + E.LastName

    ORDER BY E.ID

    ,NM.N

    ) AS BD_RID

    FROM #Employee E

    CROSS APPLY

    (

    SELECT N

    FROM (VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9),(10),

    (11),(12),(13),(14),(15),(16),(17),(18),(19),(20),

    (21),(22),(23),(24),(25),(26),(27),(28),(29),(30)

    ) X(N)

    ) NM(N)

    WHERE NM.N <= LEN(E.FirstName)

    AND SUBSTRING(E.FirstName,1,NM.N) + E.LastName NOT IN

    ( SELECT

    T.USERNAME

    FROM #TEMP_USER T)

    )

    ,UNIQUE_NEW_USERNAMES AS

    (

    SELECT

    BD.ID

    ,MIN(BD.N) MIN_N

    FROM BASE_DATA BD

    WHERE BD.BD_RID = 1

    GROUP BY BD.ID

    )

    SELECT

    BD.USERNAME

    FROM BASE_DATA BD

    INNER JOIN UNIQUE_NEW_USERNAMES UNU

    ON BD.ID = UNU.ID

    AND BD.N = UNU.MIN_N

    ;

    SET STATISTICS IO,TIME OFF;

    RAISERROR ('----------------------------------------------------------------------------------------------

    -- DB OPTION (MAXDOP 0)

    ----------------------------------------------------------------------------------------------',0,0) WITH NOWAIT;

    SET STATISTICS IO,TIME ON;

    ;WITH x (ID,FirstName,LastName,UserName,UserNum) AS (

    SELECT e.ID,e.FirstName,e.LastName,LEFT(e.FirstName,t.N)+e.LastName,

    ROW_NUMBER() OVER (ORDER BY LEN(LEFT(e.FirstName,t.N)+e.LastName) ASC,LEFT(e.FirstName,t.N)+e.LastName ASC,ID ASC)

    FROM #Employee e

    JOIN dbo.TBL_NUMBERS t ON t.N BETWEEN 1 AND LEN(e.FirstName)

    WHERE NOT EXISTS (SELECT * FROM #TEMP_USER u WHERE u.USERNAME = LEFT(e.FirstName,t.N)+e.LastName)

    ),

    y (ID,FirstName,LastName,UserNum,UserName,RowNum) AS (

    SELECT ID,FirstName,LastName,UserNum,UserName,

    ROW_NUMBER() OVER (PARTITION BY x.ID ORDER BY LEN(x.UserName) ASC)

    FROM x

    WHERE NOT EXISTS (SELECT * FROM x y WHERE y.UserName = x.UserName AND y.UserNum < x.UserNum)

    )

    SELECTID,FirstName,LastName,UserName

    FROMy

    WHERE RowNum = 1

    OPTION (MAXDOP 0);

    SET STATISTICS IO,TIME OFF;

    RAISERROR ('----------------------------------------------------------------------------------------------

    -- DB OPTION (MAXDOP 1)

    ----------------------------------------------------------------------------------------------',0,0) WITH NOWAIT;

    SET STATISTICS IO,TIME ON;

    ;WITH x (ID,FirstName,LastName,UserName,UserNum) AS (

    SELECT e.ID,e.FirstName,e.LastName,LEFT(e.FirstName,t.N)+e.LastName,

    ROW_NUMBER() OVER (ORDER BY LEN(LEFT(e.FirstName,t.N)+e.LastName) ASC,LEFT(e.FirstName,t.N)+e.LastName ASC,ID ASC)

    FROM #Employee e

    JOIN dbo.TBL_NUMBERS t ON t.N BETWEEN 1 AND LEN(e.FirstName)

    WHERE NOT EXISTS (SELECT * FROM #TEMP_USER u WHERE u.USERNAME = LEFT(e.FirstName,t.N)+e.LastName)

    ),

    y (ID,FirstName,LastName,UserNum,UserName,RowNum) AS (

    SELECT ID,FirstName,LastName,UserNum,UserName,

    ROW_NUMBER() OVER (PARTITION BY x.ID ORDER BY LEN(x.UserName) ASC)

    FROM x

    WHERE NOT EXISTS (SELECT * FROM x y WHERE y.UserName = x.UserName AND y.UserNum < x.UserNum)

    )

    SELECTID,FirstName,LastName,UserName

    FROMy

    WHERE RowNum = 1

    OPTION (MAXDOP 1);

    SET STATISTICS IO,TIME OFF;

    Output (2nd Gen i5 Laptop)

    -------------------------------------------------------

    -- EE

    -------------------------------------------------------

    Table '#TEMP_USER'. Scan count 2, logical reads 32

    Table 'Worktable'. Scan count 0, logical reads 0

    Table '#Employee'. Scan count 2, logical reads 2

    SQL Server Execution Times:

    CPU time = 0 ms, elapsed time = 0 ms.

    -------------------------------------------------------

    -- DB OPTION (MAXDOP 0)

    -------------------------------------------------------

    Table '#TEMP_USER'. Scan count 2, logical reads 2

    Table '#Employee'. Scan count 2, logical reads 2

    Table 'Workfile'. Scan count 0, logical reads 0

    Table 'Worktable'. Scan count 0, logical reads 0

    Table 'TBL_NUMBERS'. Scan count 10, logical reads 40

    Table 'Worktable'. Scan count 0, logical reads 0

    SQL Server Execution Times:

    CPU time = 15 ms, elapsed time = 14 ms.

    -------------------------------------------------------

    -- DB OPTION (MAXDOP 1)

    -------------------------------------------------------

    Table 'Worktable'. Scan count 0, logical reads 0

    Table 'Workfile'. Scan count 0, logical reads 0

    Table 'TBL_NUMBERS'. Scan count 10, logical reads 40

    Table '#Employee'. Scan count 2, logical reads 2

    Table '#TEMP_USER'. Scan count 2, logical reads 2

    SQL Server Execution Times:

    CPU time = 0 ms, elapsed time = 3 ms.

  • Hi,

    Thank you so much Eirikur and David.

    Really appreciate your efforts. Its working as per requirement.

    Only one issue - when same firstName and LastName comes then creating only one username.

    eg . Below data

    insert into #Employee (FirstName,LastName)

    values('Abhas','Patil')

    ,('Abhas','Patil')

    ,('Aarav','Patil')

    ,('Sanjay','sutar')

    ,('Swati','jadhav')

    ,('varsha','mane')

    ,('Ak','Patil');

    🙂

    Thanks once again.

    Thank you

    Abhas.

  • abhas (11/7/2016)


    Hi,

    Thank you so much Eirikur and David.

    Really appreciate your efforts. Its working as per requirement.

    Only one issue - when same firstName and LastName comes then creating only one username.

    eg . Below data

    insert into #Employee (FirstName,LastName)

    values('Abhas','Patil')

    ,('Abhas','Patil')

    ,('Aarav','Patil')

    ,('Sanjay','sutar')

    ,('Swati','jadhav')

    ,('varsha','mane')

    ,('Ak','Patil');

    🙂

    Thanks once again.

    Thank you

    Abhas.

    Quick fix, iterates through the first instance of the names, adds them, removes the first instance and repeats until the incoming set is exhausted.

    😎

    USE TEEST;

    GO

    SET NOCOUNT ON;

    IF OBJECT_ID(N'tempdb..#TEMP_USER') IS NOT NULL DROP TABLE #TEMP_USER;

    Create table #TEMP_USER

    (

    ID int identity (1,1) ,

    USERNAME nvarchar(50)

    );

    IF OBJECT_ID(N'tempdb..#Employee') IS NOT NULL DROP TABLE #Employee;

    Create table #Employee

    (

    ID int identity (1,1) ,

    FirstName varchar(40),

    LastName varchar(50)

    );

    --FIRST INSERT

    insert into #Employee (FirstName,LastName)

    values('Abhas','Patil')

    ,('Aarav','Patil')

    ,('Sanjay','sutar')

    ,('Sanjay','sutar')

    ,('Swati','jadhav')

    ,('varsha','mane');

    -- ITERATE THROUGH UNIQUE NAME INSTANCES

    WHILE (SELECT COUNT(*) FROM #Employee) > 0

    BEGIN

    RAISERROR ('----------------------------------------------------------------------------------------------

    -- FIRST INSERT

    ----------------------------------------------------------------------------------------------',0,0) WITH NOWAIT;

    ;WITH BASE_DATA AS

    (

    SELECT

    E.ID

    ,nm.N

    ,SUBSTRING(E.FirstName,1,NM.N) + E.LastName AS USERNAME

    ,ROW_NUMBER() OVER

    (

    PARTITION BY SUBSTRING(E.FirstName,1,NM.N) + E.LastName

    ORDER BY E.ID

    ,NM.N

    ) AS BD_RID

    FROM #Employee E

    CROSS APPLY

    (

    SELECT N

    FROM (VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9),(10),

    (11),(12),(13),(14),(15),(16),(17),(18),(19),(20),

    (21),(22),(23),(24),(25),(26),(27),(28),(29),(30)

    ) X(N)

    ) NM(N)

    WHERE NM.N <= LEN(E.FirstName)

    AND SUBSTRING(E.FirstName,1,NM.N) + E.LastName NOT IN

    ( SELECT

    T.USERNAME

    FROM #TEMP_USER T)

    )

    ,UNIQUE_NEW_USERNAMES AS

    (

    SELECT

    BD.ID

    ,MIN(BD.N) AS MIN_N

    FROM BASE_DATA BD

    WHERE BD.BD_RID = 1

    GROUP BY BD.ID

    )

    INSERT INTO #TEMP_USER(USERNAME)

    SELECT

    BD.USERNAME

    FROM BASE_DATA BD

    INNER JOIN UNIQUE_NEW_USERNAMES UNU

    ON BD.ID = UNU.ID

    AND BD.N = UNU.MIN_N

    ;

    SELECT

    T.ID

    ,T.USERNAME

    FROM #TEMP_USER T;

    ;WITH CHECK_IDENTICAL_NAMES AS

    (

    SELECT

    ROW_NUMBER() OVER

    (

    PARTITION BY E.FirstName,E.LastName

    ORDER BY E.ID

    ) AS NAME_RID

    ,E.ID

    ,E.FirstName

    ,E.LastName

    FROM #Employee E

    )

    DELETE CIN

    FROM CHECK_IDENTICAL_NAMES CIN

    WHERE CIN.NAME_RID = 1;

    END

    Output

    ---------------------

    -- FIRST INSERT

    ---------------------

    ID USERNAME

    ----------- ---------

    1 APatil

    2 AaPatil

    3 Ssutar

    4 Sjadhav

    5 vmane

    ---------------------

    -- FIRST INSERT

    ---------------------

    ID USERNAME

    ----------- ---------

    1 APatil

    2 AaPatil

    3 Ssutar

    4 Sjadhav

    5 vmane

    6 Sasutar

  • You need to start with a sound design, thinking through the possible issues, then implement it.

    The first design, with a suffixed number, is basically fine in principle. It will cope with any number of instances and complete name duplicates, and you have a standard format for knowing what is what. I'd always suffix by a number even if it is the only one so everything is consistent.

    With the approach of adding in as many letters from the firstname as will make it unique then you will always have the possibility of running out. If you had 3 people called Jo Smith the third one would cause problems.

    The original solution proposed worked for me when implemented as an update statement. However, it created duplicate IDs in the event that any but the last one was deleted. Whilst it is generally bad practice to delete usernames (better to mark as deleted), you never know what your successor will do - he may have been instructed to delete all unused usernames. Allowing for things that could but shouldn't happen is sometimes called 'defensive programming'.

    Assuming that #Employee has a column Username, the following code works in the limited cases I have tried. Note that username is set as 1st letter of firstname + lastname + ':' + number, e.g. JSmith:1. If there were a JSmith:2 and a JSmith:3, then if JSmith:2 is subsequently deleted it allocates the next as JSmith:4.

    with N as (

    select

    SUBSTRING(FirstName,1,1) + LastName as BaseName,

    max(substring(Username, len(SUBSTRING(FirstName,1,1) + LastName) + 2, 20)) as MaxNum,

    count(*) as Instances

    from #Employee where UserName like SUBSTRING(FirstName,1,1) + LastName + ':%'

    group by SUBSTRING(FirstName,1,1) + LastName

    ), X as (

    SELECT

    E.ID

    ,E.FirstName

    ,E.LastName

    ,SUBSTRING(E.FirstName,1,1) + E.LastName + ':'

    + ISNULL(NULLIF(

    CONVERT(VARCHAR(12),ROW_NUMBER() OVER

    (

    PARTITION BY SUBSTRING(E.FirstName,1,1) + E.LastName

    ORDER BY E.ID

    ) + cast(isnull(N.MaxNum, '0') as int) - isnull(N.Instances, 0)

    ,0),'0'),'') AS UserName

    FROM #Employee E

    left join N on SUBSTRING(E.FirstName,1,1) + E.LastName = N.BaseName

    )

    update E set Username = X.UserName from #Employee E

    inner join X on E.Id = X.ID

    where E.Username is null

  • What is the rule if you have three people named Bo Derek, or four people named Joe Smith? In the prior code run I see that the last one gets missed. That is why I am not a fan of "intelligent keys" and would prefer to simply use some numeric identifyer for the person. Is it that you are wanting to assign email addresses ?

    ----------------------------------------------------

  • Hi Friends,

    Thanks for your suggestion mhtanner and MMartin1 but client want this implementation only.Explained issue but they want like that only. 🙁

    Thanks again Eirikur for your help. You helped a lot. Now all test case has been passed. Thank you so much.

    Regards,

    Abhas.

  • abhas (11/10/2016)


    Hi Friends,

    Thanks for your suggestion mhtanner and MMartin1 but client want this implementation only.Explained issue but they want like that only. 🙁

    Thanks again Eirikur for your help. You helped a lot. Now all test case has been passed. Thank you so much.

    Regards,

    Abhas.

    You are very welcome.

    😎

    Until next time....;-)

  • Hi Guys,

    Again posting as one test case has failed. 🙁

    below code generates username wrong. it is creating usernames as below:

    APatil

    Abhas1Patil

    But requirement is

    APatil

    AbPatil

    SET NOCOUNT ON;

    IF OBJECT_ID(N'tempdb..#TEMP_USER') IS NOT NULL DROP TABLE #TEMP_USER;

    Create table #TEMP_USER

    (

    ID int identity (1,1) ,

    USERNAME nvarchar(50)

    );

    IF OBJECT_ID(N'tempdb..#Employee') IS NOT NULL DROP TABLE #Employee;

    Create table #Employee

    (

    ID int identity (1,1) ,

    FirstName varchar(40),

    LastName varchar(50)

    );

    --FIRST INSERT

    insert into #Employee (FirstName,LastName)

    values('Abhas','Patil')

    ,('Abhas1','Patil')

    ;

    -- ITERATE THROUGH UNIQUE NAME INSTANCES

    WHILE (SELECT COUNT(*) FROM #Employee) > 0

    BEGIN

    ;WITH BASE_DATA AS

    (

    SELECT

    E.ID

    ,nm.N

    ,SUBSTRING(E.FirstName,1,NM.N) + E.LastName AS USERNAME

    ,ROW_NUMBER() OVER

    (

    PARTITION BY SUBSTRING(E.FirstName,1,NM.N) + E.LastName

    ORDER BY E.ID

    ,NM.N

    ) AS BD_RID

    FROM #Employee E

    CROSS APPLY

    (

    SELECT N

    FROM (VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9),(10),

    (11),(12),(13),(14),(15),(16),(17),(18),(19),(20),

    (21),(22),(23),(24),(25),(26),(27),(28),(29),(30)

    ) X(N)

    ) NM(N)

    WHERE NM.N <= LEN(E.FirstName)

    AND SUBSTRING(E.FirstName,1,NM.N) + E.LastName NOT IN

    ( SELECT

    T.USERNAME

    FROM #TEMP_USER T)

    )

    ,UNIQUE_NEW_USERNAMES AS

    (

    SELECT

    BD.ID

    ,MIN(BD.N) AS MIN_N

    FROM BASE_DATA BD

    WHERE BD.BD_RID = 1

    GROUP BY BD.ID

    )

    INSERT INTO #TEMP_USER(USERNAME)

    SELECT

    BD.USERNAME

    FROM BASE_DATA BD

    INNER JOIN UNIQUE_NEW_USERNAMES UNU

    ON BD.ID = UNU.ID

    AND BD.N = UNU.MIN_N

    ;

    --SELECT

    -- T.ID

    -- ,T.USERNAME

    --FROM #TEMP_USER T;

    ;WITH CHECK_IDENTICAL_NAMES AS

    (

    SELECT

    ROW_NUMBER() OVER

    (

    PARTITION BY E.FirstName,E.LastName

    ORDER BY E.ID

    ) AS NAME_RID

    ,E.ID

    ,E.FirstName

    ,E.LastName

    FROM #Employee E

    )

    DELETE CIN

    FROM CHECK_IDENTICAL_NAMES CIN

    WHERE CIN.NAME_RID = 1;

    END

    SELECT

    T.ID

    ,T.USERNAME

    FROM #TEMP_USER T;

    Thanks,

    Abhas.

  • Can you post the accumulated business rules please?

    😎

  • Eirikur Thanks for reply...

    Rule is username should be first letter of first name + LastName.

    Now, If first letter of first name + LastName is repeated then consider first two letters and so on.

    eg below:

    FirstName LastName Username

    Abhas Patil Apatil

    Aby Patil abPatil

    Abhas Patil abhPatil

    Aby Patil abyPatil

    Aby Patil abyPatil1

    Abhijeet Patil AbhiPatil

    Arun Patil ArPatil

    Abyshek Patil AbysPatil

    Thanks,

    Abhas.

  • I treated this as a variation on the gaps and islands problem. This following works for your (extremely small) sample data.

    ;

    WITH usernames AS (

    SELECT *, ROW_NUMBER() OVER(PARTITION BY un.username ORDER BY e.FirstName, t.n, e.ID) - ROW_NUMBER() OVER(PARTITION BY e.FirstName ORDER BY t.n, e.ID) AS grp

    FROM #Employee e

    INNER JOIN (

    SELECT N

    FROM ( -- You may want to create a permanent tally table for this.

    VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9),(10),

    (11),(12),(13),(14),(15),(16),(17),(18),(19),(20),

    (21),(22),(23),(24),(25),(26),(27),(28),(29),(30)

    ) tally(N)

    ) t

    ON LEN(e.FirstName) >= t.n

    CROSS APPLY ( VALUES(LEFT(e.FirstName, t.n) + e.LastName) ) AS un(username)

    WHERE NOT EXISTS (

    SELECT *

    FROM #TEMP_USER tu

    WHERE tu.USERNAME = un.username

    )

    )

    SELECT *

    FROM usernames u

    WHERE u.grp = 0

    This approach uses the two ROW_NUMBER() functions to replace the WHILE loop.

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

  • Thanks drew,

    Thank you so much, Your approach is working fine.

    🙂

    Thanks,

    Abhas.

  • MMartin1 - Wednesday, November 9, 2016 11:59 AM

    What is the rule if you have three people named Bo Derek, or four people named Joe Smith? In the prior code run I see that the last one gets missed. That is why I am not a fan of "intelligent keys" and would prefer to simply use some numeric identifyer for the person. Is it that you are wanting to assign email addresses ?

    They don't even all need to be the same

    John Smith
    Joseph Smith
    Jo Smith

    will have a problem with the third name (although a different order would still work)

Viewing 15 posts - 16 through 30 (of 32 total)

You must be logged in to reply to this topic. Login to reply