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
I change and give this error :

Msg 130, Level 15, State 1, Line 3
Cannot perform an aggregate function on an expression containing an aggregate or a subquery.
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: 4369 Visits: 3668
HanShi (7/9/2013)
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.


In addition to the above you mention there is another rule: if there is only one single registration of a time on one day, the standard business hours will be applied (from 09:00 till 18:00). I've changed the code and added some other CASE statements to comply to your rules.

select
full_name -- return the name of the user
-- display the login time (altered when required)
, case when MIN(time_register) = MAX(time_register) -- if only one time registration (or equal registrations)...
then '09:00' -- ...the login_time will be set to 09:00
else MIN(CASE WHEN convert(time, time_register) < '09:00' -- if the earliest time is before 09:00...
then DATEADD(minute, datediff(minute, convert(time, time_register), '09:00'), time_register) -- ...the time will be set to 09:00...
else time_register -- ...else the registered time will be used
end)
end as login_time
-- display the logout time (altered when required)
, case when MIN(time_register) = MAX(time_register) -- if only one time registration (or equal registrations)...
then '18:00' -- ...the logout_time will be set to 18:00
else MAX(time_register) -- ...else the latest registered time will be used
end as logout_time
-- calculate the worked hours, using the displayed (see above) times
, case when MIN(time_register) = MAX(time_register) -- if only one time registration (or equal registrations)...
then datediff(minute, '09:00', '18:00')/60 -- ...the standard business hours are used
else cast(DATEDIFF(minute -- calculate the difference (in minutes)...
, MIN(CASE WHEN convert(time, time_register) < '09:00' -- ...between the login time (altered when required)...
then DATEADD(minute,
datediff(minute, convert(time, time_register), '09:00')
, time_register)
else time_register
end)
, MAX(time_register)) as decimal(5,2) -- ...and the latest registered time (i.e. logout time)
)/60 -- divide the calculated minutes by 60 to get the display in hours
end as hours_worked
from logins
group by
full_name -- display for each username...
, CONVERT(date, time_register) -- ...and for each day



** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **
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