Evaluating Rows and Returning Them

[Garyjr123]

I'm trying to write a report for my hospital's registration department where when scheduling makes a date change to an appointment the report will only return that row if certain criteria has been meet.

1)The appointment is a brand new appointment.
2)The appointment date has changed (the fields are datetime fields)not the time.

The two tables that record changes are apptaud and schedlog. When the appt (appt_id) is edited a new row in each of these tables is added. Can we write the report so that each row is evaluated on the change. Below are two examples of what I wrote for the Record Selection Expert currently but can not seem to get the right results.

Using Apptaud Table:

{res2.restype_id} = 2 and
{res2.resunit_id} in [5, 6] and
{apptaud.new_apptstatus_id} = 1 and
DateValue({apptaud.new_start_datetime}) <> DateValue({apptaud.old_start_datetime}) and
{apptaud.start_datetime} = {?StartDate}

Using the Schedlog Table:

{res2.restype_id} = 2 and
{res2.resunit_id} in [5, 6] and
{appt.scheduled_datetime} = {?StartDate} and

(
({schedlog.audittype_id} = 2)
or
({schedlog.audittype_id} = 1 and
{schedlog.reschedreason_id} in [1, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 2, 20, 21, 22, 3, 4, 5, 6, 7, 8, 9] and
DateValue({schedlog.scheduled_datetime}) <> DateValue({schedlog.scheduled_datetime}))
)

SQL Query for Schedlog Example:
SELECT "appt"."start_datetime", "res2"."resunit_id", "res2"."restype_id", "loc"."name", "pat"."name_display", "pattype"."abbr", "appt"."scheduled_datetime", "apptclass"."abbr", "appt"."appt_id", "pat"."birthdate", "visit"."pat_acct_num", "loc2"."abbr", "schedlog"."audittype_id", "schedlog"."reschedreason_id", "schedlog"."scheduled_datetime", "resunit"."abbr"
FROM (((("phsprod"."dbo"."res" "res2" INNER JOIN ((((((("phsprod"."dbo"."appt" "appt" INNER JOIN "phsprod"."dbo"."resunit" "resunit" ON "appt"."resunit_id"="resunit"."resunit_id") INNER JOIN "phsprod"."dbo"."patbooking" "patbooking" ON "appt"."appt_id"="patbooking"."appt_id") INNER JOIN "phsprod"."dbo"."visitapptlist" "visitapptlist" ON "appt"."appt_id"="visitapptlist"."appt_id") INNER JOIN "phsprod"."dbo"."schedlog" "schedlog" ON "appt"."appt_id"="schedlog"."appt_id") INNER JOIN "phsprod"."dbo"."facility" "facility" ON "resunit"."facility_id"="facility"."facility_id") LEFT OUTER JOIN "phsprod"."dbo"."loc" "loc2" ON "resunit"."resunit_id"="loc2"."loc_id") LEFT OUTER JOIN "phsprod"."dbo"."loc" "loc" ON "facility"."facility_id"="loc"."loc_id") ON "res2"."resunit_id"="resunit"."resunit_id") LEFT OUTER JOIN "phsprod"."dbo"."pat" "pat" ON "patbooking"."pat_id"="pat"."pat_id") LEFT OUTER JOIN "phsprod"."dbo"."apptclass" "apptclass" ON "patbooking"."apptclass_id"="apptclass"."apptclass_id") INNER JOIN "phsprod"."dbo"."visit" "visit" ON "visitapptlist"."visit_id"="visit"."visit_id") LEFT OUTER JOIN "phsprod"."dbo"."pattype" "pattype" ON "visit"."pattype_id"="pattype"."pattype_id"
WHERE "res2"."restype_id"=2 AND ("res2"."resunit_id"=5 OR "res2"."resunit_id"=6) AND ("appt"."scheduled_datetime">={ts '2011-06-06 00:00:00'} AND "appt"."scheduled_datetime"<{ts '2011-06-07 00:00:00'}) AND ("schedlog"."audittype_id"=2 OR "schedlog"."audittype_id"=1 AND ("schedlog"."reschedreason_id"=1 OR "schedlog"."reschedreason_id"=2 OR "schedlog"."reschedreason_id"=3 OR "schedlog"."reschedreason_id"=4 OR "schedlog"."reschedreason_id"=5 OR "schedlog"."reschedreason_id"=6 OR "schedlog"."reschedreason_id"=7 OR "schedlog"."reschedreason_id"=8 OR "schedlog"."reschedreason_id"=9 OR "schedlog"."reschedreason_id"=10 OR "schedlog"."reschedreason_id"=11 OR "schedlog"."reschedreason_id"=12 OR "schedlog"."reschedreason_id"=13 OR "schedlog"."reschedreason_id"=14 OR "schedlog"."reschedreason_id"=15 OR "schedlog"."reschedreason_id"=16 OR "schedlog"."reschedreason_id"=17 OR "schedlog"."reschedreason_id"=18 OR "schedlog"."reschedreason_id"=19 OR "schedlog"."reschedreason_id"=20 OR "schedlog"."reschedreason_id"=21 OR "schedlog"."reschedreason_id"=22))
ORDER BY "resunit"."abbr"

 

