Home › Forums › Developers › General developer's discussion ›

Acitivity report SQL

Submitted by Matt C on Sun, 18/05/2008 - 17:15
in
Hi, I'm trying to combine two data sets into one report using Ireport. I played a bit with subsets but couldn't get it to work.Is it possible to get the financial act summary totals present in query 1 as row totals in query2? Cheers, Matt Query 1: Period Financial activity SELECT entities.`name` AS clinician_name, sum(if(credit,-(financial_acts.total - financial_acts.tax_amount),(financial_acts.total - financial_acts.tax_amount)))as total FROM `acts` acts INNER JOIN `financial_acts` financial_acts ON acts.`act_id` = financial_acts.`financial_act_id` LEFT OUTER JOIN `participations` participations ON acts.`linkId` = participations.`act_linkId` INNER JOIN `entities` entities ON participations.`entity_linkId` = entities.`linkId` WHERE participations.arch_short_name = "participation.clinician" AND activity_start_time >= $P{startDate} AND activity_start_time <= date_add($P{endDate}, INTERVAL "23:59:59" HOUR_SECOND) AND (acts.arch_short_name LIKE "act.customerAccount%item") GROUP BY clinician_name ORDER BY clinician_name ASC Query 2: Period Scheduler activity SELECT entities1.name AS clinician_name, entities2.name AS pet_name, concat(entities3.name,' (',entities3.entity_Id,')') AS customer_name, date_format(acts.activity_start_time, '%a %c %b %Y') AS appt_day, date_format(acts.activity_start_time, '%H:%I') AS appt_time FROM acts LEFT JOIN participations AS participations1 ON (participations1.act_linkId = acts.linkId AND participations1.arch_short_name='participation.clinician') LEFT JOIN participations AS participations2 ON (participations2.act_linkId = acts.linkId AND participations2.arch_short_name='participation.patient') LEFT JOIN entities AS entities1 ON (entities1.linkId = participations1.entity_linkId) LEFT JOIN entities AS entities2 ON (entities2.linkId = participations2.entity_linkId) LEFT JOIN entity_relationships ON (entity_relationships.target_linkId = entities2.linkId) LEFT JOIN entities AS entities3 ON (entities3.linkId = entity_relationships.source_linkId AND entities3.arch_short_name = 'party.customerperson') WHERE acts.arch_short_name = "act.customerAppointment" AND acts.activity_start_time >= '2008-05-18' AND acts.activity_start_time <= date_add('2008-05-18', INTERVAL "23:59:59" HOUR_SECOND) ORDER BY clinician_name, appt_day, appt_time
‹ [OpenVPMS Developers] openvpms-framework 1.2-beta-1 released [OpenVPMS Developers] OpenVPMS 1.1 release now available ›

Comment viewing options

Select your preferred way to display the comments and click "Save settings" to activate your changes.

Re: [OpenVPMS Developers] Acitivity report SQL

Submitted by tanderson on Mon, 19/05/2008 - 17:01.
mpcosta@iprimus.com.au wrote: The easiest way may be to create a view of the first query. You can then join on this in the second query.

E.g,

create view clinician_totals as SELECT entities.linkId, entities.`name` AS clinician_name, sum(if(credit,-(financial_acts.total - financial_acts.tax_amount),(financial_acts.total - financial_acts.tax_amount)))as total FROM `acts` acts INNER JOIN `financial_acts` financial_acts ON acts.`act_id` = financial_acts.`financial_act_id` LEFT OUTER JOIN `participations` participations ON acts.`linkId` = participations.`act_linkId` INNER JOIN `entities` entities ON participations.`entity_linkId` = entities.`linkId` WHERE participations.arch_short_name = "participation.clinician" AND (acts.arch_short_name LIKE "act.customerAccount%item") GROUP BY clinician_name ORDER BY clinician_name ASC

And: SELECT ct.clinician_name, entities2.name AS pet_name, concat(entities3.name,' (',entities3.entity_Id,')') AS customer_name, date_format(acts.activity_start_time, '%a %c %b %Y') AS appt_day, date_format(acts.activity_start_time, '%H:%I') AS appt_time FROM acts LEFT JOIN participations AS participations1 ON (participations1.act_linkId = acts.linkId AND participations1.arch_short_name='participation.clinician') LEFT JOIN participations AS participations2 ON (participations2.act_linkId = acts.linkId AND participations2.arch_short_name='participation.patient') LEFT JOIN clinician_totals AS ct ON (ct.linkId = participations1.entity_linkId) LEFT JOIN entities AS entities2 ON (entities2.linkId = participations2.entity_linkId) LEFT JOIN entity_relationships ON (entity_relationships.target_linkId = entities2.linkId) LEFT JOIN entities AS entities3 ON (entities3.linkId = entity_relationships.source_linkId AND entities3.arch_short_name = 'party.customerperson') WHERE acts.arch_short_name = "act.customerAppointment" ORDER BY ct.clinician_name, appt_day, appt_time

