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 Wednesday, July 10, 2013 8:33 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, July 12, 2013 7:27 AM
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.
Post #1472182
Posted Thursday, July 11, 2013 12:16 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 2:45 PM
Points: 2,242, Visits: 2,701
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’! **
Post #1472439
« Prev Topic | Next Topic »

Add to briefcase «««123

Permissions Expand / Collapse