Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
mlfdoc
Starting Member
1 Post |
 Posted - 2004-04-22 : 13:57:28
|
| As a relative newcomer, I'm trying to figure out the best way to approach the following problem:I have a table (multiple ones, actually, but let's keep it simple) where there are various times for a single record. For example, there is an arrival time, a departure time, and between those two there are various event times. I'm trying to develop a flexible query where an interval can be calculated by specifying the time event on each end (e.g. arrival to departure, arrival to decision, decision to departure, etc.). I'd then like to be able to perform some basic stats on this interval, such as average, number of people whose interval is below a certain number, etc. These are all fairly straightforward tasks and can be easily hard-coded. However, there are enough event times that hard-coding a query for each possible combination would be silly. If I could simply set the begin and end time for the variables once, it'd save me lots of work. Thus, I'd like to do something like the following:declare @time1 datetimedeclare @time2 datetimedeclare @avgint intselect @time1 = Arrival Time from PeopleLogselect @time2 = DepartureTime from PeopleLogselect @avgint = avg(datediff(n,@time1,@time2)) from PeopleLogselect @avgint as 'Average Interval Length in Mins'Obviously, the above doesn't work, as the select statement will only set @time1 and @time2 as the value of ArrivalTime and DepartureTime from the last row. If I simply remove the variables and replace @time1 and @time2 with ArrivalTime and DepartureTime, the query works just fine by calculating the interval for each row and then returning the average of them. I would be happy to just hard-code things, but as I noted above, this would require many, many queries. Additionally, there are many other sections of the query that would use the @time1 and @time2 variable that I omitted for the sake of clarity.So in essence, I need to figure out a way to be able to specify a variable once whose value can vary for each record within a table.Any suggestions on how I should approach this problem? |
|
|
drymchaser
Aged Yak Warrior
552 Posts |
Posted - 2004-04-22 : 14:14:07
|
| Methinks a User-Defined Function (UDF) can work here.Feed it the columns for Arrival and Departure and have it return the interval. |
 |
|
|
|
|
|
|
|
Using variables within statements