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 |
ptah
Starting Member
3 Posts |
Posted - 2007-01-18 : 08:05:17
|
hello all,i am trying to create a view from a table that will keep track of the time between each stage of tasks given. take a look at the data below:progressID taskID stage status theDate------------------------------------------------------------------------1407525 1091657 In Progress Logged 2006-11-16 10:00:24.0001407526 1091657 In Progress Inprogress 2006-11-16 12:08:59.0361407214 1091657 In Progress Resolved 2006-11-16 14:15:48.0001407220 1091657 Closed Solved 2006-11-16 14:36:05.000i would like to be able to have just one row per task ID showing the difference between the stages, as shown below . only the 2nd column is a date, the rest are are hours (datediff) between the stage and its preceeding stage :taskID Logged InProgress Resolved Solved 1091657 2006-11-16 10:00:24.000 2.08 2.07 0.21is it possible to achieve such a transformation using views and a number of select statements (i.e no dts)? all assistance will be highly appreciated.regards,ptah |
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-01-18 : 08:23:44
|
[code]SELECT TaskID, Logged, CAST(DATEDIFF(minute, Logged, InProgress) / 60.0 AS NUMERIC(9, 2)) AS InProgress, CAST(DATEDIFF(minute, InProgress, Resolved) / 60.0 AS NUMERIC(9, 2)) AS Resolved, CAST(DATEDIFF(minute, Resolved, Solved) / 60.0 AS NUMERIC(9, 2)) AS SolvedFROM ( SELECT TaskID, MAX(CASE WHEN Status = 'Logged' THEN theDate END) AS Logged, MAX(CASE WHEN Status = 'InProgress' THEN theDate END) AS InProgress, MAX(CASE WHEN Status = 'Resolved' THEN theDate END) AS Resolved, MAX(CASE WHEN Status = 'Solved' THEN theDate END) AS Solved FROM YourTableNameHere GROUP BY TaskID ) AS dORDER BY TaskID[/code]Peter LarssonHelsingborg, Sweden |
|
|
ptah
Starting Member
3 Posts |
Posted - 2007-01-18 : 10:59:14
|
thanks for the help. much appreciated.ptah |
|
|
|
|
|
|
|