June 5, 2008 at 5:13 am
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]
June 5, 2008 at 10:25 am
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))
😎
June 5, 2008 at 11:05 am
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'
June 6, 2008 at 1:30 am
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.
June 6, 2008 at 1:37 am
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?
June 6, 2008 at 2:29 am
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
June 6, 2008 at 2:40 am
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"
June 6, 2008 at 2:46 am
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 mathselect (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!
June 6, 2008 at 6:40 am
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?
😎
June 6, 2008 at 7:41 am
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)
June 6, 2008 at 7:54 am
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)?
😎
June 6, 2008 at 8:38 am
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)
June 6, 2008 at 10:37 am
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.
June 6, 2008 at 11:06 am
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.
😎
June 6, 2008 at 4:02 pm
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
Viewing 15 posts - 1 through 15 (of 23 total)
You must be logged in to reply to this topic. Login to reply