November 7, 2016 at 9:03 am
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.
November 7, 2016 at 10:48 pm
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
November 7, 2016 at 10:58 pm
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.
November 7, 2016 at 11:16 pm
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.
November 8, 2016 at 1:27 am
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
November 9, 2016 at 3:52 am
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
November 9, 2016 at 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 ?
----------------------------------------------------
November 10, 2016 at 4:51 am
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.
November 10, 2016 at 5:05 am
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....;-)
January 4, 2017 at 7:13 am
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.
January 4, 2017 at 7:46 am
Can you post the accumulated business rules please?
😎
January 4, 2017 at 8:08 am
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.
January 4, 2017 at 1:47 pm
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
January 5, 2017 at 12:44 am
Thanks drew,
Thank you so much, Your approach is working fine.
🙂
Thanks,
Abhas.
January 17, 2017 at 6:25 am
MMartin1 - Wednesday, November 9, 2016 11:59 AMWhat 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