I have a table with the following columns
company_Id employee_id logon_time_id logoff_time start_valid_period end_valid_period
Employee's working time should only be counted if it is between start_valid_period and end_valid_period
So, if I have for employee1 from company1
logon_time_id = 04/07/2006 11:00
loggoff_time = 04/07/2006 12:20
start_valid_period = 04/07/2006 12:10
end_valid_period = 04/07/2006 12:30
I should consider 04/07/2006 12:10 as the initial datetime, 04/07/2006 12:20 as the final datetime, and count only 10min of work to him. In code:
if(logon_time_id < start_valid_period) initialDatetime = start_valid_period else initialDatetime = logon_time_id
if(logoff_time < end_valid_period) finalDatetime = logoff_time else finalDatetime = end_valid_period
Is there anyway I can do this in a query, without using a stored procedure with "ifs" and everything else?
Thank you!
Hi!
Forget one thing! I'm wondering if there is a way of doing it without using the case statement as well!
Thank you!
|||
use datediff
http://msdn2.microsoft.com/en-us/library/ms189794.aspx
the case keyword in sql is very powerful you can use it in the query to replace value
this is a single select statement to solve your problem
select
datediff( mi,
case
when logon_time_id< start_valid_period then start_valid_period
else logon_time_id
end
,-- comma
case
when loggoff_time > end_valid_period then end_valid_period
else loggoff_time
end
)as minutesworked
from mytime
joeydj
|||
use datediff
http://msdn2.microsoft.com/en-us/library/ms189794.aspx
the case keyword in sql is very powerful you can use it in the query to replace value
this is a single select statement to solve your problem
select
datediff( mi,
case
when logon_time_id< start_valid_period then start_valid_period
else logon_time_id
end
,-- comma
case
when loggoff_time > end_valid_period then end_valid_period
else loggoff_time
end
)as minutesworked
from mytime
joeydj
|||
Case would be easiest and clearest, but you could do:
select (select max(dateval)
from (select login_time_id as dateval
union all
select start_valid_period)) as initialDatetime,
(select min(dateval)
from (select logoff_time
union all
select end_valid_period)) as initialDatetime
from ...
|||
The first thing to note when comparing date overlaps is that there are 6 cases -- 4 with overlap and 2 without. Presumably, if the user was logged in only during a period that was not valid, the elapsed time should be zero. Using the logic as listed (ifs and CASEs) you could end up with a negative time accounting.
Now for a trick from the old days, before CASE was available (we always walked uphill barefoot a lot in those days, too ;) ). There is a function called SIGN() that returns -1, 0, or 1 depending on the sign of a number. By coercing it into a binary 1 or 0 and multiplying by it, you can get the same effect as a conditional.
In this case, the problem can be recast as increase the start_valid by the difference with the logon time as long as long as the difference is greater than zero. Similarly, the end_valid should be decreased by the difference of the logoff time as long as that difference is negative. Once these adjustments have been done, now apply the DateDiff().
Here is some code that should do the trick. As noted above, the case of no overlap is handled separately. The CASE statements are only to explain which case is being handled.
Drop Table #timekeeper
go
Create Table #timekeeper(
username varchar(30),
logon_time_id datetime,
loggoff_time datetime,
start_valid_period datetime,
end_valid_period datetime
)
Insert #timekeeper values( 'Fred', '04/07/2006 11:00', '04/07/2006 12:20', '04/07/2006 12:10', '04/07/2006 12:30' )
Insert #timekeeper values( 'Wilma', '04/07/2006 12:20', '04/07/2006 12:40', '04/07/2006 12:10', '04/07/2006 12:30' )
Insert #timekeeper values( 'Barney', '04/07/2006 11:20', '04/07/2006 12:40', '04/07/2006 12:10', '04/07/2006 12:30' )
Insert #timekeeper values( 'Betty', '04/07/2006 12:20', '04/07/2006 12:25', '04/07/2006 12:10', '04/07/2006 12:30' )
Insert #timekeeper values( 'Pebbles', '04/07/2006 11:00', '04/07/2006 11:25', '04/07/2006 12:10', '04/07/2006 12:30' )
Insert #timekeeper values( 'Bambam', '04/07/2006 12:35', '04/07/2006 12:50', '04/07/2006 12:10', '04/07/2006 12:30' )
Select username,
Datediff( mi,
DateAdd( ms, (( 1 + Sign( DateDiff( ms, start_valid_period, logon_time_id ) ) ) / 2) * DateDiff( ms, start_valid_period, logon_time_id ), start_valid_period ),
DateAdd( ms, (( 1 + Sign( DateDiff( ms, loggoff_time, end_valid_period ) ) ) / 2) * DateDiff( ms, end_valid_period, loggoff_time ), end_valid_period )
) as usage_minutes,
'Overlap',
Case when logon_time_id < start_valid_period then 'Logged on early' else 'Logged on during valid' end,
Case when loggoff_time < end_valid_period then 'Logged out early' else 'Logged off after valid' end
From #timekeeper
Where start_valid_period <= loggoff_time
And logon_time_id <= end_valid_period
Union All
Select username,
0,
'No overlap',
Case when logon_time_id > end_valid_period then 'Logon after valid' else '' end,
Case when loggoff_time < start_valid_period then 'Logged off before valid' else '' end
From #timekeeper
Where Not(
start_valid_period <= loggoff_time
And logon_time_id <= end_valid_period
)
Fred 10 Overlap Logged on early Logged out early
Wilma 10 Overlap Logged on during valid Logged off after valid
Barney 20 Overlap Logged on early Logged off after valid
Betty 5 Overlap Logged on during valid Logged out early
Pebbles 0 No overlap Logged off before valid
Bambam 0 No overlap Logon after valid
|||Thank you!!!
