OK. I really stink at SQL (one of the reasons I chose web2py - I come from a different programming background and just can't seem to wrap my head around queries). Here's as far as I was able to get in trying to code this using db.executesql:
q=''' select distinct m.MemberID, m.EventDate from ( select id, MemberID, max(EventDate) as maxdate from MemberEvents where EventDate< '%s' group by memberID ) as x inner join MemberEvents as m on m.MemberID = x.MemberID and m.EventDate = x.maxdate ''' % targetDate test = db.executesql(q) That query runs without errors and returns the results that I want, but I need to do a left join on the results of the query w/ my Members table so that I'm not leaving out Members that have no events before the target date (Null values in the MemberEvents fields are fine for these members). If someone could help with that, then I'd have a complete SQL query ready convert to DAL (if possible). Thanks, - Joe G. On Jun 21, 11:52 am, mdipierro <mdipie...@cs.depaul.edu> wrote: > Look at the sql it generates. Figure out what is wrong with it and > figure out what the correct should look like. When we can try > implement it in dal. >
