Flag one of mulitple records as 'N'. Possible??? 2

[hneal98]

Hi all,

I have a situation where I have records that could show multiple times in a query due to multiple claim ids. However, I have set up a field as a Y/N flag where I only want to flag one as Y, the others I want to be N. It does not matter which is selected as Y, but just one.

Example:

MemberID ClaimID Flag
1 123455 Y
1 123456 N
1 123457 N
2 246855 Y
2 246859 N
2 246873 N
3 369332 N
3 369347 Y

Again, it does not matter which one is flagged as Y.

Thanks for any help.

 

[ptheriault]

Have you tried SELECT DISTINCT?
I don't know your data but duplicate data is usually not a good thing. Have you tried to remove the dupes?

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[hneal98]

That would not work since the claimid is different.

 

[monksnake]

I wrote up an update statement that would make at exactly one of each of your memberID's have a flag of 'Y'. The code will only add one 'Y', it won't take a 'Y' away if 2 or more have 'Y' for a flag, but based on the code, you should be able to figure out how to do that.


Test data, with update and results, on a platter.

declare @table1 table(memberID int, claimID int, flag varchar(1))

insert into @table1 values (1, 123455, 'Y')
insert into @table1 values (1, 123456, 'N')
insert into @table1 values (1, 123457, 'N')
insert into @table1 values (2, 246855, 'N')
insert into @table1 values (2, 246859, 'N')
insert into @table1 values (2, 246873, 'N')
insert into @table1 values (3, 369332, 'N')
insert into @table1 values (3, 369347, 'N')
insert into @table1 values (3, 369363, 'N')


update @table1 set flag = 'Y' from @table1 a where a.claimID in (select top 1 aa.claimID from @table1 aa where (select count(*) from @table1 where memberID = aa.memberID and flag = 'Y') = 0 and aa.memberID = a.memberID)

select * from @table1

<.

 

[hneal98]

Thank you. That works perfectly.

I know this is easy stuff for most of you, and I really do appreciate the efforts everyone puts into these posts.

Thanks again.

 

[monksnake]

I know this is easy stuff for most of you

Not real easy for me, so posts like yours give me the chance to test myself and get better.

<.

 

[hneal98]

You were one up on me. Thanks again.

 

[SQLSister]

Consider if this should be in a trigger to prevent re-occurance of the problem. We have several tables where one record and only one record of many has to have a particular value and we have a trigger that manages that. If it is the first record, it gets the value no matter aht was input, if it is an update to that field to the one required value, it changes the field that currently has the value to the other value and then updates and if the record with the value is deleted , it looks to see if there is another record that could get that value. Well ther is some more to it, but you get the idea. Really worth the time to think out the process and set up a trigger like this.

Questions about posting. See faq183-874

 

[hneal98]

SQLSister,

Thanks for the info. This is great to know in the future, however for this project, I don't think it is necessary. There are actually certain criteria that I need this for, and it is all set up in a stored procedure.

This was actually a little more complex than I had stated above, but I was able to get that all worked out.

Thanks everyone for your help an comments.