asp.net - Find difference between rows of same column (difference between time data) -

i want find difference between time data in same column group of same employee. have written query below:

with    rows         (         select  isnull(left(hhmm,2)+ ':'+ right(left(hhmm,4),2),'''') login,          row_number() on (order cardno) rn            attn01072013_copy13_7_13          ) select  *--mc.login-mp.login diff    rows mc join    rows mp on      mc.rn = mp.rn - 1  

this query return data this:

cardno  login   rn  cardno  login   rn e44920  09:18   1   e44920  09:46   2 e44920  09:46   2   e44920  17:09   3 e44920  17:09   3   e44920  16:57   4 e44920  16:57   4   e44920  17:34   5 e44920  17:34   5   e44920  17:53   6 e44920  17:53   6   e44920  17:56   7 e44920  17:56   7   e44920  17:57   8 e44920  17:57   8   e44920  18:00   9 

now want find difference between 1st , 2nd login time.. 3rd , 4th login time. how can this, kindly suggest solution asap, thanks.

solution:

declare @event table(     eventid int identity(1,1) not null primary key,     cardno varchar(10) not null,     [login] datetime not null     -- prevent duplicate events     -- constraint create index used optimize rownum , last queries     unique(cardno,[login])  );  insert @event(cardno,[login])             select 'e44920', '2013-07-15t09:18:00' union   select 'e44920', '2013-07-15t09:46:00' union   select 'e44920', '2013-07-15t17:09:00' union   select 'e44920', '2013-07-15t16:57:00' union   select 'e44920', '2013-07-15t17:34:00' union   select 'e44920', '2013-07-15t17:53:00';  declare @eventwithrownum table(     rownum int not null,     cardno varchar(10) not null,     primary key (cardno,rownum),     [login] datetime not null     unique(cardno,[login]) ); insert @eventwithrownum (cardno,[login],rownum) select  e.cardno, e.[login], row_number() over(partition e.cardno order e.[login]) rownum    @event e;  -- final query select  crt.rownum,         crt.cardno,          crt.[login] currentlogin,         nxt.rownum,         nxt.[login] nextlogin,         datediff(second, crt.login, nxt.login) diff_seconds    @eventwithrownum crt -- crt = odd rows left join @eventwithrownum nxt on crt.cardno=nxt.cardno , crt.rownum=nxt.rownum-1 -- nxt = rows   crt.rownum % 2 = 1 -- odd rows; add computed column modulo2 (rownum % 2) persisted , define index (key: modulo2, cardno, login) order crt.cardno, crt.[login]; 

results:

rownum      cardno     current_login           rownum      next_login              diff_seconds ----------- ---------- ----------------------- ----------- ----------------------- ------------ 1           e44920     2013-07-15 09:18:00.000 2           2013-07-15 09:46:00.000 1680 3           e44920     2013-07-15 16:57:00.000 4           2013-07-15 17:09:00.000 720 5           e44920     2013-07-15 17:34:00.000 6           2013-07-15 17:53:00.000 1140