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?
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?
ramrom
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.....
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.
ASKER
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.
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?
ASKER
I was told loops inside an SQL stored procedure are a last resort.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
<< OR TimeLeft = '') >>
You tested that this workes.
See what you get with it replaced by
OR TimeLeft IS NULL )
You tested that this workes.
See what you get with it replaced by
OR TimeLeft IS NULL )
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
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.
http://msdn.microsoft.com/en-us/library/ms175972.aspx
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
ASKER
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]
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]
ASKER
It works, but I do not understand how it works.