Struggling with time!

Question

Struggling with time!

tim.pinder

Old Hand

Points: 322
More actions
June 5, 2008 at 5:13 am

#186519

Hi Everyone
I'm a real newbie to SQL and I guess this is probably quite straightforward:
I have some data relating to patient arrival and treatment times at an ER. The [arrival date] and [arrival time] are two separate fields (WHY?), both are datetime.
There are also some fields for internal timing:
[INITIAL_ASSESSMENT_TIME] (char(4))
[TIME_SEEN_FOR_TREATMENT] (char(4))
[ATTENDANCE_CONCLUSION_TIME] (char(4))
[DEPARTURE_TIME] (char(4))
I would like to calculate the interval times at various stages i.e.:
[waiting time for assessment] = [INITIAL_ASSESSMENT_TIME] – [arrival time]
[waiting time for treatment] = [TIME_SEEN_FOR_TREATMENT] - [INITIAL_ASSESSMENT_TIME]
[treatment time] = [ATTENDANCE_CONCLUSION_TIME] - [TIME_SEEN_FOR_TREATMENT]
[post-treatment stay] = [DEPARTURE_TIME] - [ATTENDANCE_CONCLUSION_TIME]
Two problems immediately present themselves
1Converting the internal timing fields into datetime
2Dealing with the transition through midnight
I think I can handle the modulo arithmetic to get intervals into hours and minutes 😉
Do I have to declare the interval variables? What else do I need to be aware of?
Thanks for any help you can offer
My code segment looks like this:
SELECT[ARRIVAL_DATE] AS ArriveDate
,[ARRIVAL_TIME] AS ArriveTime
,LEFT([INITIAL_ASSESSMENT_TIME],2) + ':' + RIGHT([INITIAL_ASSESSMENT_TIME],2) AS AssessTime
,DATEDIFF (mi, [ARRIVAL_TIME], AssessTime) AS AssessWait
,LEFT([TIME_SEEN_FOR_TREATMENT],2) + ':' + RIGHT([TIME_SEEN_FOR_TREATMENT],2) AS StartTreatTime
,DATEDIFF (mi, AssessTime, StartTreatTime) AS TreatWait
,LEFT([ATTENDANCE_CONCLUSION_TIME],2) + ':' + RIGHT([ATTENDANCE_CONCLUSION_TIME],2) AS EndTreatTime
,DATEDIFF (mi, StartTreatTime, EndTreatTime) AS TreatTime
,[DEPARTURE_TIME]

Viewing 15 posts - 1 through 15 (of 23 total)

You must be logged in to reply to this topic. Login to reply

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

What does the data in the following fields look like:

[INITIAL_ASSESSMENT_TIME] (char(4))
[TIME_SEEN_FOR_TREATMENT] (char(4))
[ATTENDANCE_CONCLUSION_TIME] (char(4))
[DEPARTURE_TIME] (char(4))

😎

Steve Jones - SSC Editor SSC Guru Points: 736063 More actions · Answer 2

You don't need internal variables.

You might need to convert/cast to get to numerics and then do your math

select (cast(arrivetime as int)/60) 'Minutes'

tim.pinder Old Hand Points: 322 More actions · Answer 3

Lynn Pettis (6/5/2008)
What does the data in the following fields look like:
[INITIAL_ASSESSMENT_TIME] (char(4))
[TIME_SEEN_FOR_TREATMENT] (char(4))
[ATTENDANCE_CONCLUSION_TIME] (char(4))
[DEPARTURE_TIME] (char(4))
😎

Hi Lynn

It's just a four digit number.

tim.pinder Old Hand Points: 322 More actions · Answer 4

Steve Jones - Editor (6/5/2008)
You don't need internal variables.
You might need to convert/cast to get to numerics and then do your math
select (cast(arrivetime as int)/60) 'Minutes'

Thanks Steve,

I'll give that a go.

Just one thing - I wrote interval and you wrote internal. Are we both talking about the same variables?

tim.pinder Old Hand Points: 322 More actions · Answer 5

tim.pinder (6/5/2008)
The [arrival date] and [arrival time] are two separate fields (WHY?), both are datetime.

Can I concatenate these to make one field? (How?)

And if I do, how does this affect CASTing the arrival time as minutes?

The data look like this

[ARRIVAL_DATE] [ARRIVAL_TIME]

------------------------- ------------------------

2006-12-31 00:00:00.000 1900-01-01 00:26:00.000

