CiviCRM Community Forums (archive)

I am doing a query where I get a list of all contacts who have donated in the current year but have not received a thank-you yet.  This works great, except now I want to get just the latest payment information (if they have made more than one payment w/out receiving a thank-you yet).

Below is my current query, can anyone help me come up with a new one that adds this extra functionality.  It seems like this one returns the first payment received that hasn't yet had a thank you date set.


    370 $query = "
    371             SELECT civicrm_contribution.contact_id,
    372             civicrm_contribution.id,
    373             civicrm_contribution.receive_date,
    374             civicrm_contribution.contribution_type_id,
    375             civicrm_contribution.total_amount
    376             FROM civicrm_contribution
    377             WHERE civicrm_contribution.receive_date >= '$year-01-01'
    378             AND civicrm_contribution.receive_date <= '$year-12-31'
    379             AND civicrm_contribution.thankyou_date IS NULL
    380             GROUP BY civicrm_contribution.contact_id
    381             ";

So this basically gets the contribution with the most recent "id" (as opposed to the most recent date).  I think that will work for now, but sometimes we enter checks from some time back or manually transfer donations from our current online donation data into CiviCRM so those ones would be earlier dates but more recent ids.  I'd like to see if anyone else has any other solutions too.  I have never really learned SQL, so I guess this might be the time to do it. 

I suppose there is also the brute force way (get every donation and do a search on the contact_id and receive_date) -- but that takes a lot of time for each request.