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
Query on unique records with multiple criterias
in SQL
Posted
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.