INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
• Talk With Other Members
• Be Notified Of Responses
• Keyword Search
Favorite Forums
• Automated Signatures
• Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Calculating Date Diff for timestamps in same field

 Forum Search FAQs Links MVPs

Calculating Date Diff for timestamps in same field

(OP)
Hi Everyone,

How do I calculate the time difference between two date/time timestamps that are in the same field, but use different codes. The fields are linked by the same event number.

For example: EMS arrived at the hospital (incilog.transcode= "H") at 02/28/2017 15:00 (incilog.timestamp (DateTime)) and cleared the hospital (incilog.transcode= "C") at 02/28/2017 15:25 (incilog.timestamp (DateTime)). EMS were on scene at the hospital for 25 minutes.

I can't figure out how to find the date difference between the two dates because they utilize the same table.field with different codes.

Any help is greatly appreciated.

Thanks,
Kim

RE: Calculating Date Diff for timestamps in same field

If sorting by event number then by date and doing a datediff using previous or next will not work, then I would try adding the table a second time and joining the table on the event number field.
It would still require some formula logic to be written but seems quite possible.

RE: Calculating Date Diff for timestamps in same field

Group by patient and add two formula to capture value for transcode H and C

eg
@arrival
If Trancode = H then timestamp else datetime (1900, 1, 1, 0, 0, 0)

Repeat for Clearing

in Patient footer

ad date diff formula based on max values of two formula

Eg
@Intime

datediff("n", Max(@arrival, patientgroupfield), Max(@Clear, patientgroupfield)

This should return difference in minutes

Ian

RE: Calculating Date Diff for timestamps in same field

(OP)
Thank you Ian,
Your solution worked perfectly. I appreciate your help.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

• Talk To Other Members
• Notification Of Responses To Questions
• Favorite Forums One Click Access
• Keyword Search Of All Posts, And More...

Register now while it's still free!