Red Devil Red Devil - 5 months ago 8
SQL Question

Checkintime not coming correctly because of NULL

I have a table called times and i am using mssql database

id | checkintime | checkouttime |
-------------------------------------------------------------
1 | 2016-06-30 07:00:00.000 | NULL |
1 | NULL | 2016-06-30 18:00:00.000 |
1 | 2016-07-01 07:00:00.000 | NULL |
1 | NULL | 2016-07-01 18:00:00.000 |
2 | NULL | 2016-07-01 18:00:00.000 |


I am expecting the output like

id | checkintime | checkouttime |
-----------------------------------------------------
2 | NULL | 2016-07-01 18:00:00.000 |


When I am running this query:

select * from times
where checkintime is null
and CheckOutTime
between convert(varchar(10),getdate()-1,120)
and convert(varchar(10),getdate(),120)


I am getting the output like:

id | checkintime | checkouttime |
----------------------------------------------------
1 | NULL | 2016-07-01 18:00:00.000 |
2 | NULL | 2016-07-01 18:00:00.000 |


Than i modified my query to something like this

select * from times
where checkintime is null
and CheckOutTime
between convert(varchar(10),getdate()-1,120)
and convert(varchar(10),getdate(),120)
and not exists
(select * from times
where 1=1
and ( checkintime
between convert(varchar(10),getdate()-1,120)
and convert(varchar(10),getdate(),120)
or CheckInTime is null
)
)


But it is giving me blank, I am not sure what is wrong in this

Answer

If I understand correctly, you want checkouttime times that don't have a matching checkintime. You can calculate a cumulative count of each and then take rows where the cumulative for checkouttime is greater than the cumulative for checkintime.

select t.*
from (select t.*,
             count(checkintime) over (partition by id order by coalesce(checkintime, checkouttime)) as cnt_in,
             count(checkouttime) over (partition by id order by coalesce(checkouttime, checkintime)) as cnt_out
      from times t
     ) t
where checkouttime is not null and cnt_out > cnt_in;

EDIT:

SQL Server 2008 doesn't support cumulative counts, but you can do:

      select t.*
      from times t outer apply
           (select count(t2.checkintime) as cnt
            from times t2
            where t2.id = t.id and
                  coalesce(t2.checkintime, t2.checkouttime) <= coalesce(t.checkintime, t.checkouttime)
           ) ins outer apply
           (select count(t2.checkouttime) as cnt
            from times t2
            where t2.id = t.id and
                  coalesce(t2.checkouttime, t2.checkintime) <= coalesce(t.checkouttime, t.checkintime)
           ) outs
where t.checkouttime is not null and outs.cnt > ins.cnt;