date compare calculation

[mdurso26]

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

[swuehl]

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.

[maxgro]

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)

[MarcoWedel]

maybe

-(APPT_DTTM-APPT_CANC_DTTM<=1)

or

Sign(Floor(APPT_DTTM-APPT_CANC_DTTM))

(provided always APPT_DTTM>=APPT_CANC_DTTM)

regards

Marco

[mdurso26]

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)

[swuehl]

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.