[lbass]

This is hard to follow, but you won't be able to pick out appointments that have changed dates if you only allow one date into the report. You might have better luck if you limit the report to new appointments only, and then group on patientID and use group selection (report->selection formula->GROUP):

distinctcount({@date},{table.patientID}) > 1

...where {@date} is a formula:

date({table.datetime})

Not sure how you know which date is the final appointment date since it could be earler or later, but if you have some sort of sequence number, you could then change the group selection to the following so you would pick out the current correct date:

distinctcount({@date},{table.patientID}) > 1 and
{table.seq} = maximum({table.seq},{table.patientID})

-LB

 

[Garyjr123]

Sorry LB,

Trying not to make this hard to follow but I failed miserably. Maybe showing you the table columns will help.

In the SCHEDLOG table:

SELECT [schedlog_id]
,[appt_id] \\Prinmary key in appt table, foreign key here
,[connect_id]
,[user_name]
,[start_datetime] \\posting datetime of appointment
,[stop_datetime]
,[audittype_id]
,[starting]
,[stop]
,[timestamp]
,[scheduled_datetime] \\actual appointment surgery date, last row of this column is the correct appointment surgery date
,[apptresched_id] \\the apptresched table has a last_schd_datetime and
,[reschedreason_id] FROM [schedlog]

SELECT [apptaud_id]
,[appt_id]
,[old_start_datetime] \\previous appointment start_datetime
,[new_start_datetime] \\new appointment start_datetime
,[is_start_datetime_changed] \\0 = NO 1 = Yes
,[old_duration]
,[new_duration]
,[is_duration_changed]
,[old_service_id]
,[new_service_id]
,[is_service_id_changed]
,[old_apptstatus_id]
,[new_apptstatus_id]
,[is_apptstatus_id_changed]
,[old_is_reqconfirm]
,[new_is_reqconfirm]
,[is_is_reqconfirm_changed]
,[start_datetime]
,[user_name]
,[connect_id]
,[old_resunit_id]
,[new_resunit_id]
,[is_resunit_id_changed]
,[is_selfsched]
,[old_requestby_id]
,[new_requestby_id]
,[is_requestby_id_changed]
,[old_comments]
,[new_comments]
,[is_comments_changed]
,[old_act_start_datetime]
,[new_act_start_datetime]
,[is_act_start_datetime_changed]
,[old_act_duration]
,[new_act_duration]
,[is_act_duration_changed]
,[old_is_patgrp]
,[new_is_patgrp]
,[is_is_patgrp_changed]
,[old_is_emerg]
,[new_is_emerg]
,[is_is_emerg_changed]
,[old_confirm_reason]
,[new_confirm_reason]
,[is_confirm_reason_changed]
,[old_confirm_desc]
,[new_confirm_desc]
,[is_confirm_desc_changed]
,[old_confirm_datetime]
,[new_confirm_datetime]
,[is_confirm_datetime_changed]
,[old_short_comment]
,[new_short_comment]
,[is_short_comment_changed]
,[is_long_comment_changed]
,[old_confirm_by_id]
,[new_confirm_by_id]
,[is_confirm_by_id_changed]
,[old_attending_phys_id]
,[new_attending_phys_id]
,[is_attending_phys_id_changed]
,[old_owner_id]
,[new_owner_id]
,[is_owner_id_changed]
,[old_sched_by_id]
,[new_sched_by_id]
,[is_sched_by_id_changed]
,[old_scheduled_datetime]
,[new_scheduled_datetime]
,[is_scheduled_datetime_changed]
,[is_confirm_comment_changed]
,[old_worklisted_by_id]
,[new_worklisted_by_id]
,[is_worklisted_by_id_changed]
,[old_is_multi_pro]
,[new_is_multi_pro]
,[is_is_multi_pro_changed]
,[old_conflict_comments]
,[new_conflict_comments]
,[is_conflict_comments_changed]
,[old_inbox_comments]
,[new_inbox_comments]
,[is_ibx_cmnts_changed]
,[old_orderstatus_id]
,[new_orderstatus_id]
,[is_orderstatus_id_changed]
,[old_is_order_received]
,[new_is_order_received]
,[is_is_order_received_changed]
,[old_consult_date]
,[new_consult_date]
,[is_consult_date_changed]
FROM [apptaud]

