I'm trying to get my brain bent around this and I know I could write a mile of code, but I thought someone could find an easier solution.
I have a ton of records (nearly 2 million) that are sequenced by date. For some dates there is a single record, for some there are 2 records. There are never more than 2 for any date.
When the 2 values for a selected date are the same I want to set the value for both to missing ("."), but I want to keep the records in the table. I know I could sort and use "first." and "last.", or use a "lag", but I'm thinkin' there is a more elegant solution. Most solutions I come up with only get rid of the duplicate but I need to get rid of them both.
create table temp1
when b.date is not null then .
END) as new_data_value
from temp a
left outer join (
group by date
having count(*)>1) b on a.date=b.date