Case Statement Help

  • Thread starter Thread starter AccessDummy99
  • Start date Start date
A

AccessDummy99

Hi

I am trying to do edit an existing query to find the difference between 2
dates and times (equalling a duration). For example:

1. Arrival Date 2. Arrival Time 3. DoctorTimeSeen Date 4. DoctorTimeSeen
Time
01/07/2008 08:44:00 01/07/2008
08:44:00

This is the case statement below that's already been written for me and it
works when the 2 dates/times are different however when they are the same
like the example above it returns 1440 minutes (24 hours) instead of 0
minutes.

Can anyone help. I'm totally stumped.


CASE WHEN (((((cast(edeArrivalDate AS int)) + (cast(LEFT (edeDoctorTimeSeen
, 2) AS decimal) / 24) + (cast(substring(edeDoctorTimeSeen , 4 , 2) AS
decimal) / 24 / 60)) - ((cast(edeArrivalDate AS int)) + (cast(LEFT
(edeArrivalTime , 2) AS decimal) / 24) + (cast(substring(edeArrivalTime , 4 ,
2) AS decimal) / 24 / 60))) * 24 * 60)) < 1 THEN (((((cast(edeArrivalDate AS
int) + 1)) + (cast(LEFT (edeDoctorTimeSeen , 2) AS decimal) / 24) +
(cast(substring(edeDoctorTimeSeen , 4 , 2) AS decimal) / 24 / 60)) -
((cast(edeArrivalDate AS int)) + (cast(LEFT (edeArrivalTime , 2) AS decimal)
/ 24) + (cast(substring(edeArrivalTime , 4 , 2) AS decimal) / 24 / 60))) * 24
* 60) ELSE ((((cast(edeArrivalDate AS int)) + (cast(LEFT (edeDoctorTimeSeen ,
2) AS decimal) / 24) + (cast(substring(edeDoctorTimeSeen , 4 , 2) AS decimal)
/ 24 / 60)) - ((cast(edeArrivalDate AS int)) + (cast(LEFT (edeArrivalTime ,
2) AS decimal) / 24) + (cast(substring(edeArrivalTime , 4 , 2) AS decimal) /
24 / 60))) * 24 * 60) END

Thanks
 
Hi

I am trying to do edit an existing query to find the difference between 2
dates and times (equalling a duration). For example:

1. Arrival Date    2. Arrival Time  3. DoctorTimeSeen Date 4. DoctorTimeSeen
Time
01/07/2008           08:44:00             01/07/2008                    
08:44:00

This is the case statement below that's already been written for me and it
works when the 2 dates/times are different however when they are the same
like the example above it returns 1440 minutes (24 hours) instead of 0
minutes.

Can anyone help. I'm totally stumped.

CASE WHEN (((((cast(edeArrivalDate AS int)) + (cast(LEFT (edeDoctorTimeSeen
, 2) AS decimal) / 24) + (cast(substring(edeDoctorTimeSeen , 4 , 2) AS
decimal) / 24 / 60)) - ((cast(edeArrivalDate AS int)) + (cast(LEFT
(edeArrivalTime , 2) AS decimal) / 24) + (cast(substring(edeArrivalTime ,4 ,
2) AS decimal) / 24 / 60))) * 24 * 60)) < 1 THEN (((((cast(edeArrivalDateAS
int) + 1)) + (cast(LEFT (edeDoctorTimeSeen , 2) AS decimal) / 24) +
(cast(substring(edeDoctorTimeSeen , 4 , 2) AS decimal) / 24 / 60)) -
((cast(edeArrivalDate AS int)) + (cast(LEFT (edeArrivalTime , 2) AS decimal)
/ 24) + (cast(substring(edeArrivalTime , 4 , 2) AS decimal) / 24 / 60))) * 24
* 60) ELSE ((((cast(edeArrivalDate AS int)) + (cast(LEFT (edeDoctorTimeSeen ,
2) AS decimal) / 24) + (cast(substring(edeDoctorTimeSeen , 4 , 2) AS decimal)
/ 24 / 60)) - ((cast(edeArrivalDate AS int)) + (cast(LEFT (edeArrivalTime,
2) AS decimal) / 24) + (cast(substring(edeArrivalTime , 4 , 2) AS decimal) /
24 / 60))) * 24 * 60) END

Thanks
Click to expand...

Wow, that's a LOT of work to do something that should be simple. For
starters, store both the date and the time in *same field*. Then all
you need is DateDiff().

Difference = DateDiff("n",[TimeIn],[TimeOut])
 
Hi

I am trying to do edit an existing query to find the difference between 2
dates and times (equalling a duration). For example:

1. Arrival Date 2. Arrival Time 3. DoctorTimeSeen Date 4. DoctorTimeSeen
Time
01/07/2008 08:44:00 01/07/2008
08:44:00

This is the case statement below that's already been written for me and it
works when the 2 dates/times are different however when they are the same
like the example above it returns 1440 minutes (24 hours) instead of 0
minutes.

Can anyone help. I'm totally stumped.


CASE WHEN (((((cast(edeArrivalDate AS int)) + (cast(LEFT (edeDoctorTimeSeen
, 2) AS decimal) / 24) + (cast(substring(edeDoctorTimeSeen , 4 , 2) AS
decimal) / 24 / 60)) - ((cast(edeArrivalDate AS int)) + (cast(LEFT
(edeArrivalTime , 2) AS decimal) / 24) + (cast(substring(edeArrivalTime , 4 ,
2) AS decimal) / 24 / 60))) * 24 * 60)) < 1 THEN (((((cast(edeArrivalDate AS
int) + 1)) + (cast(LEFT (edeDoctorTimeSeen , 2) AS decimal) / 24) +
(cast(substring(edeDoctorTimeSeen , 4 , 2) AS decimal) / 24 / 60)) -
((cast(edeArrivalDate AS int)) + (cast(LEFT (edeArrivalTime , 2) AS decimal)
/ 24) + (cast(substring(edeArrivalTime , 4 , 2) AS decimal) / 24 / 60))) * 24
* 60) ELSE ((((cast(edeArrivalDate AS int)) + (cast(LEFT (edeDoctorTimeSeen ,
2) AS decimal) / 24) + (cast(substring(edeDoctorTimeSeen , 4 , 2) AS decimal)
/ 24 / 60)) - ((cast(edeArrivalDate AS int)) + (cast(LEFT (edeArrivalTime ,
2) AS decimal) / 24) + (cast(substring(edeArrivalTime , 4 , 2) AS decimal) /
24 / 60))) * 24 * 60) END

Thanks
Click to expand...

Is this in an Access database, or more likely a SQL/Server database? This is
T/SQL syntax and would certainly not work in an Access JET/ACE database.
 
Back
Top