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

dastaub
dastaub used Ask the Experts™
on
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?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
ramromconsultant

Commented:
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.
11/26 Forrester Webinar: Savings for Enterprise

How can your organization benefit from savings just by replacing your legacy backup solutions with Acronis' #CyberProtection? Join Forrester's Joe Branca and Ryan Davis from Acronis live as they explain how you can too.

Author

Commented:
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?

Author

Commented:
I was told loops inside an SQL stored procedure are a last resort.
I wasn't sure if you were running this inside a VB script or anything.... that was my thoughts.  To be honest I think we need a smarter expert because I'm not sure how to do that without having to run a separate a different statement per time set.

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

See what you get with it  replaced by
OR TimeLeft IS NULL )
Software Engineer
Commented:
try this
declare @startDate DATETIME -- start date
declare @endDate DATETIME

select @startDate = MIN(TimeIn), @endDate = MAX(TimeOut)
from YourTable

;WITH tblDates([DATE]) AS
(
SELECT @startDate AS [DATE]
 UNION ALL
SELECT DATEADD(HOUR,1,[DATE])
  FROM tblDates
     WHERE [DATE] < @endDate
)

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

Open in new window

Author

Commented:
ewangoya,
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.

Commented:
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.
http://msdn.microsoft.com/en-us/library/ms175972.aspx

Author

Commented:
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]
 UNION ALL
SELECT DATEADD(HOUR,1,[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]

Author

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

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial