I have some data in a table where the history got incorrectly handled because of blanks/spaces coming in one column segment_value_cd.
I have to identify such records from the table.
I tried few queries but it;s fetching me the entire results. I was trying something like this.
SELECT * FROM PARTY_SEGMENTATION_HIST QUALIFY COALESCE(MIN(SEGMENT_VALUE_CD) -- PREVIOUS CODE OVER (PARTITION BY PARTY_ID, SEGMENT_TYPE_CD ORDER BY SEGMENTATION_START_DT DESC ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING), '---') <> SEGMENT_VALUE_CD WHERE SEGMENT_VALUE_CD IN ( NULL, ' ')
Is there any way to identify only such records?
Sel * from party_segment where party_id in(6303031,6824664,216502393,6916270) id Segment_Type_Cd Segment_Value_Cd Segment_Start_Dt Segment_End_Dt 6,303,031 MB 3/20/2013 6/7/2015 6,303,031 MB ? 6/7/2015 ? 6,824,664 MB 3/20/2013 6/7/2015 6,824,664 MB ? 6/7/2015 ? 6,916,270 MB ? 9/28/2015 ? 6,916,270 MB 3/20/2013 9/28/2015 216,502,393 NR ? 6/7/2015 ? 216,502,393 NR 8/7/2010
I have to merge those two records into one.
In this data example, for any given pair you retrieve both records because:
For the first row of the pair, there is no preceding row, so the min preceding Segment_Value_Cd will be null, and it will coalesce to '---', which is not equal to blanks; and
for the second row of the pair, the min preceding Segment_Value_Cd is blank, which is not equal to null.
If you want to combine the pairs, I think it would be something like:
select party_id, segment_type_cd, segment_value_cd, min(segment_start_dt) over (rows between 1 preceding and 1 preceding) as first_seg_start_dt, segment_end_dt
where first_seg_start_dt is not null
"where first_seg_start_dt is not null" lets you ignore the first row in the pair.