CODEWITHSUNDEEP
sqlsql-serverstored-procedures
Frank
Frank

Reputation: 13

To find the date when a threshold is reached in SQL

I would like to write a query in SQL that finds out what is the date when the X number of people have checked in. Records are tracking the person's name, Date checked in, and Date checked out (Null if not checked out).

For the following dataset, how do I know that I have reached the 3 patient-limit on 2/8?

    Name,      Date In,     Date Out
    John,     1/25/2014,     NULL
    Tom,      2/8/2014,    2/9/2014
    Joe,      1/21/2014,   1/22/2014
    Dave,     2/7/2014,      NULL

I have this query so far, but I don't know how to reduce the count number when the user has checked out

select count(c1.Name) as count, c2.DateIn
from customer c1 join customer c2 
on c1.DateIn <= c2.DateIn 
group by c2.DateIn
having count(c1.Name) >= 3

Thanks!

Upvotes: 0

Views: 1297

Answers (2)

PrfctByDsgn
PrfctByDsgn

Reputation: 1050

how about this:

select t1.*, 
     (select count(t2.name) from customer t2 
         where t2.date_in <= t1.date_in and (t2.date_out is null or t2.date_out >= t1.date_in))
     from customer t1
     order by t1.date_in

the new column shows how many users are logged on for every row in your customer table.

Upvotes: 2

NP3
NP3

Reputation: 662

If I understand the question correctly:

select date_in
from customer
where date_out is null
group by date_in
having count(*) >= 3

Upvotes: 0

Related Questions