Help to write a query to calculate # of hours betw

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 2005 Forums

Transact-SQL (2005)

Help to write a query to calculate # of hours betw

Author

Topic

prakashms
Starting Member

2 Posts

Posted - 2011-02-21 : 09:53:03

From the below table, I need to calculate the number of hours took from creation to finalize and show the record with refrence to 'ReferenceID'

ie. for the reference id 1, I need to sum (UpdatedWhen with reference to StatusTypeID 4 - CreatedWhen with reference to StatusTypeID 2)

ReferenceID,|CreatedWhen,|UpdatedWhen,|StatusTypeID,|Status
--------------------------------------- ----------------------- ----------------------- ----------
1,|2007-08-23 17:17:40.390,|2007-08-23 17:53:36.857,|2,|Created
1,|2007-08-23 17:53:36.857,|2007-08-23 17:53:36.857,|4,|Finalized
3,|2007-08-24 11:21:47.310,|2007-08-24 11:23:41.733,|2,|Created
3,|2007-08-24 11:23:41.733,|2007-08-24 11:23:41.733,|4,|Finalized

Expected Result
ReferenceID,|#of hours took from creation to finalize
------------------
1,|2 (in hours, approx)
3,|4 (in hours, approx)

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-02-21 : 10:06:58

[code]
select ReferenceID, datediff(hour, min(CreatedWhen), max(UpdatedWhen))
from yourtable
where Status in ('Created', 'Finalized')
group by ReerenceID
[/code]

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources