Join 99,784 Programmers for FREE! Ask your question and get quick answers from experts. There are 1,548 online right now! We've got more than 500 tutorials and 2,000 snippets. Join and find out why Dream.In.Code is the #1 programming help community on the internet! Registration is fast and FREE... Join Now!
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.).
CODE
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
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.
CODE
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:
CODE
UPDATE E.Users SET Username = RTRIM('TERMED - ' + Username) WHERE UserID in (SELECT TERMEDUserID FROM @TempTable) AND Username NOT LIKE 'TERMED% -%'
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.
CODE
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:
CODE
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
-- 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
-- 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?
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
CODE
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:
CODE
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.
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.
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:
CODE
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.
QUOTE(rastaman832003 @ 25 Apr, 2008 - 09:58 AM)
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:
CODE
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
-- 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
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 Apr, 2008 - 08:11 AM
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?
21 Apr, 2008 - 12:51 PM
|