Calculate average time - SQL Server Forums

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.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Calculate average time

Author

Topic

jung1975
Aged Yak Warrior

503 Posts

Posted - 2005-01-27 : 22:10:53

I have a table A:


P_id        Status                         time
1001       Pending                2003-02-12  08:30
1001       Non-enrolled           2003-06-07 11:00
1001       Enrolled               2003-08-23  15:00
1001       Termed                 2003-11-03  02:00
1002       Pending                2003-01-23  11:00
1002       Non-enrolled           2003-02-24  12:00 
1002       Enrolled               2004-08-25  01:00
1003       Pending                2004-08-11  02:11
1003       Enrolled               2005-01-01  14:34
1004       Pending                2004-10-21  13:08
1004       Non-enrolled           2004-11-11  07:11
1005       pending                2004-01-24  06:30
1005       Termed                 2004-07-11  05:14
1006       Pending                2004-01-23  11:00
1006       Enrolled               2004-06-11   3:33
1006       Non-enrolled           2004-11-22  07:15

I would like to calculate the average time of status change


 Status  change                                     Avg time
Pending to Enrolled (P_id :1002,1003)                *******
Pending to Termed (p_id : 1001,1005)                 *******   
Pending to Non – Enrolled (p_id: 1004,1006)          *******

hgorijal
Constraint Violating Yak Guru

277 Posts

Posted - 2005-01-28 : 01:38:43

[code]
SELECT B.status + ' To ' + c.status STATUS, AVG(datediff(mi, A.min_x_time, A.max_x_time)) Average_time
FROM(SELECT p_id, min(x_time) min_x_time , max(x_time) max_x_time FROM xyz GROUP BY p_id) as A
, xyz B
, xyz C
WHERE B.p_id = A.p_id
AND B.x_time = A.min_x_time
AND C.p_id = A.p_id
AND C.x_time = A.max_x_time
GROUP BY B.status + ' To ' + c.status

[/code]

Hemanth Gorijala
Exchange a Dollar, we still have ONE each._______Exchange an Idea, we both have TWO each.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources