d3LL

Hi,I'm fairly new in SQL. Been trying for months to create the right script for this particular case but still cannot give me 100% result as required. Really appreciate your expertise. Sorry if my explaination below is quite lengthy. SCENARIO : I am required to query from 2 tables for those unique record that meets both conditions below:-1. Status is 1 @ max (trans_id), paychnl = CC2. Status is 2 @ max (trans_id), paychnl = A or B FYR, 2 tables and respective columns to query are as below:-table PTFF --> col ID, TRANS_ID,TRANSDATE,EFFDATE,TRANSCODEtable CHFF --> col STATUS,PAYCHNL FYI, status refers to the paychnl method status:-==> 1 means the current paychnl method==> 2 means the previous paychnl method paychnl method can be multiple because it will be defined as 2 for all the histories' paychnl chosen earlier, but 1 should only be unique as it is the latest paychnl chosen for each unique ID. however, it may appear more than once when it's taking those in earlier TRANSDATE, so here we would need the max trans_id as it will show the latest updated TRANSDATE. Apart from that, I need only those most recent paychnl to be A or B and the latest paychnl is CC so, this been indicated by the same max trans_id for the same ID. Aft trying so many times on this MAX command but failed to get any result, I only managed to come up to this part only. please refer below:-table PTFF --> col ID, TRANS_ID,TRANSDATE,EFFDATE,TRANSCODEtable CHFF --> col STATUS,PAYCHNL SELECT DISTINCT PTFF.TRANSCODE,PTFF.ID,PTFF.TRANS_ID,PTFF.TRANSDATE,PTFF.EFFDATE, CHFF.STATUS,CHFF.PAYCHNL FROM DBO.PTFF PTFF JOIN DBO.CHFF CHFF ON CHFF.ID = PTFF.ID WHERE PTFF.TRANSDATE BETWEEN 130501 AND 130831 AND PTFF.TRANSCODE='T522' AND (CHFF.STATUS=1 AND CHFF.PAYCHNL='CC' OR (CHFF.STATUS=2 AND (CHFF.PAYCHNL='A' OR CHFF.PAYCHNL='B'))) However, the script above returns :-1. All those records with STATUS 1 regardless paychnl is A or B in most recent status 2,2. Expected results also appear ==> 1 same ID with status 1 while paychnl=CC and status 2 while paychnl=A or B3. Also duplicates of expected results but for different TRANSDATE and not at MAX TRANS_ID Samples of the result as below. ID STATUS TRANS_ID PAYCHNL TRANSDATE EFFDATE TRANSCODE 5188 1 712 CC 130829 20130920 T522 9361 1 64 CC 130816 20140813 T522 7853 1 153 CC 130820 20130814 T522 8949 1 51 CC 130812 20130801 T522 8949 2 51 B 130812 20130801 T522 1908 1 455 CC 130516 20131129 T522 1908 2 455 A 130516 20131129 T522 1908 1 409 CC 111019 20111129 T522 1908 2 409 A 111019 20111129 T522 1908 1 404 CC 110929 20111129 T522 1908 2 404 B 110929 20111129 T522