SitePoint Sponsor

User Tag List

Results 1 to 2 of 2
  1. #1
    I solve practical problems. bronze trophy
    Michael Morris's Avatar
    Join Date
    Jan 2008
    Knoxville TN
    65 Post(s)
    0 Thread(s)

    Group by date ranges?

    I have this working query..

    	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. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Toronto, Canada
    60 Post(s)
    3 Thread(s)
    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 | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts