SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


calculate working hours between 1 year ?


calculate working hours between 1 year ?

Author
Message
cagkan.koru
cagkan.koru
Grasshopper
Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)

Group: General Forum Members
Points: 12 Visits: 24
sorry ı donot understand anythingSad
I think it is not a code which I need.
HanShi
HanShi
SSCarpal Tunnel
SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)

Group: General Forum Members
Points: 4382 Visits: 3668
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’! **
cagkan.koru
cagkan.koru
Grasshopper
Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)

Group: General Forum Members
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........01-01-2013 11:02:36 ------01-01-2013 18:00:47 --------------0.00000
....serap genc.... 01-04-2013 09:00:24----- 01-04-2013 09:00:24 ---------------(-0.11666)
....serap genc.... 01-03-2013 18:03:00----- 01-03-2013 18:03:00 ----------------0.00000
....ali sever........01-02-2013 11:02:36 ------01-02-2013 18:00:47 --------------0.00000
....ali sever........01-05-2013 18:03:00 ------01-05-2013 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 Smile
cagkan.koru
cagkan.koru
Grasshopper
Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)

Group: General Forum Members
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 Smile
cagkan.koru
cagkan.koru
Grasshopper
Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)

Group: General Forum Members
Points: 12 Visits: 24
Also i want to add extra case but i get syntax erorr could you please help me ?
keebler96
keebler96
SSC Rookie
SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)

Group: General Forum Members
Points: 38 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 Smile


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 2013-01-01 09:00:00.000 2013-01-01 18:30:00.000 9.500000
pinar gezer 2013-01-01 09:20:00.000 2013-01-01 18:00:00.000 8.666666
ali sever 2013-01-02 09:00:00.000 2013-01-02 18:15:00.000 9.250000
pinar gezer 2013-01-02 09:20:00.000 2013-01-02 18:00:00.000 8.666666
cagkan.koru
cagkan.koru
Grasshopper
Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)

Group: General Forum Members
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 ?
keebler96
keebler96
SSC Rookie
SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)

Group: General Forum Members
Points: 38 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)


HanShi
HanShi
SSCarpal Tunnel
SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)

Group: General Forum Members
Points: 4382 Visits: 3668
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’! **
cagkan.koru
cagkan.koru
Grasshopper
Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)

Group: General Forum Members
Points: 12 Visits: 24
I donot understand Sad
Could you please update full code and write again to me?
thank you thank you :-)

Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search