I'm thinking the apptaud.old_start_datetime and apptaud.new_start_datetime would fit the criteria you are looking for to evaluate the two datetimes.

 

[lbass]

Sure, use that and your selection formula is:

{res2.restype_id} = 2 and{res2.resunit_id} in [5, 6] and
{apptaud.new_apptstatus_id} = 1 and
Date({apptaud.new_start_datetime}) <>
Date({apptaud.old_start_datetime}) and //etc.


But I'm not sure about the last line and how the apptaud.start_datetime relates to the old and new datetimes. If the old start date always = {apptaud.start_datetime}, then this clause should have worked correctly:

{apptaud.start_datetime} = {?StartDate}

-LB

 

[Garyjr123]

LB,

The way that table sets up when an edit is made to an appt is a new row appears, so:

old_start_datetime new_start_datetime is_start_datetime_changed
row
1 null null 0
2 null 12-06-2011 00:00 1
3 12-06-2011 00:00 null 0
4 null 12-07-2011 00:00 1

apptaud.new_apptstatus_id tells us what the actual status is (scheduled, working etc.) which when it get's scheduled will be a 1.

The very last row with a is_start_datetime_changed and apptaud.apptstatus_id of 1 is the final scheduled surgical appointment. Then in the appt.scheduled_start_datetime (different table) we would see the datetime for the scheduled surgical appointment.

My question is (and I know not to question you) does the null value in either column (old_start_datetime & new_start_datetime) effect my data returned?

 

[Garyjr123]

LB,

Here is a better example of what I want.

From ApptAud table I did a SQL query to limit the columns
select appt_id, apptaud_id, old_start_datetime, new_start_datetime, is_start_datetime_changed,
old_apptstatus_id, new_apptstatus_id
from apptaud where appt_id = 'xxxx'
order by appt_id

so, the columns data are shown as follows above.

row
1 xxxx xxxx Null Null 0 Null 7
2 xxxx xxxx Null Null 0 7 5
3 xxxx xxxx Null 2010-12-24 14:00:00 1 5 1
4 xxxx xxxx 2010-12-24 14:00:00 Null 1 Null Null
5 xxxx xxxx Null Null 0 1 5
6 xxxx xxxx Null 2010-12-24 15:00:00 0 5 1
7 xxxx xxxx Null 2011-01-07 15:45:00 1 5 1

Now, I would want row 3 on my report (it is a new appointment) and row 7 (b/c it had a date change) not row 6 (b/c it was only a time change). You will see that all the apptaud.new_apptstatus_id of 1 (scheduled) has a date associated with it as well as apptaud.is_start_datetime of 1.

Hope this clears it up a bit.

 

[lbass]

It looks like this would do it:

is_start_datetime_changed = 1 and
not isnull(new_start_datetime)

-LB

 

[Garyjr123]

LB,

I created the record selection above in a subreport but now I want to link the main report parameter apptaud.start_datetime = {?StartDate} to the subreport. How do I do this?

Gary

 

[lbass]

Not sure why you are using a subreport, but how you link depends upon the placement of the subreport. If you want to use the parameter in the sub to limit records in the sub, then create the same parameter in the sub, add the parameter to the record selection formula, and then then link the two parameters to each other, by using the dropdown in the lower left of the subreport linking screen to select {?StartDate}, not the default {?pm-?StartDate}.

-LB

 

[Garyjr123]

LB,

I added this to my record selection but I'm still returning the rows that have just a time change when I truly want the row to return if the date has changed.

\\this is a posting date; if a date is entered in the parameter the report will return all the rows that have appointments scheduled that day (i.e. 6-9-11 will show all cases booked on that day; usually for later dates).

{apptaud.start_datetime} = {?StartDate} and
{res2.restype_id} = 2 and
{res2.resunit_id} in [5, 6] and
{apptaud.new_apptstatus_id} = 1 and
not isnull({apptaud.new_start_datetime})

I tried it this way and didn't return any rows.

{apptaud.start_datetime} = {?StartDate} and
{res2.restype_id} = 2 and{res2.resunit_id} in [5, 6] and{apptaud.new_apptstatus_id} = 1 andDate({apptaud.new_start_datetime}) <> Date({apptaud.old_start_datetime})

I think I've been working too hard on this report b/c I'm fried.

 

[lbass]

In your sample you showed {apptaud.new_apptstatus_id} = 0 when there was only a time change (row 6 above) in which case my formula should have worked.

