[rastaman832003]

I have a Stored procedure that Updates a Table column when the user has been set to a certain status for more then 30 days, on that 31 day mark the username is set to 'TERMED - 'Username. The problem is that the usernames in the Table need to be unique but alot are re-used Names with numerals appended to the end to show how many times this username has been used. Now i need to change the stored procedure to check if, after the 'TERMED' string is added, it is the same as any previous Usernames. And if it is the same i need to add a number to the end of the TERMED to show how many have been TERMED with the same Username(ie. TERMED, TERMED1, TERMED2, etc.).

SET @rowCount = @@ROWCOUNT;
	DECLARE @index int; SET @index = 1;
	DECLARE @termedIndex int; SET @termedIndex = 1;
	DECLARE @UserIDTemp int;
	
	WHILE (@index <= @rowCount)
		BEGIN			
			SELECT @UserIDTemp = TERMEDUserID
			FROM @TempTable
			WHERE ID = @index
			UPDATE E.Users
			SET	
				Username = RTRIM('TERMED - ' + Username)
			WHERE
				UserID = @UserIDTemp
				AND Username NOT LIKE 'TERMED% -%'
			SET @index = @index + 1;
		END

[rastaman832003]

So no ideas? Did i not word it well enough to understand the question? Any ideas would be highly appreciated

[baavgai]

I'm afraid I'm not entirely following. However, if you want to find duplicate user names, you can use a select like this to speed it up.

select Username
   from Users
   where Username LIKE 'TERMED% -%'
   group by Username
   having count(*)>1

Now, how you handle these is very database dependant. This doesn't entirely look like SQL Server, put real close.

I'm at a total loss as to the point of the code previously offered. It seems like you're going through a lot of effort to do a very slow version of this:

UPDATE E.Users
	SET Username = RTRIM('TERMED - ' + Username)
	WHERE UserID in (SELECT TERMEDUserID FROM @TempTable)
		AND Username NOT LIKE 'TERMED% -%'

Hope this helps.

[rastaman832003]

baavgai, on 24 Apr, 2008 - 07:28 AM, said:

I'm afraid I'm not entirely following. However, if you want to find duplicate user names, you can use a select like this to speed it up.

select Username
   from Users
   where Username LIKE 'TERMED% -%'
   group by Username
   having count(*)>1

Now, how you handle these is very database dependant. This doesn't entirely look like SQL Server, put real close.

I'm at a total loss as to the point of the code previously offered. It seems like you're going through a lot of effort to do a very slow version of this:

UPDATE E.Users
	SET Username = RTRIM('TERMED - ' + Username)
	WHERE UserID in (SELECT TERMEDUserID FROM @TempTable)
		AND Username NOT LIKE 'TERMED% -%'

Hope this helps.

It kinda is a slower version of your snippet, thing about it is i need the Query to loop through the Table, Change the Usernames to the TERMED Status and then i need it to check the table again to see if any of the Usernames that were changed to TERMED are the same as any Usernames that were previously set to TERMED(since all the Usernames must be unique), if the new one does match any of the older ones, i need to add a numeral to the end of the 'TERMED -' string to show that this is the (1st,2nd,3rd,etc.) agent with that name set to TERMED username status.

so it would be like:
Termed - RJAllen
Termed2- RJAllen
Termed3- RJAllen

[baavgai]

rastaman832003, on 24 Apr, 2008 - 10:42 AM, said:

so it would be like:
Termed - RJAllen
Termed2- RJAllen
Termed3- RJAllen

Ok, I follow now. What database?

[rastaman832003]

baavgai, on 24 Apr, 2008 - 08:02 AM, said:

rastaman832003, on 24 Apr, 2008 - 10:42 AM, said:

so it would be like:
Termed - RJAllen
Termed2- RJAllen
Termed3- RJAllen

Ok, I follow now. What database?

The table that is being updated is the E.Users table in SQL2005.

[baavgai]

Here's my solution, given what I understand.

