HI!
I have data that is duplicated by a field that should be unique but is not. The whole record is distinct due to date field values and other CHARVARs.
Mem_ID is expected to be duplicative where appropriate, i.e. more than one uniqueID by Mem_Id.
Would like to keep just one record by UniqueID (earliest).
Ideally, min(admit) and set discharge date to max(discharge)
Have tried combinations of SELECT DISTINCT & MIN & MAX, but keep getting same number of records, obviously doing something wrong.
Thanks for any insight!
Here are some examples.
For example, UniqueID 5459110 is just bad data due to influences beyond the scope of my current needs and only need one record.
UniqueID Mem_Id admit discharge Status
5459110 135802 7/20/2010 7/27/2010 Some Description 1
5459110 135802 7/22/2010 7/27/2010 Some Description 1
13776129 478821 6/8/2011 6/12/2011 Some Description 4
13776129 478821 6/11/2011 6/12/2011 Some Description 1
14844766 478914 4/28/2011 5/2/2011 Some Description 2
14844766 478914 4/29/2011 5/2/2011 Some Description 1
14844766 478914 4/30/2011 5/2/2011 Some Description 1
14844766 478914 5/1/2011 5/2/2011 Some Description 1
14844766 478914 5/2/2011 5/2/2011 Some Description 3