Admision.dbf AdmCode – FileNum – OkXPha – XphaDate 1 - 10 - .T. - 88/01/01 2 - 11 - .T. - 88/01/02 Drg_Main.dbf AdmCode – Fee40 - Fee41 - Fee42 1 - 100 - 0 - 0 1 - 200 - 0 - 0 2 - 300 - 0 - 0 2 - 400 - 0 - 0 2 - 500 - 0 - 0 Drg_Main_Ret.dbf AdmCode – Fee31 1 - 50I have a SQL statement as below :
SELECT Admision.AdmCode, Admision.FileNum, Admision.OkXPha, Admision.XPhaDate AS 'RecDate', ; SUM(Drg_Main.Fee40-Drg_Main.Fee41+Drg_Main.Fee42) AS 'SumFee', ; SUM(Drg_Main_Ret.Fee31) AS 'SumRet' ; FROM Admision ; LEFT OUTER JOIN Drg_Main ON Admision.AdmCode = Drg_Main.AdmCode ; LEFT OUTER JOIN Drg_Main_Ret ON Drg_Main.AdmCode = Drg_Main_Ret.AdmCode ; WHERE Admision.OkXPha = .T. ; GROUP BY Admision.AdmCode ; ORDER BY Admision.XPhaDate,Admision.AdmCode ; INTO CURSOR &Crs_DrgRep02 READWRITEThis must to extract data from 3 table and I want below result.
AdmCode – FileNum – OkXPha – RecDate – SumFee – SumRet 1 - 10 - .T. - 88/01/01 - 300 - 50 2 - 11 - .T. - 88/01/02 - 1200 - 0I know that this SQL is wrong. Please help me to correct and optimize this.