declare @Termed table(ParentUserID int, UserID int, TermedSeq int)

-- here we assign at "Parent" table, with the base name, all permutations are children
insert into @Termed(ParentUserID, UserID)
	select a.UserId, b.UserId
		from E.Users a
			inner join E.Users b on 
				b.Username LIKE 'TERMED% - ' + a.Username
		where a.Username NOT LIKE 'TERMED% -%'

-- define a sequence for each parent.  Note, to make the count go the other direction, use max.
DECLARE @iter int; 
SET @iter = 1;
while @iter!=0 begin
	update @Termed
		set TermedSeq = @iter
		where UserID in (select min(UserID) from @Termed where TermedSeq is null group by ParentUserID)
	if @@ROWCOUNT>0
		SET @iter = @iter + 1
	else 
		SET @iter = 0
end

-- With the sequence determined, apply it to the table.
update E.Users
	set Username = 
		(select 'TERMED' 
				+ (case when b.TermedSeq=1 then '' else cast(TermedSeq as varchar) end)
				+ ' - ' + RTRIM(Username)
			from E.Users where UserID=b.ParentUserID)
	from E.Users a
		inner join @Termed b
			on b.UserID=a.UserID

Hope this helps.

[rastaman832003]

baavgai, on 24 Apr, 2008 - 11:01 AM, said:

Here's my solution, given what I understand.

declare @Termed table(ParentUserID int, UserID int, TermedSeq int)

-- here we assign at "Parent" table, with the base name, all permutations are children
insert into @Termed(ParentUserID, UserID)
	select a.UserId, b.UserId
		from E.Users a
			inner join E.Users b on 
				b.Username LIKE 'TERMED% - ' + a.Username
		where a.Username NOT LIKE 'TERMED% -%'

Hope this helps.

Im a little confused on your join here. Are you joining the table to itself by giving the same table different Aliases? Or are you meaning to join @Termed with E.Users?

[baavgai]

rastaman832003, on 24 Apr, 2008 - 02:26 PM, said:

Im a little confused on your join here. Are you joining the table to itself by giving the same table different Aliases? Or are you meaning to join @Termed with E.Users?

Self join. What I'm trying ot do is get some kind of identity to work from. I'm assuming for all the 'TERMED - RJAllen' permutations, there must be one 'RJAllen'.

By way of example, this

create table #Test1( UserID int identity(1,1) not null, Username varchar(50))

insert into #Test1(Username) values ('RJAllen')
insert into #Test1(Username) values ('TERMED - RJAllen')
insert into #Test1(Username) values ('TERMED2 - RJAllen')
insert into #Test1(Username) values ('TERMED - RJAllen')
insert into #Test1(Username) values ('Bob')
insert into #Test1(Username) values ('Bill')
insert into #Test1(Username) values ('TERMED - Bill')
insert into #Test1(Username) values ('TERMED - Bill')

select *
	from #Test1 a
		inner join #Test1 b on 
			b.Username LIKE 'TERMED% - ' + a.Username
	where a.Username NOT LIKE 'TERMED% -%'

Produces this:

UserID	  Username	UserID	  Username
----------- ----------- ----------- ------------------
1		   RJAllen	 2		   TERMED - RJAllen
1		   RJAllen	 3		   TERMED2 - RJAllen
1		   RJAllen	 4		   TERMED - RJAllen
6		   Bill		7		   TERMED - Bill
6		   Bill		8		   TERMED - Bill

Now that we have a key, the parent id, to work from, we can define an order for the rest of the values. One the order is defined, the names can be pumped back with the numbers as part of the user names.

[rastaman832003]

baavgai, on 24 Apr, 2008 - 11:54 AM, said:

rastaman832003, on 24 Apr, 2008 - 02:26 PM, said:

Im a little confused on your join here. Are you joining the table to itself by giving the same table different Aliases? Or are you meaning to join @Termed with E.Users?

Self join. What I'm trying ot do is get some kind of identity to work from. I'm assuming for all the 'TERMED - RJAllen' permutations, there must be one 'RJAllen'.

