Search code examples
sqlrdbms

Find out number of people who are at the office at 6 p.m

I have a simple query to which I forgot the solution, I remember there was a function to look for each possible value in a range, so I am asking this query here.

There is a table 'Time' with 2 columns checkInTime and CheckOutTime of the employees for one day in the office.

in the format: hour*100+minutes

For instance:

Check in Check out
850 1758
902 1640
1330 1530
1630 2020

Find out the number of people which were in office at 6 pm (1800)

To this I came with this query (obviously its wrong since I dont know how to look for the value 1800 in the checkin-checkout range):

select sum(rn) from Table row_number() over() as rn Where Checkout >= 1800

Solution 
  •     SELECT COUNT(*)
    FROM table 
    WHERE 1800 BETWEEN CheckIn AND CheckOut)

    That said this is incomplete without a full date to qualify that they were not in longer than a day.