How to compare rows among different columns using particular scenario

Database

How to compare rows among different columns using particular scenario

I have one table A which consists of two columns Route_Cd and Event_Actual_Dttm.
And other table B contains column as Src_Obj_Id

1)If Route_Cd and Event_Actual_Dttm are same for all Src_Obj_Id's which are coming from Table B
then we have to set flag as zero.

2)If Route_Cd is not same for all Src_Obj_Id's but Event_Actual_Dttm is same then we have to set flag to one

3)If Route_Cd is same for all Src_Obj_Id's but Event_Actual_Dttm is not same then flag should be two

Could you please tell me how can we achieve this?
Tags (1)
2 REPLIES
Enthusiast

Re: How to compare rows among different columns using particular scenario

Can you please elaborate it or give some sample records with final results that you want to achieve?

Ckp
Enthusiast

Re: How to compare rows among different columns using particular scenario

Hi Gaurav2512

Make sure one thing your flag can have more values then you expressed in question.

Below query will work for you :

SEL Route_cd,

Event_Actual_Dttm,

Src_Obj_Id,

CASE WHEN Route_cd=Event_Actual_Dttm AND Event_Actual_Dttm=Src_Obj_Id THEN 0

WHEN Route_cd<>Src_Obj_Id AND Event_Actual_Dttm=Src_Obj_Id THEN 1

WHEN Route_cd=Src_Obj_Id AND Event_Actual_Dttm<>Src_Obj_Id THEN 2

ELSE 3

END AS flag

FROM cktmpA A CROSS JOIN cktmpB B

ORDER BY flag