2006-12-31 00:00:00.000 1900-01-01 00:28:00.000

2006-12-31 00:00:00.000 1900-01-01 00:30:00.000

2006-12-31 00:00:00.000 1900-01-01 00:49:00.000

2006-12-31 00:00:00.000 1900-01-01 01:01:00.000

2006-12-31 00:00:00.000 1900-01-01 01:04:00.000

2006-12-31 00:00:00.000 1900-01-01 01:05:00.000

ChiragNS One Orange Chip Points: 26137 More actions · Answer 6

Hi

I think you should concatenate the date and time since there can be cases where the time interval is more than one day.

check out the convert function in BOL.

"Keep Trying"

tim.pinder Old Hand Points: 322 More actions · Answer 7

tim.pinder (6/6/2008)

Steve Jones - Editor (6/5/2008)
You might need to convert/cast to get to numerics and then do your math
select (cast(arrivetime as int)/60) 'Minutes'

Hmmmm.

The Minutes column always contains 0. If I omit the /60 it becomes a flag for AM/PM.

I'll play with that some more!

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 8

tim.pinder (6/6/2008)
Lynn Pettis (6/5/2008)
What does the data in the following fields look like:
[INITIAL_ASSESSMENT_TIME] (char(4))
[TIME_SEEN_FOR_TREATMENT] (char(4))
[ATTENDANCE_CONCLUSION_TIME] (char(4))
[DEPARTURE_TIME] (char(4))
😎
Hi Lynn
It's just a four digit number.

Okay, but 9999 is a four digit number. Is that a valid value in these fields?

😎

tim.pinder Old Hand Points: 322 More actions · Answer 9

Lynn Pettis (6/6/2008)
tim.pinder (6/6/2008)
Lynn Pettis (6/5/2008)
What does the data in the following fields look like:
[INITIAL_ASSESSMENT_TIME] (char(4))
[TIME_SEEN_FOR_TREATMENT] (char(4))
[ATTENDANCE_CONCLUSION_TIME] (char(4))
[DEPARTURE_TIME] (char(4))
😎
Hi Lynn
It's just a four digit number.
Okay, but 9999 is a four digit number. Is that a valid value in these fields?
😎

Fair point.

Taken as two pairs all the values I have seen are 00-23 and 00-59 except where the field is empty.

(I haven't tested - that is supposed to have been done before I get the data)

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 10

You also stated that [arrival date] and [arrival time] are both datetime fields, what does this data look like (sample values would be nice to see)?

😎

tim.pinder Old Hand Points: 322 More actions · Answer 11

Lynn Pettis (6/6/2008)
You also stated that [arrival date] and [arrival time] are both datetime fields, what does this data look like (sample values would be nice to see)?
😎

I posted some this morning :0)

Steve Jones - SSC Editor SSC Guru Points: 736063 More actions · Answer 12

Sorry, I meant internal, meaning you don't need variables that you use in the SELECT. Guess it could apply for interval variables.

You could use a CTE if it makes things easier to read.

Concatenation: you'd have to strip date out of one, time of the other, and then put them together. a PIA. Check to be sure the dates are the same. Someone could mess that up.

If it's 4 digit stuff, perhaps you want to combine things before they get into date format?

I think you're on the right track, just work the math with a row, testing it out, and then check all rows against the source data. Tedious, but I imagine there will be strange exceptions in the source you need to account for. Might start with stripping out all >60s and store them in an error table.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 13

tim.pinder (6/6/2008)
Lynn Pettis (6/6/2008)
You also stated that [arrival date] and [arrival time] are both datetime fields, what does this data look like (sample values would be nice to see)?
😎
I posted some this morning :0)

Snake bit. Noticed it after I had posted the request.

Did a slight test, and you may want to verify. To "concatenate" your Date and Time fields, it may be just as easy as ArrivalDate + ArrivalTime. I took one of the Date/Time pairs, set each to a datetime variable and did a + operation, and it looks good. I think this works because the ArrivalTime column, the datetimes all have 1900-01-01 as the date portion, and this is the "zero" date (cast(0 as datetime) returns 1900-01-01 00:00:00.000).

Haven't had time to do anything else yet unfortunately.

😎

tim.pinder Old Hand Points: 322 More actions · Answer 14

Lynn and Steve

Thanks to both of you. I'll look at this some more on Monday.

Fortunately, being a Mac household, I don't take work home :D:D:D