How to avoid same out time in this query?

Question

My table:Trnevents

emp_reader_id   EVENTID     DT
102                0    2018-01-04 15:57:04.000
102                0    2018-01-04 15:58:05.000
102                1    2018-01-04 16:46:19.000
102                0    2018-01-04 18:15:27.000
102                1    2018-01-04 18:20:47.000
102                0    2018-01-04 20:02:05.000
102                0    2018-01-04 21:47:29.000
102                1    2018-01-04 22:00:00.000

I used this query it works good but it get same out time

select
       emp_Reader_id, cast(DT as date) [date]
     ,  DT  as       check_in_1
     ,  next_timestamp as check_out_1


from (
      select
            emp_Reader_id, DT, EVENTID, next_timestamp, next_EVENTID
          , dense_rank() over(partition by emp_Reader_id, cast(DT as date) order by DT) in_rank
      from trnevents t1
      outer apply (
          select top(1) t2.DT, t2.EVENTID
          from trnevents t2
          where t1.emp_Reader_id = t2.emp_Reader_id and t1.EVENTID <> t2.EVENTID
          and cast(t1.DT as date) = cast(t2.DT as date)
          and t1.DT < t2.DT
          order by t2.DT
          ) oa (next_timestamp, next_EVENTID)
      where EVENTID = '0'
     ) d
group by emp_Reader_id, cast(DT as date),DT,next_timestamp
order by emp_reader_id

Result:

emp_Reader_id   date    check_in_1  check_out_1
     102    2018-01-04  2018-01-04 15:57:04.000 2018-01-04 16:46:19.000
     102    2018-01-04  2018-01-04 15:58:05.000 2018-01-04 16:46:19.000
     102    2018-01-04  2018-01-04 18:15:27.000 2018-01-04 18:20:47.000
     102    2018-01-04  2018-01-04 20:02:05.000 2018-01-04 22:00:00.000
     102    2018-01-04  2018-01-04 21:47:29.000 2018-01-04 22:00:00.000

Expected Output:

emp_Reader_id   date    check_in_1  check_out_1
         102    2018-01-04  2018-01-04 15:57:04.000      ----
         102    2018-01-04  2018-01-04 15:58:05.000 2018-01-04 16:46:19.000
         102    2018-01-04  2018-01-04 18:15:27.000 2018-01-04 18:20:47.000
         102    2018-01-04  2018-01-04 20:02:05.000      ----
         102    2018-01-04  2018-01-04 21:47:29.000 2018-01-04 22:00:00.000

Is it possible to get above expected output.anyone could help. Thanks in advance

Use `ROW_NUMBER` with a partition on the `check_out_1` field. I would answer except that your query is ugly (and scary because of cross apply). — Tim Biegeleisen, Feb 07 '18 at 08:11

uzi · Accepted Answer · 2018-02-07T08:25:42.657

5

This query is for SQL 2012 or above

Sample data

create table Trnevents (
    emp_reader_id int
    , EVENTID int
    , DT datetime
)

insert into Trnevents
select
        a, b, cast(c as datetime)
    from
        (values 
            (102, 0, '20180104 15:57:04')
            ,(102, 0, '20180104 15:58:05')
            ,(102, 1, '20180104 16:46:19')
            ,(102, 0, '20180104 18:15:27')
            ,(102, 1, '20180104 18:20:47')
            ,(102, 0, '20180104 20:02:05')
            ,(102, 0, '20180104 21:47:29')
            ,(102, 1, '20180104 22:00:00')
        ) t (a, b, c)

Query:

select
    emp_reader_id, cast(max(DT) as date), max(iif(EVENTID = 0, DT, null)), max(iif(EVENTID = 1, DT, null))
from (
    select
        *, grp = sum(iif(EVENTID = 0, 1, 0) ) over (partition by emp_reader_id order by DT)
    from
        Trnevents
) t
group by emp_reader_id, grp

edited Feb 07 '18 at 08:25

answered Feb 07 '18 at 08:14

uzi

4,118
1
15
22

it goods but where can i get trnevents table in this query without giving values directly – Time Attendance System Feb 07 '18 at 08:20
is it possible to get total work time between in and out time? – Time Attendance System Feb 07 '18 at 08:40
In what format do you need to show? You can calculate minute difference with `datediff` function and then use `sum() over (partition by emp_reader_id)` to get total sum – uzi Feb 07 '18 at 08:52

Salman A · Answer 2 · 2018-02-07T08:40:15.850

You can use LEAD function to probe next row:

WITH cte AS (
    SELECT *, CASE WHEN eventid = 0 THEN
        LEAD(CASE WHEN eventid = 1 THEN dt ELSE NULL END, 1)
        OVER (PARTITION BY emp_reader_id ORDER BY dt)
    END AS checkout_time
    FROM testdata
)
SELECT *
FROM cte
WHERE eventid = 0

Result:

| emp_reader_id | eventid | dt                  | checkout_time       |
|---------------|---------|---------------------|---------------------|
| 102           | 0       | 2018-01-04 15:57:04 | NULL                |
| 102           | 0       | 2018-01-04 15:58:05 | 2018-01-04 16:46:19 |
| 102           | 0       | 2018-01-04 18:15:27 | 2018-01-04 18:20:47 |
| 102           | 0       | 2018-01-04 20:02:05 | NULL                |
| 102           | 0       | 2018-01-04 21:47:29 | 2018-01-04 22:00:00 |

score 3 · Answer 3 · answered Feb 07 '18 at 08:29

3

Try this:

select * from (
    select *,
           case when Lead(eventid) over (order by dt) = 1
                then Lead(dt) over (order by dt) end [CloseTime]
    from Trnevents
) a where EVENTID = 0

NOTE: it requeries SQL Server 2012 or newer.

answered Feb 07 '18 at 08:29

Michał Turczyn

32,028
14
47
69

How to avoid same out time in this query?

3 Answers3