date difference between two dates in two rows based on the status of the column

Question

I have table with columns and data as well

Id    CardNo    IODate                     IOStatus
====  ========  =========================  ========
1     0004      2011-02-17 10:48:39.000    Entry    
2     0004      2011-02-17 10:55:39.000    Exit    
3     0004      2011-02-17 11:48:39.000    Entry
4     0004      2011-02-17 15:55:39.000    Exit

I want to get the hours and minutes spent between entry and exit. If the entry is in one date and exit is in the next day it should able to calculate the hours. All this has to be done in sql server.

it should able to sum up the hours for that particular day. – ramakrishna Nov 12 '15 at 18:37 — ramakrishna, Nov 12 '15 at 18:37
This question does not show any research effort. – Tab Alleman Nov 12 '15 at 18:40 — Tab Alleman, Nov 12 '15 at 18:40

Kent Lee · Answer 1 · 2015-11-12T19:03:37.493

0

You want to do that in SQL? Try this: Suppose the name is of the table is Table:

Select DATEDIFF(hour,t1.IODate,t2.IODate) from Table t1 left join t2 on t1.ID=t2.ID-1

edited Nov 12 '15 at 19:03

answered Nov 12 '15 at 18:44

Kent Lee

107
4
13

Yes need it in sql. But this is not getting me the correct results – ramakrishna Nov 12 '15 at 18:49
@ramakrishna I updated the answer, does that help? – Kent Lee Nov 12 '15 at 19:04
what result do you want – Kent Lee Nov 12 '15 at 19:29

date difference between two dates in two rows based on the status of the column

1 Answers1