July 6, 2010 at 7:52 am
You will see that your query will return strange results as to make it work, COUNT should be changed to SUM in the HAVING clause.
Yes, SUM() should be used in place of COUNT().
But, If I will add the following test data:
insert @timeLog
...
union select 113665, 320422, 'OUT', '7/3/10 4:21 PM'
union select 113666, 320422, 'LOGIN', '7/3/10 4:26 PM'
The query will stop working. As it is important to check if the LOGIN comes before OUT in the given time frame.
The OUT time will never be before the intime. Time is linear, at least on earth. 🙂
Scott Pletcher, SQL Server MVP 2008-2010
July 6, 2010 at 9:29 am
scott.pletcher (7/6/2010)
You will see that your query will return strange results as to make it work, COUNT should be changed to SUM in the HAVING clause.
Yes, SUM() should be used in place of COUNT().
But, If I will add the following test data:
insert @timeLog
...
union select 113665, 320422, 'OUT', '7/3/10 4:21 PM'
union select 113666, 320422, 'LOGIN', '7/3/10 4:26 PM'
The query will stop working. As it is important to check if the LOGIN comes before OUT in the given time frame.
The OUT time will never be before the intime. Time is linear, at least on earth. 🙂
Are you aware of relativity?
Lets have a look the following linear list of dates:
IN 05 Jan 2010
OUT 10 Jan 2010
IN 15 Jan 2010
OUT 20 Jan 2010
Now if you take the period from 1 Jan 2010 to 31 Jan 2010, you will see that all dates are in nice order: IN - OUT, IN - OUT. But, let's have a look another period. Period from 7 Jan 2010 to 17 Jan 2010, what we have here? The answer is:
OUT 10 Jan 2010
IN 15 Jan 2010
So, you can have OUT first and IN after, their possible order depends on the required period! That is exactly what I was referring to...
And, time is still linear, and we are still on earth, but it depends from which side you look in it. 😛
July 6, 2010 at 10:46 am
That would have NO effect on my query. As long as the proper pairs were present.
I've done this before on time systems. You really don't have to pass thru the dataset mutliple times sorting it multiple ways just to get the elapsed time.
Scott Pletcher, SQL Server MVP 2008-2010
July 6, 2010 at 11:14 am
scott.pletcher (7/6/2010)
That would have NO effect on my query. As long as the proper pairs were present.I've done this before on time systems. You really don't have to pass thru the dataset mutliple times sorting it multiple ways just to get the elapsed time.
Have you tested your query? Obviously not, you changed COUNT to SUM only in left part of the equality check, so the posted query stoped to return any results!
Here we are, I've made the changes to make your query to return something, and I have exact problem I've mentioned.
I have posted full test for your convinience (you can try it too):
set dateformat mdy
declare @timeLog table (SNO int, EMPID int, [STATUS] char(10), [DATE] Datetime)
insert @timeLog
select 113645, 320417, 'LOGIN', '7/2/10 3:21 PM'
union select 113648, 320417, 'PAUSE', '7/2/10 3:23 PM'
union select 113649, 320417, 'RESUME', '7/2/10 3:24 PM'
union select 113650, 320417, 'OUT', '7/2/10 3:54 PM'
union select 113651, 320418, 'LOGIN', '7/2/10 4:21 PM'
union select 113652, 320418, 'PAUSE', '7/2/10 4:31 PM'
union select 113653, 320418, 'RESUME', '7/2/10 4:33 PM'
union select 113654, 320418, 'OUT', '7/2/10 4:54 PM'
union select 113655, 320418, 'LOGIN', '7/3/10 4:21 PM'
union select 113656, 320418, 'PAUSE', '7/3/10 4:26 PM'
union select 113657, 320418, 'RESUME', '7/3/10 4:28 PM'
union select 113658, 320418, 'OUT', '7/3/10 4:54 PM'
union select 113659, 320419, 'LOGIN', '7/3/10 4:21 PM'
union select 113662, 320419, 'OUT', '7/3/10 4:54 PM'
union select 113665, 320422, 'LOGIN', '7/2/10 4:26 PM'
union select 113666, 320422, 'OUT', '7/3/10 4:21 PM'
union select 113667, 320422, 'LOGIN', '7/3/10 4:26 PM'
union select 113668, 320422, 'OUT', '7/4/10 4:26 PM'
select EMPID, cast(WorkMins / 60 AS varchar(5)) + ':' +
right('0' + cast(WorkMins % 60 AS varchar(2)), 2) as [Work HH:MM]
from (
select EMPID,
(sum(case when STATUS = 'out' then datediff(minute, '19700101', [DATE]) else 0 end) -
sum(case when STATUS = 'login' then datediff(minute, '19700101', [DATE]) else 0 end)) -
(sum(case when STATUS = 'resume' then datediff(minute, '19700101', [DATE]) else 0 end) -
sum(case when STATUS = 'pause' then datediff(minute, '19700101', [DATE]) else 0 end)) WorkMins
from @timelog
where [DATE] between '3 Jul 2010 00:00:00.000' and '3 Jul 2010 23:59:59.997'
group by EMPID
having sum(case when STATUS = 'out' then 1 else 0 end) = sum(case when STATUS = 'login' then 1 else 0 end)
and sum(case when STATUS = 'resume' then 1 else 0 end) = sum(case when STATUS = 'pause' then 1 else 0 end)
) as derived
order by EMPID
-- RESULTS:
/*
(18 row(s) affected)
EMPID Work HH:MM
----------- ----------
320418 0:31
320419 0:33
320422 0:-5
(3 row(s) affected)
*/
Please check what the result is for EMPID 320422.
It may be possible to twick your query so it will do job properly, but I have no time for this right now. I have also had similar tasks done before, but they were similar not the same...
July 6, 2010 at 11:32 am
As I have mentioned in my first post, that my query will only take in count "closed" periods.
That is why I have not added filter for time period, as new it will not be able to take everything in count.
In order to calculate elapsed time properly, the query should take period start date as LOGIN time for every orphan OUT and period end time for every orphan LOGIN and the same for PAUSE periods...
July 6, 2010 at 11:42 am
Yes, you can "trick" it my making up a logout time occuring before the login time. In real life, that WON'T happen. Time is linear. You CANNOT clock out from a shift before you clocked in for that shift.
Scott Pletcher, SQL Server MVP 2008-2010
July 6, 2010 at 11:59 am
Scott, could you please advise where you found that I have "tricked" the test data?
For ecah employee there is a record with LOGIN time before record with OUT time!
You are failing to understand that the period itself may conflict with login/out time and your query fails to deal with it!
I will try again:
If user loggs in MONDAY EVENING, then loggs out TUESDAY MORNING then loggs back in on TUESDAY EVENING and loggs out on WEDNESDAY MORNING. Then, if you run your query for TUESDAY, it will calculate NEGATIVE time!
My query will not calculate any time in this case, but I have mentioned it in my first post!
There is another complication for the following set (again all records are in perfect order IN-OUT):
set dateformat mdy
declare @timeLog table (SNO int, EMPID int, [STATUS] char(10), [DATE] Datetime)
insert @timeLog
select 113665, 320422, 'LOGIN', '7/2/10 4:26 PM'
union select 113666, 320422, 'OUT', '7/3/10 4:21 PM'
union select 113667, 320422, 'LOGIN', '7/3/10 4:26 PM'
union select 113666, 320422, 'OUT', '7/3/10 4:28 PM'
union select 113667, 320422, 'LOGIN', '7/3/10 4:29 PM'
union select 113668, 320422, 'OUT', '7/4/10 4:26 PM'
For a period from '3 Jul 2010 00:00:00.000' to '3 Jul 2010 23:59:59.997'
your query will caculate negative time, my will calculate only for the LOGIN-OUT period which completely within required one, ignoring that the user was loggedin for much longer time.
July 6, 2010 at 12:05 pm
If by "run it for Tuesday", then you mean "ignore Wednesday's" rows in the query, then no, it still won't, because the count of clock-in will not match the count of clock-out.
As I also stated clearly, the counts must match for my direct method -- avoiding all the multiple scans of the table, sorts and internal loops -- to work.
Scott Pletcher, SQL Server MVP 2008-2010
July 6, 2010 at 12:13 pm
scott.pletcher (7/6/2010)
If by "run it for Tuesday", then you mean "ignore Wednesday's" rows in the query, then no, it still won't, because the count of clock-in will not match the count of clock-out.As I also stated clearly, the counts must match for my direct method, avoiding all the sorts and internal loops, to work.
FOR GOD'E SAKE, TRY YOUR QUERY!
Again:
If user loggs in MONDAY EVENING, then loggs out TUESDAY MORNING then loggs back in on TUESDAY EVENING and loggs out on WEDNESDAY MORNING.
Your query, when run for TUESDAY, will find 1 (ONE) OUT record and 1 (ONE) LOGIN record,
1 = 1, therefore it will calculate duration and end up with NEGATIVE result.
So, basically what you're saying is: when you run such a query you should make sure first that the required period boundaries sould not fall within LOGIN-OUT times. The question is how to know this without checking it first.
I have edited the prev. post to include one more interesting situation...
July 6, 2010 at 1:12 pm
I admit I just do these when I'm taking a quick break. I have so much work to do right now I just don't have even break time right now to use on this 🙂 .
Scott Pletcher, SQL Server MVP 2008-2010
July 6, 2010 at 2:41 pm
I am off to Italy for very long weekend tomorrow, so I should care less 😀
But this is quite a nice puzzle, which I am going to have a look into when I will be back, of course if I will not forget about it during my weekend 😀
Basically, to calculate summary duration of multiple occurances between datetime START-END markers properly for any given period, period boundaries must be taken in count in case if the START-END markers are outside these boundaries :w00t:
It should be the way, and I have feeling that I have done something like this may be year or so ago...
Viewing 11 posts - 16 through 25 (of 25 total)
You must be logged in to reply to this topic. Login to reply