How to identify first time and last time for same surgeon by date?

[arhawley]

I am attempting to write a report which requires the ability to identify a surgeon's {v_basic_case_rec_data2.surg_descr} first in room time {v_CRA_Billing_01.time_pat_in_room} and last room out time {v_CRA_Billing_01.time_pat_out_of_room}.. A surgeon can have multiple pat in room times and out room times for each day the surgeon has worked in the OR.
I have limited skills in crystal but have written several simplistic reports which have proven to be beneficial.. The end result I am attempting to gain is.. when was the surgeon first in the OR.. and when did he finally leave after a hard days work.. then identify the times between the two values for each day and identify if his utilization could be improved..
Thank you for any assistance.. Rose

 

[DJWheezyWeez]

What type are the time_pat_in_room / out_room fields? Are they datetime fields? Just time? Is it a number in seconds since midnight? Any information is helpful.

Basically what you'll want to do is group by surg_descr then do a minumum for time in and maximum for time out. Problem is you can't add/subtract a sum. There are ways around it but it depends on the format.

-DJWW

 

[fisheromacse]

try a SQL expression for each time.

it sounds like you would want the minimum for {v_CRA_Billing_01.time_pat_in_room} and maximum for {v_CRA_Billing_01.time_pat_out_of_room} for each {v_basic_case_rec_data2.surg_descr}


//{%Max}
(
select max("time_pat_in_room")
from v_CRA_Billing_01 a
where ("v_CRA_Billing_01"."surg_descr" = a."surg_descr")
)


//{%Min}
(
select min("time_pat_out_of_room")
from v_CRA_Billing_01 a
where ("v_CRA_Billing_01"."surg_descr" = a."surg_descr")
)


I do not have crystal in front of me, but the above should be close. I am certain that someone here can correct any typos/mistakes i made.

 

[arhawley]

DJWW the time pulls as a text field..
I have the work around for the times and calculating the difference..
I have the rpt grouped by Surgeon.. Sorry should have mentioned this.. however when I pull the min and max in.. however it does not filter for just that particular surgeon.. it pulls for for the entire query.. so I also under surgeon sorted by date.. same thing occurs.. Thanks Rose

 

[DJWheezyWeez]

Are you putting the min/max in the surg_descr group footer? That should return the min/max time for each surgeon. If you put it in the report footer, it will return the min/max for all surgeons.

 

[arhawley]

fisheromacse I am using Crystal 9.2.2.634 Standard.. I am unable to use the SQL but thank you

 

[arhawley]

I am placing min/max in the Group header.. currently I have details also visible with the correct case record in room and out room time for each surgeon visible.. just to be able to validate this formula.. I just cked.. irregardless of where I place the Min/Max.. it shows the same.. Maybe it has something to do with how I linked the tables? going to ck my joins.. Rose

 

[arhawley]

Joins are good.. This is how I have it currently built..

The report is grouped by surgeon{v_basic_case_rec_data2.surg_descr} and In "GH1" I surgeon showing..
I have under Record Sort Order I have {v_basic_case_rec_data2.cr_prdate} I have this showing under "Da"
and here I am displaying the Min / Max formulas.. yet they are displaying for the entire query..
Under Db I have all of the case record times populating...
Either out of an act of desperation or ignorance this is the formula I am using for Min:
//Surgeon Min time by day
if({v_basic_case_rec_data2.surg_descr}={v_basic_case_rec_data2.surg_descr})
and {v_basic_case_rec_data2.cr_prdate}={v_basic_case_rec_data2.cr_prdate}then
(minimum({v_CRA_Billing_01.time_pat_in_room}))
form Max.. very similar as above..

Thank you again.. Rose

 

[DJWheezyWeez]

If I understand this, I think the problem is your min/max formula. I'm not sure why you're including "if ({v_basic_case_rec_data2.surg_descr} = {v_basic_case_rec_data2.surg_descr}) and {v_basic_case_rec_data2.cr_prdate} = {v_basic_case_rec_data2.cr_prdate} then" since something will always equal itself for each line but you should be able to take that out. If you use "minimum({v_CRA_Billing_01.time_pat_in_room}, {v_basic_case_rec_data2.surg_descr})" and put that in GF1, I think that'll work.

-DJWW

 

[arhawley]

I originally began with Minimum({v_CRA_Billing_01.time_pat_in_room}).. I did not realize one could.. enter a "," to relate it to the surgeon description..
Thank you.. it worked.. You are my Hero! for the day! Rose