Getting the max record in a Join?

[fmrock]

Hey everyone,

I have the following query. You will see I have the last join commented out and the last 2 fields commented out. I basicly need to add to the existing report the user who made the last update and I am not sure what the best way to do this is.

SELECT DISTINCT
	C.CPCODE "Group"
	, C.ACCOUNT "Account"
	, to_date(A.ACCTLOGDATE,'j') "Log_Date"
	, C.REJREASON "Rejection"
	, A.ACCTBALANCE "Balance"
	, A.ACCTBILLSTATUS "FC"
	, C.DOCCODE "Attending_Doc"
	, C.OPDOCCODE "Performing_Doc"
	, to_date(C.REJDATE,'j') "Rejection_Date"
	, AI.AIINSSEQ "Ins_Seq"
	, AI.AICOMPANY "Ins_Code"
	, I.INSTYPE "Ins_Type"
	, I.INSMASTERSTATE "Ins_Master_State"
	, L.LOCSTATE "Location_State"
	, C.PRCODE "PRCode"
	, C.INSCODE "Chrg_Ins_Code"
	, to_date(A.ACCTSETUP,'j') "Account_Setup_Date"
	, I.INSMASTER "Master_Code"
	, A.ACCTSCANINDEX "Chart_Num"
	, CL.CLSTATUS "Claim_Status"
	--, T.CHANGEDATE "Last_Changed_Date"
	--, T.CHANGEBY "Last_Changed_By"
FROM   
	MEDACCOUNT A 	
	JOIN MEDACCTINS AI ON ((A.ACCTCPCODE=AI.AICPCODE) AND (A.ACCTCODE=AI.AIACCTCODE))
	JOIN MEDCHARGES C ON ((C.CPCODE=AI.AICPCODE) 
							AND (C.ACCOUNT=AI.AIACCTCODE) 
							AND (C.SPLITFLAG IS NULL) 
							AND (C.TYPE='C'))	
	JOIN MEDLOCATIONS L ON ((C.CPCODE=L.LOCCPCODE) AND (C.DOCLOC=L.LOCCODE))	
	LEFT JOIN MEDCLAIMS CL ON ((AI.AICPCODE=CL.CLCPCODE) AND (AI.AIACCTCODE=CL.CLACCTCODE) AND (AI.AICOMPANY=CL.CLINSCODE))								
	LEFT JOIN MEDINSURANCE I ON (AI.AICOMPANY=I.INSCODE)
	--LEFT JOIN MEDTRACE T ON ((A.ACCTCPCODE=T.CPCODE) AND (A.ACCTCODE=T.ACCOUNT))
WHERE  
	(A.ACCTBALANCE > 0) 
	AND (L.LOCSTATE<>I.INSMASTERSTATE)

 

[jaxtell]

This probably isn't the best way, but I think it'll work

SELECT DISTINCT
    C.CPCODE "Group"
    , C.ACCOUNT "Account"
    , to_date(A.ACCTLOGDATE,'j') "Log_Date"
    , C.REJREASON "Rejection"
    , A.ACCTBALANCE "Balance"
    , A.ACCTBILLSTATUS "FC"
    , C.DOCCODE "Attending_Doc"
    , C.OPDOCCODE "Performing_Doc"
    , to_date(C.REJDATE,'j') "Rejection_Date"
    , AI.AIINSSEQ "Ins_Seq"
    , AI.AICOMPANY "Ins_Code"
    , I.INSTYPE "Ins_Type"
    , I.INSMASTERSTATE "Ins_Master_State"
    , L.LOCSTATE "Location_State"
    , C.PRCODE "PRCode"
    , C.INSCODE "Chrg_Ins_Code"
    , to_date(A.ACCTSETUP,'j') "Account_Setup_Date"
    , I.INSMASTER "Master_Code"
    , A.ACCTSCANINDEX "Chart_Num"
    , CL.CLSTATUS "Claim_Status"
    --, T.CHANGEDATE "Last_Changed_Date"
    --, T.CHANGEBY "Last_Changed_By"
FROM   
    MEDACCOUNT A     
    JOIN MEDACCTINS AI ON ((A.ACCTCPCODE=AI.AICPCODE) AND (A.ACCTCODE=AI.AIACCTCODE))
    JOIN MEDCHARGES C ON ((C.CPCODE=AI.AICPCODE) 
                            AND (C.ACCOUNT=AI.AIACCTCODE) 
                            AND (C.SPLITFLAG IS NULL) 
                            AND (C.TYPE='C'))    
    JOIN MEDLOCATIONS L ON ((C.CPCODE=L.LOCCPCODE) AND (C.DOCLOC=L.LOCCODE))    
    LEFT JOIN MEDCLAIMS CL ON ((AI.AICPCODE=CL.CLCPCODE) AND (AI.AIACCTCODE=CL.CLACCTCODE) AND (AI.AICOMPANY=CL.CLINSCODE))                                
    LEFT JOIN MEDINSURANCE I ON (AI.AICOMPANY=I.INSCODE)
    LEFT JOIN (select CHANGEDATE , CHANGEDBY, CPCODE from MEDTRACE cir where exists (select max(CHANGEDATE ), CPCODE from MEDTRACE cir2 where cir.CPCODE = cir2.CPCODE having max(cir2.CHANGEDATE ) = cir.CHANGEDATE group by CPCODE )) T ON ((A.ACCTCPCODE=T.CPCODE) AND (A.ACCTCODE=T.ACCOUNT))
WHERE  
    (A.ACCTBALANCE > 0) 
    AND (L.LOCSTATE<>I.INSMASTERSTATE)

-----------------------------------------
I cannot be bought. Find leasing information at

http://www.joshaxtell.com/

 

[fmrock]

Thanks, I will give it a shot. I was a little worried about doing it that way as the query with the Trace table removed takes about 30 minutes to run durning the off hours.