Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««123»»

calculate working hours between 1 year ? Expand / Collapse
Author
Message
Posted Tuesday, July 9, 2013 7:18 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

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.
Post #1471603
Posted Tuesday, July 9, 2013 7:22 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 6:08 AM
Points: 2,482, Visits: 3,028
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’! **
Post #1471604
Posted Wednesday, July 10, 2013 5:44 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

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........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 :)
Post #1472063
Posted Wednesday, July 10, 2013 6:40 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

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 :)
Post #1472087
Posted Wednesday, July 10, 2013 7:02 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

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 ?
Post #1472108
Posted Wednesday, July 10, 2013 7:23 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

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 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
Post #1472119
Posted Wednesday, July 10, 2013 7:31 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

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 ?
Post #1472123
Posted Wednesday, July 10, 2013 7:48 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

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)

Post #1472143
Posted Wednesday, July 10, 2013 8:13 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 6:08 AM
Points: 2,482, Visits: 3,028
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’! **
Post #1472172
Posted Wednesday, July 10, 2013 8:16 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

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
Post #1472175
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse