I have this working query..
I would like to know if it is possible to further split the results of this query using procedures.dateofservice field, which is a unix timestamp. I would like to get 4 sum results for each claim, one for 0-30 days old, one for 31-60 days, one for 61-90 and finally a result for 91+ days. I know how to do this with 4 queries and then merge the results, but I'm curious if anyone knows if and how this could be done in one query.
patients.recordid AS patientid,
CONCAT(personal.lastname, ', ', personal.firstname) AS name,
SUM(charge) + SUM(debit) - SUM(credit) - SUM(payment) AS balance
LEFT OUTER JOIN bills ON ( bills.parentid = claims.recordid )
LEFT OUTER JOIN procedures ON ( procedures.parentid = bills.recordid )
LEFT OUTER JOIN ledger ON ( ledger.parentid = ledger.recordid )
LEFT JOIN episodeofcare ON ( claims.parentid = episodeofcare.recordid )
LEFT JOIN patients ON ( episodeofcare.parentid = patients.recordid )
LEFT JOIN personal ON ( personal.recordid = patients.parentid )
GROUP BY claims.recordid
EDIT: The last 3 joins are only to get to the patient name. The fields that are being summed up are on the ledger table.