Now that we have a key, the parent id, to work from, we can define an order for the rest of the values. One the order is defined, the names can be pumped back with the numbers as part of the user names.

ok, i am having issues with that join still, when i keep it there it executes but gets no results even tho i know there should be at least 2, but when i take out the join it finds the 2 rows. Now i do have a "History" table that has a list of all the changes made to each user, could i use the Username field in there to do a similar join? This table just recieves a row dump whenever a row is updated in the E.Users table. In this history table, almost every person has multiple entries so the Username would have to be taken from the most recently created row for that person. I dont know if this works or not but i cant seem to get it to work when i have the self join active.

[baavgai]

rastaman832003, on 25 Apr, 2008 - 09:58 AM, said:

when i keep it there it executes but gets no results even tho i know there should be at least 2

There is some assumption of cleanliness of data. Try a more sanitized version:

rtrim(b.Username) LIKE 'TERMED% -%' + rtrim(a.Username)

The other assumption is that there won't be a 'TERMED' value that doesn't have a master value.

rastaman832003, on 25 Apr, 2008 - 09:58 AM, said:

Now i do have a "History" table that has a list of all the changes made to each user, could i use the Username field in there to do a similar join?

Yes, actually. If you have a history table it shouldn't be too hard to adapt it. Just understand the steps.

1. Identify all duplicates and choose the master entry, the one top one that doesn't change.
2. With a master entry choosen, order all related rows and assign a sequence.
3. With the sequence assigned, maniplate the fields as needed.

You run into this kind of thing a lot when someone wants "line numbers" in report data. Databases aren't spreadsheets, they don't think in line numbers. How, they do think in min, max and, if you're lucky, auto number.

If you follow the process, you should be able to adapt it to what you need.


One more spin on the test data:

Script:

create table #Test1( UserID smallint identity(1,1) not null, Username varchar(8))

insert into #Test1(Username) values ('Larry')
insert into #Test1(Username) values ('Moe')
insert into #Test1(Username) values ('Curly')
-- add some more data
insert into #Test1(Username) select Username from #Test1
insert into #Test1(Username) select Username from #Test1
-- we now have 12 rows of data

create table #TestWk( Seq smallint identity(1,1) not null, UserID smallint, Username varchar(8), UserSeq int)

-- no self join, we're sequencing all data
insert into #TestWk(UserID, Username)
	select UserID, Username
	from #Test1
	order by Username, UserID desc

-- This is where we start
select * from #TestWk

