I'm having trouble with a query that is supposed to grab a field from each of my
tables. and set up a Total line at the bottom of each set.
I have:
Insurance Company table (insco)
Patient table (patient)
Procedure table (procrec)
Charges table (feeProfiles)
Patient Coverage table (coverage)
Procedure Code table (cpt)
I need to get:
Insurance company name (insco.insconame)
Patient ID & full name built from first and last fields (patient.id,
CONCAT(patient.ptlname, ", ", patient.ptfname) )
Procedure date (procrec.procdt)
Procedure Codes (cpt.id)
Expected payment total for each procedure and a total for each insurance company (
SUM(feeProfile.expected) )
Currently, I get all of the fields that I need, but some of the totals at the end
either don't show up or they are not correct.
Here's my current query:
(SELECT insco.insconame, pt.id, CONCAT_WS(", ", pt.ptlname, pt.ptfname) name,
cpt.cptcode , DATE_FORMAT(proc.procdt, "%m-%d-%Y") procdt, FORMAT(SUM(fee.expected),
2) sum_expected
FROM procrec AS proc, patient AS pt, cpt, feeProfiles AS fee, coverage, insco
WHERE proc.procpatient = pt.id AND cpt.id = proc.proccpt AND cpt.id = fee.cpt_ID
AND coverage.covpatient = pt.id AND coverage.covinsco = insco.id
GROUP BY insco.insconame, CONCAT_WS(", ", pt.ptlname, pt.ptfname),
DATE_FORMAT(proc.procdt, "%m-%d-%Y"))
UNION
(SELECT DISTINCT insco.insconame, NULL id, NULL name, NULL cptcode,
NULL procdt, FORMAT(SUM(fee.expected), 2) sum_expected
FROM feeProfiles AS fee, cpt , procrec, patient, coverage, insco
WHERE procrec.procpatient=patient.id AND cpt.id = fee.cpt_ID AND procrec.proccpt =
cpt.id
AND coverage.id = procrec.proccurcovid AND coverage.covinsco = insco.id GROUP BY
insco.insconame)
ORDER BY insco.insconame
Is there an easier way to do this on mysql version 4.18 or should I rework my entire
query?
Thanks,
Craig
