Avatar of dastaub
Flag for United States of America asked on

SQL Time In and Time Left - How Many Are Here?

I have rows with a Time Stamp of when someone arrives and a TimeStamp of when the same person leaves.  Each person is identified when they arrive and leave, it is not a simple count of INs and OUTs  Each person's TimeIn and TimeOut are recorded in the same row.
I need an idea for a query that will tell me the count of people who are IN at any point in time?
I can tell you how many people arrived between time A and B or how many left between time A and B, but I'm not clear on calculating how many are present at time A or present at time B?
Microsoft SQL Server 2005Microsoft SQL Server 2008SSRS

Avatar of undefined
Last Comment

8/22/2022 - Mon

select ... where TimeIn >= pointInTime and TimeOut <= pointInTime

SELECT * FROM tblTime WHERE TimeIn >=PointinTime AND TimeOut<PointinTime

That should show you who's clocked in at that time.  The only thing this doesn't account for is if someone has the exact same time in as their time out.  However, that should never be allowed anyway.....

lol, sorry I didn't see that ramrom had already posted, I was afk for a few minutes....  I opted for only TimeOut<PointinTime instead of <= because that shouldn't be allowed in my mind but I think it depends on your DB and were you get the figures from.
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy

I use the below and it gives me the list of who is IN

WHERE (TimeIn >= @mTwoDaysAgo AND TimeIn < @mAsOf) AND
(TimeLeft >= @mAsOf OR TimeLeft = '')

The person comes in prior to a specific time and has not left until after that time or the timeleft is blank.  I use @mTwoDaysAgo to eliminate errors of a person who incorrectly shows up as being IN for a week, etc.

What I am looking for is a query that would supply
08:00   32
09:00   28
10:00   31

My current approach would require a query for each time of day requested.

Can you put the statement into a loop and just increase by an hour each time?

I was told loops inside an SQL stored procedure are a last resort.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question

<<   OR TimeLeft = '') >>
You tested that this workes.

See what you get with it  replaced by
OR TimeLeft IS NULL )
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.

Can you give an explantion of what is going on inside your query?  I'm trying to understand how it works because it does appear to work and does give me the answer I need.  I'm now trying to under "how" it works.

Creditds for ewangoya

Technique is a common table expression (CTE) where you build a result set (line 7 -14) to use it in a following select (or insert/update/delete) statement (line 16 - 19). Here power is used to refer to itself during definition.
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck

Below is the query that worked.  zjunk is the table containing Time data.

declare @startDate datetime -- start date
declare @endDate datetime

select @startDate = MIN(TimeIn2), @endDate = MAX(TimeLeft2)
from zjunk

;WITH tblDates([DATE]) AS
SELECT @startDate AS [DATE]
  FROM tblDates
     WHERE [DATE] < @endDate

select B.[DATE], COUNT(*)
from zjunk A
inner join tblDates B on B.[DATE] > DATEADD(hour, DATEDIFF(hour, 0, A.TimeIn2), 0) and B.[DATE] <= COALESCE(A.TimeLeft2, GETDATE())
group by B.[DATE]

It works, but I do not understand how it works.