You could use the formula:

{apptaud.start_datetime} = {?StartDate} and
{res2.restype_id} = 2 and
{res2.resunit_id} in [5, 6] and
{apptaud.new_apptstatus_id} = 1 and
not isnull({apptaud.new_start_datetime})

...and then add a group selection formula (report->selection formula->GROUP):

maximum({@date},{patientID}) <> minimum({@date},{patientID})

...where {@date} is a formula:

date({apptaud.new_start_datetime})

-LB

 

[Garyjr123]

LB,

Again, sorry...did not mean to mislead. {apptaud.new_apptstatus_id} = 0 when there isn't any datetime change. {apptaud.new_apptstatus_id} = 1 when either there is a date or time change or both.

I will give the group formula selection a shot and let you know how it went.

Thanks,

Gary

 

[Garyjr123]

LB,

This report is giving me a migraine.

Ok...I did everything you told me to and when I add the group selection, I do not return any rows and I think it has to do with the max and min functions. How do those functions evaluate the data? I'm thinking about just starting from scratch with the report, rebuilding it and trying your formulas/group/record selections again. Any thoughts? I added the SQL query of my final attempt below with all the elements you told me to add.

SELECT "appt"."start_datetime", "res2"."resunit_id", "res2"."restype_id", "loc"."name", "pat"."name_display", "pattype"."abbr", "apptclass"."abbr", "appt"."appt_id", "pat"."birthdate", "visit"."pat_acct_num", "loc2"."abbr", "resunit"."abbr", "apptaud"."new_apptstatus_id", "apptaud"."new_start_datetime", "apptaud"."start_datetime", "apptaud"."apptaud_id"
FROM (((("phsprod"."dbo"."res" "res2" INNER JOIN ((((((("phsprod"."dbo"."appt" "appt" INNER JOIN "phsprod"."dbo"."resunit" "resunit" ON "appt"."resunit_id"="resunit"."resunit_id") INNER JOIN "phsprod"."dbo"."patbooking" "patbooking" ON "appt"."appt_id"="patbooking"."appt_id") INNER JOIN "phsprod"."dbo"."visitapptlist" "visitapptlist" ON "appt"."appt_id"="visitapptlist"."appt_id") LEFT OUTER JOIN "phsprod"."dbo"."apptaud" "apptaud" ON "appt"."appt_id"="apptaud"."appt_id") INNER JOIN "phsprod"."dbo"."facility" "facility" ON "resunit"."facility_id"="facility"."facility_id") LEFT OUTER JOIN "phsprod"."dbo"."loc" "loc2" ON "resunit"."resunit_id"="loc2"."loc_id") LEFT OUTER JOIN "phsprod"."dbo"."loc" "loc" ON "facility"."facility_id"="loc"."loc_id") ON "res2"."resunit_id"="resunit"."resunit_id") LEFT OUTER JOIN "phsprod"."dbo"."pat" "pat" ON "patbooking"."pat_id"="pat"."pat_id") LEFT OUTER JOIN "phsprod"."dbo"."apptclass" "apptclass" ON "patbooking"."apptclass_id"="apptclass"."apptclass_id") INNER JOIN "phsprod"."dbo"."visit" "visit" ON "visitapptlist"."visit_id"="visit"."visit_id") LEFT OUTER JOIN "phsprod"."dbo"."pattype" "pattype" ON "visit"."pattype_id"="pattype"."pattype_id"
WHERE ("apptaud"."start_datetime">={ts '2011-06-10 00:00:00'} AND "apptaud"."start_datetime"<{ts '2011-06-11 00:00:00'}) AND "res2"."restype_id"=2 AND ("res2"."resunit_id"=5 OR "res2"."resunit_id"=6) AND "apptaud"."new_apptstatus_id"=1 AND "apptaud"."new_start_datetime" IS NOT NULL
ORDER BY "resunit"."abbr", "apptaud"."apptaud_id", "appt"."start_datetime"


Record Selection:

{apptaud.start_datetime} = {?StartDate} and
{res2.restype_id} = 2 and
{res2.resunit_id} in [5, 6] and
{apptaud.new_apptstatus_id} = 1 and
not isnull({apptaud.new_start_datetime})

Group Selection:

maximum({@Date},{apptaud.apptaud_id}) <> minimum({@Date},{apptaud.apptaud_id})

(@Date): date({apptaud.new_start_datetime})

 

[lbass]

I think you are using the wrong group field in your group selection formula. You should be grouping on appointment ID--I'm guessing that the apptaud_ID is unique to each row in the table, not to the appointment ID (there should be a set of rows).

-LB