

Grasshopper
Group: General Forum Members
Last Login: Friday, July 12, 2013 7:27 AM
Points: 12,
Visits: 24


sorry ı donot understand anything I think it is not a code which I need.




SSCrazy
Group: General Forum Members
Last Login: Yesterday @ 7:09 AM
Points: 2,868,
Visits: 3,350


So the earliest registered time is the login_time and the latest regestered time is the log_out time. All other registrations are ignored. The login and logout times are altered if they comply to your rules (i.e. login_time earlier then 09:00 will be 09:00 and log_out time between 14:00 and 18:00 will be 18:00). After the times are altered (only when needed) the working time is the difference between log_in time and log_out time.
The code below will give you what you need:
select full_name , MIN(CASE WHEN convert(time, time_register) < '09:00' then DATEADD(minute, datediff(minute, convert(time, time_register), '09:00') , time_register) else time_register end) as login_time , MAX(CASE WHEN convert(time, time_register) between '14:00' and '18:00' then DATEADD(minute, datediff(minute, convert(time, time_register), '18:00') , time_register) else time_register end) as logout_time , cast(DATEDIFF(minute , MIN(CASE WHEN convert(time, time_register) < '09:00' then DATEADD(minute, datediff(minute, convert(time, time_register), '09:00') , time_register) else time_register end) , MAX(CASE WHEN convert(time, time_register) between '14:00' and '18:00' then DATEADD(minute, datediff(minute, convert(time, time_register), '18:00') , time_register) else time_register end)) as decimal(5,2) )/60 as hours_worked from logins group by full_name , CONVERT(date, time_register)
** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **




Grasshopper
Group: General Forum Members
Last Login: Friday, July 12, 2013 7:27 AM
Points: 12,
Visits: 24


in this code some of users log_in time and log_out time and worked_hour is coming wrong.
for example some of user coming like this :
FULLNAME LOG_IN LOG_OUT WORKINGHOUR ....ali sever........01012013 11:02:36 01012013 18:00:47 0.00000 ....serap genc.... 01042013 09:00:24 01042013 09:00:24 (0.11666) ....serap genc.... 01032013 18:03:00 01032013 18:03:00 0.00000 ....ali sever........01022013 11:02:36 01022013 18:00:47 0.00000 ....ali sever........01052013 18:03:00 01052013 18:00:00 (0.05000)
How can I solve this problem ? I think some of case statement will be improve. Somethings can not control correctly. How can I control this ? you understand what I mean and you can give idea how to solve it ?
and an other problem when I try to change MIN case < 09:00 to BETWEEN '06:00' AND '14:00' I take error message
Msg 8115, Level 16, State 8, Line 1 Arithmetic overflow error converting int to data type numeric.
?? thank you :)




Grasshopper
Group: General Forum Members
Last Login: Friday, July 12, 2013 7:27 AM
Points: 12,
Visits: 24


And also some of users some of days log_in time and log_out time is same. Because user have only one record in that day. When it is like this, log_in time aquall to '09:00' Thank you :)




Grasshopper
Group: General Forum Members
Last Login: Friday, July 12, 2013 7:27 AM
Points: 12,
Visits: 24


Also i want to add extra case but i get syntax erorr could you please help me ?




Grasshopper
Group: General Forum Members
Last Login: Tuesday, January 14, 2014 1:35 PM
Points: 22,
Visits: 147


And also some of users some of days log_in time and log_out time is same. Because user have only one record in that day. When it is like this, log_in time aquall to '09:00' Thank you :)
To me you have an application design issue. If I see one record as above with log_in equal to '09:00' I would assume the user has NOT logged out. It is not logical to have the same log in and log out time.
What has been proposed is the best solution to your issue, get min and max values for the log in and log out values, the time difference is hours worked.
And given the sample data you provided, HanShi's code works fine. This is the result I get from his sample data.
full_name login_time logout_time hours_worked ali sever 20130101 09:00:00.000 20130101 18:30:00.000 9.500000 pinar gezer 20130101 09:20:00.000 20130101 18:00:00.000 8.666666 ali sever 20130102 09:00:00.000 20130102 18:15:00.000 9.250000 pinar gezer 20130102 09:20:00.000 20130102 18:00:00.000 8.666666




Grasshopper
Group: General Forum Members
Last Login: Friday, July 12, 2013 7:27 AM
Points: 12,
Visits: 24


Yes code is working very good. But this case is to important for me. When login_time = logout_time ofcourse working time will be 0.000 But We'll assume that loginn_time '09:00' when they are aqual. How to do this in that code ?




Grasshopper
Group: General Forum Members
Last Login: Tuesday, January 14, 2014 1:35 PM
Points: 22,
Visits: 147


You need to modify your business rules for log out
1 if max vdate between 14:00 and 18:00 then set log out = 18:00 else use vdate except, when vdate is less than 9:00 then set log out to 9:00
Here is the case statement form HanShi updated to incorporate the above max log out business rules.
select full_name , MIN(CASE WHEN convert(time, time_register) < '09:00' then DATEADD(minute, datediff(minute, convert(time, time_register), '09:00') , time_register) else time_register end) as login_time , MAX(CASE WHEN convert(time, time_register) between '14:00' and '18:00' then DATEADD(minute, datediff(minute, convert(time, time_register), '18:00') , time_register) WHEN convert(time, time_register) < '09:00' then DATEADD(minute, datediff(minute, convert(time, time_register), '09:00') , time_register) else time_register end) as logout_time , cast(DATEDIFF(minute , MIN(CASE WHEN convert(time, time_register) < '09:00' then DATEADD(minute, datediff(minute, convert(time, time_register), '09:00') , time_register) else time_register end) , MAX(CASE WHEN convert(time, time_register) between '14:00' and '18:00' then DATEADD(minute, datediff(minute, convert(time, time_register), '18:00') , time_register) WHEN convert(time, time_register) < '09:00' then DATEADD(minute, datediff(minute, convert(time, time_register), '09:00') , time_register) else time_register end)) as decimal(5,2) )/60 as hours_worked from logins group by full_name , CONVERT(date, time_register)




SSCrazy
Group: General Forum Members
Last Login: Yesterday @ 7:09 AM
Points: 2,868,
Visits: 3,350


cagkan.koru (7/10/2013) But We'll assume that loginn_time '09:00' when they are aqual. How to do this in that code ? Change the code ...MIN(CASE WHEN convert(time, time_register) < '09:00'... on two location in the code to ...MIN(CASE WHEN convert(time, time_register) < '09:00' OR MIN(time_register) = MAX(time_register)...
But keep in mind that some calculated working hours can become a negative value, if the only registered time is before 09:00.
** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **




Grasshopper
Group: General Forum Members
Last Login: Friday, July 12, 2013 7:27 AM
Points: 12,
Visits: 24


I donot understand Could you please update full code and write again to me? thank you thank you



