Here's the query. You need to stick in appropriate values for the '?' for 1. schedule.entity_id=? - the schedule id - you can get this from entities 2. any activity_start_time. E.g p.activity_start_time<? - these should all be the same 3. any activity_end_time. E.g schedule.activity_end_time>? - these should all be the same

The query looks a little odd as the database structure has been denormalised to improve query times.

-Tim

select a.act_id, p.activity_start_time, p.activity_end_time, d.name, d.type, d.value, a.status, a.reason, a.description, e.arch_short_name, e.arch_version, e.entity_id, e.name from acts a join participations p on a.act_id=p.act_id and a.arch_short_name='act.customerAppointment' and p.act_arch_short_name='act.customerAppointment' join entities e on p.entity_id=e.entity_id join participations schedule on a.act_id=schedule.act_id and schedule.arch_short_name='participation.schedule' left outer join act_details d on a.act_id=d.act_id where (p.activity_start_time<? and p.activity_end_time>? or p.activity_start_time<? and p.activity_end_time>? or p.activity_start_time>=? and p.activity_end_time<=?) and schedule.entity_id=? and (schedule.activity_start_time<? and schedule.activity_end_time>? or schedule.activity_start_time<? and schedule.activity_end_time>? or schedule.activity_start_time>=? and schedule.activity_end_time<=?) order by p.activity_start_time, a.act_id

mpcosta@boroniavet.com.au wrote:

> Hi everyone, > If this is not a simple cut and paste answer, ignore the question. I > can work my way through it. But I was wondering if the query that > generates a list of schedule acts for a particular schedule could be > dropped here. > I'm looking to produce a report for printing a particular days > schedule complete with patient details etc (request from user some way > back). > > Cheers, > Matt C > _______________________________________________ > OpenVPMS Developers Mailing List > developers@lists.openvpms.org > To unsubscribe or change your subscription visit: > http://lists.openvpms.org/mailman/listinfo/developers > Posts from this mailing list can be viewed online and replied to in > the OpenVPMS Developer's forum- http://tinyurl.com/openvdf >

_______________________________________________ OpenVPMS Developers Mailing List developers@lists.openvpms.org To unsubscribe or change your subscription visit: http://lists.openvpms.org/mailman/listinfo/developers Posts from this mailing list can be viewed online and replied to in the OpenVPMS Developer's forum- http://tinyurl.com/openvdf