Help to write a query to calculate # of hours between two records.

• lookpete

  Grasshopper

  Points: 10

  More actions

  February 21, 2011 at 7:40 am

  #231563

  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)

  Thanks in advance.

  PETE

• GSquared

  SSC Guru

  Points: 260824

  More actions

  February 21, 2011 at 8:40 am

  #1289455

  Are you familiar with the DateDiff function? It will calculate the number of hours between dates, with a few caveats.

  Take a look at it in Books Online or on MSDN.com. See if it'll do what you need.

  - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
  Property of The Thread

  "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon