Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi all! I'm looking for help in comparing the difference between two date fields to a third, static number. I have two fields (APPT_DTTM and APPT_CANC_DTTM) that I need to find the difference between, then take that difference and compare it to a static number (24 hours). What would be the best way to go about this? The APPT_DTTM and APPT_CANC_DTTM fields are in the same format (example: 7/6/2009 9:28:44 AM shows as 40000.416666667 without a Timestamp conversion).
What I'm trying to do is show the percentage of appointments that have been cancelled within 24 hours (APPT_CANC_DTTM) of the scheduled appointment time (APPT_DTTM). To do this I think I would need to find the difference between the two times, compare that difference to 24 hours, and return a "1" if the difference is within 24 hours of the scheduled appointment time and a "0" if it is outside of 24 hours of the scheduled appointment time. I can then sum all the "1"'s and divide that by the total number of appointments to get my percentage; this part I can do, it's just getting those "1"'s I can't figure out.
Any help you can provide would be HUGELY appreciated!!
Thanks,
Matt
Your sample number for a timestamp returns 0706//2009 10:00:00 on my system, but basically, I think you should be fine with something like
=Sum( If( Fabs(APPT_DTTM - APPT_CANC_DTTM) <= 1,1,0))
Then divide by the number of appointments.
for time difference maybe you can use the "-"
1 is 24 hours = 1 day
if time difference >=1 then 1 else 0
if((APPT_DTTM - APPT_CANC_DTTM )>=1, 1, 0)
maybe
-(APPT_DTTM-APPT_CANC_DTTM<=1)
or
Sign(Floor(APPT_DTTM-APPT_CANC_DTTM))
(provided always APPT_DTTM>=APPT_CANC_DTTM)
regards
Marco
Thanks for your reply! I need to do the comparison part to other times in addition to 24 hours. I'm looking to compare the difference to 24 hours, 16 hours, 12 hours, 8 hours, 4 hours, 2 hours, and 1 hour within the scheduled appointment time. Would I do that like below?
24 hours
if((APPT_DTTM - APPT_CANC_DTTM )>=1, 1, 0)
16 hours
if((APPT_DTTM - APPT_CANC_DTTM )>=0.6667, 1, 0)
12 hours
if((APPT_DTTM - APPT_CANC_DTTM )>=0.5, 1, 0)
8 hours
if((APPT_DTTM - APPT_CANC_DTTM )>=0.3334, 1, 0)
4 hours
if((APPT_DTTM - APPT_CANC_DTTM )>=0.1667, 1, 0)
2 hours
if((APPT_DTTM - APPT_CANC_DTTM )>=0.0833, 1, 0)
1 hour
if((APPT_DTTM - APPT_CANC_DTTM )>=0.0416, 1, 0)
Exactely, the fractional part of a timestamp or time interval is stating part of a day, i.e. part of 24 hours.
12 hours = 12/24 = 1/2
2 hours = 2/24 = 1/12
etc.
edit: And remember to use aggregation functions if you need to aggregate multiple records, e.g. in a chart expression or text box.
Like the suggested Sum(If( ....)) above.