Need to add flag for connecting records

Database

Need to add flag for connecting records

Hi,

I have a scenario where i need to pull some conecting records and highlight them with proper flags to mention to which it belongs to.

Below is the sample input data and exepected output.

 

Input:
AC|FN|SSC|StartDate|StartTime|SEC|EndDate|EndTime|EffectiveDate|DiscontinueDate
PR |127|JFK|04/11/2017|00:15:00|YVR|04/11/2017|03:30:00|04/11/2017|12/31/9999|
PR |127|YVR|04/11/2017|05:15:00|MNL|04/12/2017|07:40:00|04/11/2017|12/31/9999|
PR |127|JFK|04/12/2017|00:15:00|YVR|04/12/2017|03:30:00|04/11/2017|12/31/9999|
PR |127|YVR|04/12/2017|05:15:00|MNL|04/13/2017|08:55:00|04/11/2017|12/31/9999|
PR |127|MNL|04/12/2017|21:35:00|QPX|04/12/2017|22:35:00|04/11/2017|12/31/9999|

 

Output:
AC|FN|SSC|StartDate|StartTime|SEC|EndDate|EndTime|EffectiveDate|DiscontinueDate|FLAG(Some)
PR |127|JFK|04/11/2017|00:15:00|YVR|04/11/2017|03:30:00|04/11/2017|12/31/9999|1|
PR |127|YVR|04/11/2017|05:15:00|MNL|04/12/2017|07:40:00|04/11/2017|12/31/9999|1|
PR |127|MNL|04/12/2017|21:35:00|QPX|04/12/2017|22:35:00|04/11/2017|12/31/9999|1|
PR |127|JFK|04/12/2017|00:15:00|YVR|04/12/2017|03:30:00|04/11/2017|12/31/9999|2|
PR |127|YVR|04/12/2017|05:15:00|MNL|04/13/2017|08:55:00|04/11/2017|12/31/9999|2|

 

Thanks,
Mallesh

1 REPLY
Teradata Employee

Re: Need to add flag for connecting records

It is not obvious to me how you decide what value to assign to Flag, but if the criterion is somewhere in the other columns then you can use a CASE operation:

Select AC, FN, SSC, ..., DiscontinueDate, (CASE when <some-condition> then 1 when <some-other-condition> then 2 else 0 END) as Flag