DateDiff calculation not working properly

[feller]

Ok, this is complicated - ready for a challenge? Using Crystal 8.5. I have a datetime field that I wanted to extract just the time information from, so I created a formula field called @PatOrInTime as:

Time ({caseintraop.pat_or_in_datetime})

this works just fine - displays as I want on the report

I then created a "standard" time formula field called @SchedTime as:

Time ('07:30AM')

this also works just fine and displays correctly on the report. Both are formatted to be HH:MM I want to do a time difference and display the result as a number in minutes between these two formula fields. Here's what I've tried:

if not isnull({casemain.schedcase_start_datetime}) and not isnull({caseintraop.pat_or_in_datetime}) then
Formula = ({@SchedTime}-{@PatOrInTime})
end if

if not isnull({casemain.schedcase_start_datetime}) and not isnull({caseintraop.pat_or_in_datetime}) then
Formula = ToNumber({@SchedTime}-{@PatOrInTime})
end if

You can ignore the "isnull" portions - that is just to be sure that I will return correct data. Both give results, but, as an example, gives 86,220 as a result when it should only be 3 (minutes). What am I doing wrong?

 

[feller]

I've also created a whole new formula field and tried:

ToNumber (Time ('07:30AM') - Time ({caseintraop.pat_or_in_datetime}))

this also returns 86,220

 

[lbass]

The problem is because you are ignoring dates, I think. 86,220 seconds = 24 hours - 3 minutes, since there are 86,400 seconds in a day. Why not make it simple and use:

datediff("n",{caseintraop.pat_or_in_datetime},{casemain.schedcase_start_datetime})

Note that the sign on the result will differ depending upon which field you enter first into the formula. The date that is more current should be the last date ordinarily.

-LB

 

[feller]

what you suggested is exactly how it is on the original report (I've been tasked to re-create the report). The reason I'm wanting to "hard-code" in 07:30AM instead of using {casemain.schedcase_start_datetime} is a complicated issue. This database is used to track surgical cases scheduled in the OR. They want to see the "first cases" of the day to see what the delays are (i.e., the difference between the scheduled start time to the actual start time of the case). The assumption is that if the first case is delayed, it throws off the rest of the cases for the whole day... Anyway, some cases are scheduled in the database as 07:45AM, 07:50AM, etc. Even though they still all fall under the "first cases" which are in reality 07:30. I'm not a clinical person, so I don't really know how this works, but I'm told that even though a case is scheduled for 07:50, it's still considered 07:30 because they always fudge in a half hour or so for room setup, etc. If I use the actual case time instead of 07:30, it throws the number off because I have the "Scheduled Start Time" field on the report only show the date and have a text of "07:30" with the date - so all cases are shown as 07:30 even though they may have a real scheduled time of 07:45. Does that make sense? I know it's stupid and what really needs to be done is the cases need to all be scheduled for 07:30, but you're talking the basic users here

 

[lbass]

I think you are mixing two issues here. You can standardize the scheduled times however you like, but you still should use a datetime for your datediff formula. Surely there must be other scheduled times besides 7:30--why not incorporate the rules when creating the standardized scheduled time. If the rule is that anything between the half-hour (0:30 and the hour (0:59) belongs to the half-hour (and 0:0 to 0:29 belongs to the hour) then create a formula like {@schedtime}:

if time({table.datetime}) in time(hour({table.datetime}),0,0) to time(hour({table.datetime}),29,59) then datetime(year({table.datetime}),month({table.datetime}),day({table.datetime}),hour({table.datetime}), 0, 0) else
if time({table.datetime}) in time(hour({table.datetime}),30,0) to time(hour({table.datetime}),59,59) then datetime(year({table.datetime}),month({table.datetime}),day({table.datetime}),hour({table.datetime}), 30, 0)

Substitute your scheduled datetime field for {table.datetime}. Then for your datediff formula use:

datediff("n",{@schedtime},{caseintraop.pat_or_in_datetime})

Again, this will give you a positive number of minutes for how late the actual operation started. If you want to show this as a negative number of minutes, reverse the order of the datetime fields in the formula.

-LB

 

[feller]

this does seem to be working for the positive numbers - you'd said to reverse this for the negative numbers, but if I reverse it then the positive numbers get thrown off...

 

[lbass]

You have to decide whether to show a late operation as negative or positive minutes--that's all I meant. The last formula I gave you would should positive results if the operation was late, but negative results if it was early. If you switched the dates, the reverse would occur, positive minutes for early operations, negative for late ones.

I don't think you would want to make all results negative or all results positive, as this would not distinguish early and late operations--unless you are ONLY interested in the variation from schedule.

-LB

 

[feller]

I figured out what is wrong - I have the select expert to only pull scheduled start times between 07:30 and 08:05. I need any records that come in based on this criteria to revert back to 07:30. The script you gave me reverts any 08:00 or beyond to the hour instead of the half hour (i.e, appointments scheduled for 08:00 but started at 07:20. It results at -40 but should show -10 because I need it to revert to 07:30 instead of 08:00) What do I need to do to make it this way?

 

[lbass]

If you always using that select statement then just change {@schedtime} to:

datetime(year({table.datetime}),month({table.datetime}),day({table.datetime}),07, 30, 00)

-LB

 

[feller]

yes, that was it - I was attempting to "hard-code" 07:30 in by using @SchedTime as:

Time ('07:30AM')

my original posting shows this... Thank you, Thank you, Thank you!!! I know that in a week, they will see that this is totally screwy and the best way to take care of this is to actually have ACCURATE DATA instead of tweaking the report to make it look like accurate data... Oh well, job security