Identify the incorrect history handled records

Database
Enthusiast

Identify the incorrect history handled records

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.

Thanks.

1 REPLY
Teradata Employee

Re: Identify the incorrect history handled records

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.