CODEWITHSUNDEEP
sqlsql-serverdatetimetimesql-server-2012
Anyname Donotcare
Anyname Donotcare

Reputation: 11403

How to query the two related transactions in different days

If i have table consists of two columns one for the user id and the other for the finger print(attendance,leave) how to detect all the users whose (leave time ) in the next day of their first finger print?

USERID  CHECKTIME
2982    2015-03-11 09:08:10.000
2982    2015-03-11 20:13:57.000
2982    2015-03-12 08:43:35.000
2982    2015-03-13 04:54:57.000
2982    2015-03-13 05:01:03.000
2982    2015-03-13 19:24:11.000
2982    2015-03-14 10:28:26.000
2982    2015-03-15 04:55:03.000
2982    2015-03-15 05:01:24.000

Example : this user has a check in 2015-03-12 in 08:43:35.000 and its check out in the next day 2015-03-13 in 04:54:57.000

i want to query all the users who has cases like that considering the user may have more than two check-in-out in the same day so i want just the first and (the last in the next day)

Upvotes: 0

Views: 77

Answers (2)

void
void

Reputation: 7890

Assuming all users are checking correctly and there is no missed checktime.

Assume our Schema is:

create table tblchk (USERID int,  CHECKTIME datetime)
insert into tblchk values
(2982    ,'2015-03-11 09:08:10.000'),
(2982    ,'2015-03-11 20:13:57.000'),
(2982    ,'2015-03-12 08:43:35.000'),
(2982    ,'2015-03-13 04:54:57.000'),
(2982    ,'2015-03-13 05:01:03.000'),
(2982    ,'2015-03-13 19:24:11.000'),
(2982    ,'2015-03-14 10:28:26.000'),
(2982    ,'2015-03-15 04:55:03.000'),
(2982    ,'2015-03-15 05:01:24.000');

No we can use a row number to specify which record is for enter time and which is exit time, ODDs are enter and Evens are exit:

select userid,
       checktime,
       case row_number()over(partition by userid order by checktime)%2
            when 1 then 'Enter'
            else 'Exit'
       end [type]
from tblchk

result is:

userid  checktime               type
2982    2015-03-11 09:08:10.000 Enter
2982    2015-03-11 20:13:57.000 Exit
2982    2015-03-12 08:43:35.000 Enter
2982    2015-03-13 04:54:57.000 Exit
2982    2015-03-13 05:01:03.000 Enter
2982    2015-03-13 19:24:11.000 Exit
2982    2015-03-14 10:28:26.000 Enter
2982    2015-03-15 04:55:03.000 Exit
2982    2015-03-15 05:01:24.000 Enter

Now if we want to specify the days with last checktime is enter and the first checktime of the next day is exit we can use above query as:

with cte as
(
    select userid,
           checktime,
           row_number()over(partition by userid order by checktime) rn
    from tblchk
)

select q_enter.userid as [USER],
       q_enter.checktime as EnterTime, 
       q_exit.checktime as ExitTime
from
(
    select userid,
           max(checktime) checktime,
           max(rn) rn
    from cte 
    group by userid,cast(checktime as date)
    having max(rn)%2=1
)q_enter
join 
(
    select userid,
           min(checktime) checktime,
           min(rn) rn
    from cte 
    group by userid,cast(checktime as date)
    having min(rn)%2=0
)q_exit
on q_enter.userid=q_exit.userid and q_enter.rn=q_exit.rn-1

Result:

USER    EnterTime               ExitTime
2982    2015-03-12 08:43:35.000 2015-03-13 04:54:57.000
2982    2015-03-14 10:28:26.000 2015-03-15 04:55:03.000

Upvotes: 1

Swetha reddy
Swetha reddy

Reputation: 71

You can make use of the aggregate functions like min and max which gives first in and last out values

you can check here http://sqlfiddle.com/#!9/68897/2

SELECT USERID,MIN(CHECKTIME),MAX(CHECKTIME) FROM attendence GROUP BY USERID;

Upvotes: 0

Related Questions