Newbie Question
-
oh man how do i describe this.....
I created a cursor, the cursor checks for duplicate people based on first and last name.
Sudo Code:
DECLARE Cursor Cursor Forward_Only
FOR
Select user.firstname, user.lastname from users
group by lastname, firstname
having count(lastname) > 1
Open Cursor
Fetch ...
While ...
DECLARE UserID Cursor Forward_Only
FOR
select ID from users where lastname = @lastname and firstname = @firstname
Open UserID
While ...
Fetch ...
print @lastname + ', ' + @firstname + ', ' + convert(varchar(255), @ID)
Fetch ....
Close ...
Deallocate....
Etc...
---------------
What I want to do is take the ID of the identified duplicate users and scan Table 'X' looking in 6 columns where all ID's identified for that user exist. Then I want to be able to update the ID's with the identified correct ID and inactive the bad duplicate user ID...
does this make sense? 🙁
-
Why are you using a cursor? Can you provide the table DDL (CREATE TABLE statement), sample data (a series of INSERT INTO statements), expected results?
This would help greatly to assist you.
-
First off, you won't need a cursor for this. Secondly, I'm not sure I follow what you want to do with the duplicate rows. Can you give an example of what Table X looks like, along with some sample data, and an example of how you want the finished Users table to appear?
This article will help you with creating your sample data in an easily consumable format:
http://www.sqlservercentral.com/articles/Best+Practices/61537/"> http://www.sqlservercentral.com/articles/Best+Practices/61537/
-
I don't know what kind of query would do the comparison like I need it to, that is why I am using a cursor. I recently learned how much using cursors are frowned upon because of the cost on performance.
The table has 6 columns
ReportID
User1Review
User2Review
User3Review
User1SignOff
User2SignOff
User3SignOff
If a user with two or more selections are present in the system, there ID's may be incorrect in the table causing web front end problems.
John Doe has ID 1 and 2.
A user may have put ID 2 in the User1Review and it goes to user 2's queue for signing off on the review, however ID 2 is invalid and does not have a login but ID 1 does. I need to find every row where 2 exists in one of those 6 columns and make it a 1.
-
John Rowan (1/5/2010)
Can you give an example of what Table X looks like, along with some sample data, and an example of how you want the finished Users table to appear?This article will help you with creating your sample data in an easily consumable format:
http://www.sqlservercentral.com/articles/Best+Practices/61537/"> http://www.sqlservercentral.com/articles/Best+Practices/61537/
You didn't read the article did you?
-
I will repost after reading the article and complying with its best practice for posting.
Thanks.
-
Thank you. It's not that we don't want to help. Each of us has our own jobs with our own work to do. So the more work you do up front, the quicker you will get what you want and the higher the quality will be. Its the help-us-help-you attitude.
We all speak the same language, for the most part, but nothings speaks more clearly than exmaple data.
-
I understand. Let me try this again.
This is the users table:
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[users]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)drop table [dbo].[users]
GO
CREATE TABLE [dbo].[users] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[firstname] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[lastname] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[isinactive] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
insert into users values ('john','doe','test@test.com','N')
insert into users values ('john','doe','test@test.com','N')
insert into users values ('jack','doe','test@jack.com','N')
insert into users values ('jack','doe','test@doe.com','N')
This is the Financial Reports Table
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[financial_reports]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)drop table [dbo].[financial_reports]
GO
CREATE TABLE [dbo].[financial_reports] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[financialreviewer1] [int] NULL ,
[financialreviewer2] [int] NULL ,
[financialreviewer3] [int] NULL ,
[signingfinancialprovider1] [int] NULL ,
[signingfinancialprovider2] [int] NULL ,
[signingfinancialprovider3] [int] NULL
) ON [PRIMARY]
GO
insert into financial_reports values ('1','2','','','','')
insert into financial_reports values ('3','4','','','','')
What I want to do is search for results in the financial_reports table where the person with the same first and last name have two different user ID's and have had two different ID's put in two different columns in the table, I then want to update the number with the correct user ID fixing and then inactivating the accident user in the system.
Does that make sense or am I still sound like a complete idiot?
-
Now, based on the above, what should the final result look like?
A picture is worth a thousand words. Or, as I tell people at work, pretend I'm from Missouri and show me.
-
The result I would like to see is:
For every ID where exist in one of the five columns a 1 and a 2, I want to replace the value with the correct ID, making the ID consistent across the columns.
before
after
-
First of all, thank you for creating the DDL, sample data, and desired results. This makes a HUGE difference when trying to help someone. You'll find that you will get very good help if you continue to do some of the leg work that comes along with preparing a quality thread question. Like Lynn said, a picture speaks a thousand words and "Show'n Us" helps even more.
Now, you say
For every ID where exist in one of the five columns a 1 and a 2, I want to replace the value with the correct ID, making the ID consistent across the columns.
Here's where you give us a verbal description of how we can logically get to the answer. Given the example results you want to get, what makes your final result set the 'Correct'? Why would you make the change that you show in your example? Why would you not also change the values for row #2 to be consistent since user IDs 3&4 are the same person?
-
Say a user exists two times 'John Doe'.
Someone does a accounting review for a customer and a report is generated, there is a window when you search for the financial reviewer, in that screen there are two users returned if someone selects one of the two returns one of the values is invalid to login and the other is valid.
When the financial viewer logs into the application he has a reports queue that tells him to review the financial data and sign off on the data.
If a wrong ID is in one of the first three columns the process will be broken because the person with the wrong ID does not have a web app login therefore unable to get to the financial reviewer queue....
This problem happened due to a bad import of data.....
-
I didn't mean to upset anyone. My goal is to become a more efficient SQL programmer. I have a difficult time explaining myself as it is. I apologize.
-
itsmeman (1/6/2010)
Say a user exists two times 'John Doe'.Someone does a accounting review for a customer and a report is generated, there is a window when you search for the financial reviewer, in that screen there are two users returned if someone selects one of the two returns one of the values is invalid to login and the other is valid.
When the financial viewer logs into the application he has a reports queue that tells him to review the financial data and sign off on the data.
If a wrong ID is in one of the first three columns the process will be broken because the person with the wrong ID does not have a web app login therefore unable to get to the financial reviewer queue....
This problem happened due to a bad import of data.....
First, no one is upset. We are trying to help you, but to do that, we needed you to help us. This is why we asked for everything that you have provided so far. It makes it easier for us to focus more on the problem instead of the setup for the problem (and possibly not get it right).
Second, you didn't answer John's question:
Here's where you give us a verbal description of how we can logically get to the answer. Given the example results you want to get, what makes your final result set the 'Correct'? Why would you make the change that you show in your example? Why would you not also change the values for row #2 to be consistent since user IDs 3&4 are the same person?
-
Here's where you give us a verbal description of how we can logically get to the answer. Given the example results you want to get, what makes your final result set the 'Correct'? Why would you make the change that you show in your example? Why would you not also change the values for row #2 to be consistent since user IDs 3&4 are the same person?
I don't understand the first question.
The reason why I would make the change is so anything with financialreviewer1 2 or 3 does not have a valid ID.. A valid ID would be a user that exists in the user table but is not able to login and get to the financial reviewer queue... If this fails, the finalization process fails.
FinancialReviewer1, 2, 3 and FinancialReviewer1, 2, 3 are user ID's from the user table. The web app queries this table to handle the workflow.
Viewing 15 posts - 1 through 15 (of 29 total)
You must be logged in to reply to this topic. Login to reply