How to calculate timespan based in login in status

• sqllearner-339367

  Hall of Fame

  Points: 3004

  More actions

  September 6, 2012 at 3:19 am

  #267273

  I have a temporary table whose structure is a below.

  create table #temptime

  (

  logtime datetime,

  [status] varchar(3)

  )

  As you can see from above , I have logintime and status. I need to partition this table based on status , ordered by date. I will need to calculate time span between these two created partition and consolidate to find how long user is in the premise.

  For example let's say we have 4 records

  Partition based on status IN Partition based in status OUT

  8:45 AM 10:45 AM

  11:00 AM 15:00 PM

  Now program should find time span

  10:45 AM - 8:45 AM = 2 Hours + 11:00 Am - 15:00 PM = 4 Hours ; total = 6 hours.

  Whats the best way to accomplish this task?

  Thanks

• sqllearner-339367

  Hall of Fame

  Points: 3004

  More actions

  September 6, 2012 at 3:46 am

  #1534343

  All,

  Found out (thanks to Joe) it can be solved using CTE expression for both IN and OUT status and performing date diff.

  Vidhya