> Hi, > > I'm trying to combine two data sets into one report using Ireport. I > played a bit with subsets but couldn't get it to work. Is it possible > to get the financial act summary totals present in query 1 as row > totals in query2? > > Cheers, > > Matt > > Query 1: Period Financial activity > SELECT > entities.`name` AS clinician_name, > sum(if(credit,-(financial_acts.total - > financial_acts.tax_amount),(financial_acts.total - > financial_acts.tax_amount)))as total > FROM > `acts` acts INNER JOIN `financial_acts` financial_acts ON > acts.`act_id` = financial_acts.`financial_act_id` > LEFT OUTER JOIN `participations` participations ON acts.`linkId` = > participations.`act_linkId` > INNER JOIN `entities` entities ON participations.`entity_linkId` = > entities.`linkId` > WHERE > participations.arch_short_name = "participation.clinician" > AND activity_start_time >= $P{startDate} > AND activity_start_time <= date_add($P{endDate}, INTERVAL > "23:59:59" HOUR_SECOND) > AND (acts.arch_short_name LIKE "act.customerAccount%item") > GROUP BY > clinician_name > ORDER BY > clinician_name ASC > > Query 2: Period Scheduler activity > SELECT entities1.name AS clinician_name, entities2.name AS pet_name, > concat(entities3.name,' (',entities3.entity_Id,')') AS customer_name, > date_format(acts.activity_start_time, '%a %c %b %Y') AS appt_day, > date_format(acts.activity_start_time, '%H:%I') AS appt_time > FROM acts > LEFT JOIN participations AS participations1 ON > (participations1.act_linkId = acts.linkId AND > participations1.arch_short_name='participation.clinician') > LEFT JOIN participations AS participations2 ON > (participations2.act_linkId = acts.linkId AND > participations2.arch_short_name='participation.patient')LEFT JOIN > entities AS entities1 ON (entities1.linkId = > participations1.entity_linkId)LEFT JOIN entities AS entities2 ON > (entities2.linkId = participations2.entity_linkId) > LEFT JOIN entity_relationships ON (entity_relationships.target_linkId > = entities2.linkId) > LEFT JOIN entities AS entities3 ON (entities3.linkId = > entity_relationships.source_linkId AND entities3.arch_short_name = > 'party.customerperson') > WHERE acts.arch_short_name = "act.customerAppointment" > AND acts.activity_start_time >= '2008-05-18' > AND acts.activity_start_time <= date_add('2008-05-18', INTERVAL > "23:59:59" HOUR_SECOND) > ORDER BY clinician_name, appt_day, appt_time > >

_______________________________________________ OpenVPMS Developers Mailing List developers@lists.openvpms.org http://lists.openvpms.org/mailman/listinfo/developers

Acivity Report SQL

Submitted by Matt C on Wed, 21/05/2008 - 13:41.

Hi Tim,
Thanks for that - I used your idea and created a subquery which returns a nested table to get the matched row totals to clinician_name.

The naming conventions could do with some work but here is the, working SQL (with arbitary date period instead of jasper report parameter tags for testing).

Matt C

PS. Dont use the WSIWYG editor as I did - it removed all my line breaks! Sorry about that :0

SELECT
entities1.`name` AS clinician_name, entities2.name AS pet_name,
ct.ct_clinician_total AS clinician_total,
concat(entities3.name,' (',entities3.entity_Id,')') AS customer_name,
date_format(acts.activity_start_time, '%a %c %b %Y') AS appt_day, date_format(acts.activity_start_time, '%H:%I') AS appt_time
FROM acts
LEFT JOIN participations AS participations1 ON (participations1.act_linkId = acts.linkId AND participations1.arch_short_name='participation.clinician')
LEFT JOIN participations AS participations2 ON (participations2.act_linkId = acts.linkId AND participations2.arch_short_name='participation.patient')
LEFT JOIN entities AS entities1 ON (entities1.linkId = participations1.entity_linkId)
LEFT JOIN entities AS entities2 ON (entities2.linkId = participations2.entity_linkId)
LEFT JOIN entity_relationships ON (entity_relationships.target_linkId = entities2.linkId)
LEFT JOIN entities AS entities3 ON (entities3.linkId = entity_relationships.source_linkId AND entities3.arch_short_name = 'party.customerperson')
LEFT JOIN (SELECT entities.`name` AS ct_clinician_name,
sum(if(credit,-(financial_acts.total - financial_acts.tax_amount),(financial_acts.total - financial_acts.tax_amount)))as ct_clinician_total
FROM `acts`
INNER JOIN `financial_acts` financial_acts ON acts.`act_id` = financial_acts.`financial_act_id`
LEFT OUTER JOIN `participations` participations ON acts.`linkId` = participations.`act_linkId`
INNER JOIN `entities` entities ON participations.`entity_linkId` = entities.`linkId`
WHERE participations.arch_short_name = "participation.clinician"
AND activity_start_time >= '2008-05-18' AND activity_start_time <= date_add('2008-05-18', INTERVAL "23:59:59" HOUR_SECOND)
AND (acts.arch_short_name LIKE "act.customerAccount%item")
GROUP BY entities.`name`
ORDER BY entities.`name` ASC) AS ct ON (ct.ct_clinician_name=entities1.`name`)
WHERE acts.arch_short_name = "act.customerAppointment"
AND acts.activity_start_time >= '2008-05-18' AND acts.activity_start_time <= date_add('2008-05-18', INTERVAL "23:59:59" HOUR_SECOND)
ORDER BY clinician_name, appt_day, appt_time

Syndicate content