-- Here, the first record is the max(UserID) for each unique name
-- after that, all values will follow as the iterator moves, 
-- until there are no more records without a value assigned
DECLARE @iter int;
SET @iter = 1;
while @iter!=0 begin
	update #TestWk
		set UserSeq = @iter
		where Seq in (select min(Seq) from #TestWk where UserSeq is null group by Username)
		-- not, for this example we could also have used the UserID as a key
		-- this is not always an option
		-- where UserID in (select max(UserID) from #TestWk where UserSeq is null group by Username)
	if @@ROWCOUNT>0
		SET @iter = @iter + 1
	else
		SET @iter = 0
end

-- data in table
select * from #TestWk

-- With the sequence determined, apply it to the table.
update #Test1
	set Username = b.UserName +  (case when b.UserSeq=1 then '' else cast(b.UserSeq as varchar) end)
	from #Test1 a
		inner join #TestWk b
			on b.UserID=a.UserID

-- verify
select * from #Test1

Results:

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

(3 row(s) affected)

(6 row(s) affected)

(12 row(s) affected)
Seq	UserID Username UserSeq
------ ------ -------- -----------
1	  12	 Curly	NULL
2	  9	  Curly	NULL
3	  6	  Curly	NULL
4	  3	  Curly	NULL
5	  10	 Larry	NULL
6	  7	  Larry	NULL
7	  4	  Larry	NULL
8	  1	  Larry	NULL
9	  11	 Moe	  NULL
10	 8	  Moe	  NULL
11	 5	  Moe	  NULL
12	 2	  Moe	  NULL

(12 row(s) affected)


(3 row(s) affected)

(3 row(s) affected)

(3 row(s) affected)

(3 row(s) affected)

(0 row(s) affected)
Seq	UserID Username UserSeq
------ ------ -------- -----------
1	  12	 Curly	1
2	  9	  Curly	2
3	  6	  Curly	3
4	  3	  Curly	4
5	  10	 Larry	1
6	  7	  Larry	2
7	  4	  Larry	3
8	  1	  Larry	4
9	  11	 Moe	  1
10	 8	  Moe	  2
11	 5	  Moe	  3
12	 2	  Moe	  4

(12 row(s) affected)

(12 row(s) affected)

UserID Username
------ --------
1	  Larry4
2	  Moe4
3	  Curly4
4	  Larry3
5	  Moe3
6	  Curly3
7	  Larry2
8	  Moe2
9	  Curly2
10	 Larry
11	 Moe
12	 Curly

(12 row(s) affected)

Hope this makes sense. If self joins are an issue, you'll want to brush up on them. SQL is a terse language, you should know it well if you must work with it.

This post has been edited by baavgai: 25 April 2008 - 08:11 AM

[rastaman832003]

DECLARE @TempTable Table( Sequence SMALLINT IDENTITY(1,1) NOT NULL, UserID INT, Username VARCHAR(50), UserSequence INT)

	INSERT INTO @TempTable(UserID, Username)
		SELECT EU.UserID, EU.Username
		FROM E.Users EU
			INNER JOIN E.RecordStatus ERS
				ON ERS.UserID = EU.UserID
			INNER JOIN L.RecordStatuses LRS 
				ON LRS.RecordStatusID = ERS.RecordStatusID
		WHERE (LRS.Name = 'Terminated - Not Eligible for Rehire' OR LRS.Name = 'Terminated - Eligible for Rehire')
			AND DATEDIFF( d , ERS.StatusDate , GETDATE()) > 30
		ORDER BY Username, UserID DESC
		
		--This block is to get rid of the 'TERMED' string from previously termed Users
		IF (E.Users.Username LIKE 'TERMED%-%')
		BEGIN
			Update @TempTable		
			SET
				Username = HEU.Username
			from @TempTable TT
			INNER JOIN History.EUsers HEU
				on HEU.UserID = TT.UserID
			where TT.UserID = HEU.UserID
		END
	
	--This Block is to create the Sequence for the Users
	DECLARE @Count INT;
		SET @Count = 1;
		WHILE @Count!=0 
			BEGIN
				UPDATE @TempTable
					SET UserSequence = @Count
					WHERE UserID IN (SELECT MAX(UserID) FROM @TempTable WHERE UserSequence IS NULL GROUP BY Username)
	
					IF @@ROWCOUNT>0
						SET @Count = @Count + 1
					ELSE
						SET @Count = 0
			END

	


	--This block prepends the 'TERMED' string along with the UserSequence number(If Availible) to the Username.
	UPDATE @TempTable
		SET Username = (SELECT 'TERMED'
						+ (CASE WHEN UserSequence=1 THEN ' ' ELSE CAST(UserSequence AS VARCHAR) END)
						+ '- ' + RTRIM(Username))

Ok, so this is my most recent change to this Stored Procedure. I think it is correct except i cant get the 'If' statement to work. I am sure it isnt set up correctly but i cant seem to figure out what to change. The If area is supposed to check if any of the Usernames inside the TempTable have 'TERMED' prepended, and if they do its supposed to switch those Usernames for the ones in the History Table that match the UserID(since the ones in history shouldnt have the 'TERMED' on the front). Any suggestions on what im doing wrong still?

[rastaman832003]

Thanks for the help baavgai. I finally got it working the way i need. I appreciate all the time you took to help me. SOMEONE GIVE THIS MAN SOME KUDOS!