Group by date ranges?

Databases
1

I have this working query…


SELECT 
	patients.recordid AS patientid,
	CONCAT(personal.lastname, ', ', personal.firstname) AS name,
	SUM(charge) + SUM(debit) - SUM(credit) - SUM(payment) AS balance
FROM claims
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

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.

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.

2

if you divide the dateofservice unix timestamp (which is just an integer) by the right number, then integer arithmetic will result in a “floor” number, discarding any decimal portion of the dividend result, so you could add that to your GROUP BY clause

the right number, of course, would be the number of seconds in 30 days

:slight_smile: