CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Rajashri
Rajashri

Reputation: 59

How to calculate the duration between two dates in same column in SQL

I want to calculate the Break Duration of user(Break Duration=Unlock-Lock Time).

Can anyone please tell me how to calculate that.

below is my table

Event id  EventDate                       EventType            Userid
1     2015-11-05 13:54:28.900       Login                10
2     2015-11-05 13:55:27.527       Lock                 10
3     2015-11-05 13:55:27.537       Break                10
4     2015-11-05 13:55:37.037       Unlock               10
5     2015-11-05 13:56:13.953       Break                10
7     2015-11-05 14:33:26.347       Login                10
8     2015-11-05 14:33:46.243       TaskStartedInManual  10
9     2015-11-05 14:34:34.570       TaskPause            10

My break formula is not always same. In the below example my Break Event is in between Login-Taskpause.So basically whenever there is a break in EventType calculate Previous row of Date-Next row of date.How can i achieve that?

EventId  EventDate                EventType   Userid
1   2015-11-05 13:54:28.900   Login        10
2   2015-11-05 13:55:27.527   Lock         10
3   2015-11-05 13:55:27.537   Break        10
4   2015-11-05 13:55:37.037   Unlock       10
5   2015-11-05 14:33:26.347   Login        10
6   2015-11-05 14:33:46.243   Break        10
7   2015-11-05 14:34:34.570   TaskPause    10

Desired Output will be

Userid  Break_Duration(In sec)
10         10
10         68

Upvotes: 0

Views: 795

Answers (2)

cjb110
cjb110

Reputation: 1491

You will need to use the DATEDIFF function to do this:

DATEDIFF('d', [COLUMN1], [COLUMN2]) where d is the type of difference you want (days in this example)

See the MSDN DATEDIFF page for full details.

Upvotes: 0

Juan Carlos Oropeza
Juan Carlos Oropeza

Reputation: 48207

Asign an ID to each event type and then match by that ID to calculate DATEDIFF

Sql Fiddle Demo

with cte as (
      SELECT s.*,
             row_number() over (partition by [EventType] order by [EventDate]) as rn
      FROM shift s
)
SELECT L.[Userid], DATEDIFF ( second, L.[EventDate], U.[EventDate] ) as breaktime_seconds
FROM cte U  -- unlock
JOIN cte L  -- lock
  ON U.rn = L.rn
 AND U.[Userid] = L.[Userid]
WHERE U.[EventType] = 'Unlock'
  AND L.[EventType] = 'Lock'
ORDER BY L.[Userid]

OUTPUT

| Userid | breaktime_seconds |
|--------|-------------------|
|     10 |                10 |

Upvotes: 2

Related Questions