|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Assign A Flag ValueRecord1 = LName: Doe FName: John SSN: 555-55-5555 Date: 10/1/2006 Record2 = LName: Doe FName: John SSN: 555-55-5555 Date: 06/15/2006 I would like to loop through all records using the LName, FName, and SSN (not my data so I don't have a unique identifier and due to data intergrity issues I can't use SSN alone) to assign a flag value. The flag value needs to reset for each new group of records, e.g., John Doe will have a flag value of 1, 2 and Jane Doe who has 3 records will have a flag value of 1, 2, 3. For example, the field Flag in Record 1 would be assigned a value of 2 and the field Flag in Record 2 would be assigned 1 because the Date field on Record 2 is before Record 1. The results would be as follows: Record1 = LName: Doe FName: John SSN: 555-55-5555 Date: 10/1/2006 Flag: 2 Record2 = LName: Doe FName: John SSN: 555-55-5555 Date: 06/15/2006 Flag: 1 Here is the kicker . . . if the date field are the same value then I need to evaluate a different field to determine the Flag value. For example: Record 1: Date: 10/1/2006 DocID: 12 Record 2: Date: 10/1/2006 DocID: 3 Record 3: Date: 6/1/2006 DocID: 20 In this case, Record 3 gets a Flag value = 1 because it has the first date and Record 2 gets a Flag value = 2 because it has the same date as Record 1 but a lower DocID. The results would be: Record 1: Date: 10/1/2006 DocID: 12 Flag: 3 Record 2: Date: 10/1/2006 DocID: 3 Flag: 2 Record 3: Date: 6/1/2006 DocID: 20 Flag: 1 Thanks!!! Anonymous wrote:
Show quote > I have a table as follows: You said your table doesn't have a "unique identifier". Do you mean it> > Record1 = > > LName: Doe > FName: John > SSN: 555-55-5555 > Date: 10/1/2006 > > Record2 = > > LName: Doe > FName: John > SSN: 555-55-5555 > Date: 06/15/2006 > > I would like to loop through all records using the LName, FName, and SSN > (not my data so I don't have a unique identifier and due to data intergrity > issues I can't use SSN alone) to assign a flag value. > > The flag value needs to reset for each new group of records, e.g., John Doe > will have a flag value of 1, 2 and Jane Doe who has 3 records will have a > flag value of 1, 2, 3. > > For example, the field Flag in Record 1 would be assigned a value of 2 and > the field Flag in Record 2 would be assigned 1 because the Date field on > Record 2 is before Record 1. > doesn't have a key? If so then how do you expect to identify individual rows correctly for their flag values? Why would you want to preserve duplicate rows anyway? Unless you can make more sense of the question there may not be a sensible answer. Please post DDL in future so that we don't have to guess. Take a look at the ROW_NUMBER function in Books Online. I'm assuming 2005 because you haven't said otherwise. -- David Portas, SQL Server MVP Whenever possible please post enough code to reproduce your problem. Including CREATE TABLE and INSERT statements usually helps. State what version of SQL Server you are using and specify the content of any error messages. SQL Server Books Online: http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx -- I am doing data cleanup . . . the table is not a design that I created. Yes
you are right, I do not have a primary key. As a result, I am using a combination of fields to identify records that belong to the same person. I basically wanted suggestions on how to do it with the info I provided and I would fine tune the code to work in my situation. Show quote "David Portas" wrote: > Anonymous wrote: > > I have a table as follows: > > > > Record1 = > > > > LName: Doe > > FName: John > > SSN: 555-55-5555 > > Date: 10/1/2006 > > > > Record2 = > > > > LName: Doe > > FName: John > > SSN: 555-55-5555 > > Date: 06/15/2006 > > > > I would like to loop through all records using the LName, FName, and SSN > > (not my data so I don't have a unique identifier and due to data intergrity > > issues I can't use SSN alone) to assign a flag value. > > > > The flag value needs to reset for each new group of records, e.g., John Doe > > will have a flag value of 1, 2 and Jane Doe who has 3 records will have a > > flag value of 1, 2, 3. > > > > For example, the field Flag in Record 1 would be assigned a value of 2 and > > the field Flag in Record 2 would be assigned 1 because the Date field on > > Record 2 is before Record 1. > > > > You said your table doesn't have a "unique identifier". Do you mean it > doesn't have a key? If so then how do you expect to identify individual > rows correctly for their flag values? Why would you want to preserve > duplicate rows anyway? Unless you can make more sense of the question > there may not be a sensible answer. Please post DDL in future so that > we don't have to guess. > > Take a look at the ROW_NUMBER function in Books Online. I'm assuming > 2005 because you haven't said otherwise. > > -- > David Portas, SQL Server MVP > > Whenever possible please post enough code to reproduce your problem. > Including CREATE TABLE and INSERT statements usually helps. > State what version of SQL Server you are using and specify the content > of any error messages. > > SQL Server Books Online: > http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx > -- > >  |
|||||||||||||||||||||||
Other interesting topics