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 12:32 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, July 12, 2013 7:27 AM
Points: 12, Visits: 24
I have limited table like :

FUL NAME DATE
ali sever 01-01-2013 12:50
ali sever 01-01-2013 18:30
ali sever 01-01-2013 15:30
ali sever 01-01-2013 08:30
pinar gezer 01-01-2013 09:20
pinar gezer 01-01-2013 17:50
pinar gezer 01-01-2013 12:50
. .
. .
. .

Our job is starting 09:00 and finish 18:00.
There are about 50 users and every user have lots of login and logout time in same day.
I want to calculate total working hour but ıf users came before 09:00 we will set login time defult 09:00. and logout time will be max hour in same day.

for example you can see want I want in example

FUL NAME login time logout time total work time
ali sever 01-01-2013 09:00 01-01-2013 18:30 9.5 hours
. . .
. . .
. . .


------------------------------------------------------------------- every total time

this code brings lots of log_in and log_out time for 1 user.
There are lots of log_in and log_out time for 1 user.But I donot know which is log_in which is log_out.
There will be only one log_in and only one log_out time in same day for 1 user. I want to find log_in and log_out time according to time from this table.Only just 1 colomb here.

I will decide log_in time like this :
1-) if user vdate_time <= 09:00 then set log_in time '09:00'
2-) if user vdate_time > 09:00 then set log_in time 'vdate_time' (not change,stay same)

I will decide log_out time like this:
1-) if user vdate_time <= 18:00 then set log_out time '18:00'
2-) if user vdate_time >18:00 then set log_out time ' vdate_time' (not change,stay same)

Lastly I need total work time in 1 year for 1 user and for all .


----------------
I'll try to explain one by one.
Lets talk about your wonderful example
SAMPLE DATA (registered time for 1 user on one day, rownumbers added for clarification):
1....ali sever....01-01-2013 13:00
2....ali sever....01-01-2013 18:30
3....ali sever....01-01-2013 15:30
4....ali sever....01-01-2013 07:30
5....ali sever....01-01-2013 08:30
6....ali sever....01-01-2013 19:30

Firstly we'll have only one log_in time and only one log_out time for 1 day. Discard other 4 records,we'll chosee 2 records from 1,2,3,4,5,6. one log_in time and one log_out time

there are 2 rules in log_in time:
1-) When we look example our code will should be set log_in time = 09:00 because user log_in before 09:00 I dont want before,it's not important we'll set 09:00 if user came early.İf user come before 09:00 users log_in time will be 09:00 (we can control it with ıf or case statement ı donot know)
2-) İf user came after 09:00 we'll use registered time,there is not any modification.So log_in time will be register time.

If we look table ....ali sever....01-01-2013 log_in time will be 01-01-2013 09:00 (not 07:30,not 08:30)

After we decided log_in time then we'll decide log_out time

there are also 2 rules in log_out time:
a-) if user register time > 18:00 we'll use registered time,there is not any modification.
b-) if user register time 14:00 < registered_time < 18:00 We'll update log_out time 18:00,we'll change it with 18:00,we set log_out time 18:00

If we look table ....ali sever....01-01-2013 log_out time will be 01-01-2013 19:30 (not 18:00,not 18:30 not15:30 ..)

for example if user have like this records
1....serap genc....01-01-2013 09:30
2....serap genc....01-01-2013 09:50
3....serap genc....01-01-2013 17:45
3....serap genc....01-01-2013 17:55

serap genc log_in time = 01-01-2013 09:30 because user doesnot have any record which before 09:00(look log_in time rule 2)
serap genc log_out time = 01-01-2013 18:00 because user 14:00 < log_out < 18:00 ( look at log_out rule b)

Lastly I want to see total work time .(log_out - log_in)
like this

FULLNAME ---------LOG_IN--------------- LOG_OUT--------------------- WORKINGHOUR
....ali sever........01-01-2013 09:00 ------01-01-2013 19:30 --------------LOG_OUT - LOG_İN = MİN.
....serap genc.... 01-01-2013 09:30----- 01-01-2013 18:00 ---------------LOG_OUT - LOG_İN = MİN
.
.
.
.CONTUNİE FOR ABOUT 50 USER...
Post #1471452
Posted Tuesday, July 9, 2013 2:48 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Monday, November 3, 2014 4:48 AM
Points: 418, Visits: 589
Send some sample data .. in Create and insert scripts
Post #1471479
Posted Tuesday, July 9, 2013 2:54 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 10:41 AM
Points: 2,435, Visits: 2,975
I have build below code to get your output. I have used a CASE statement to change the register time to 09:00 when the registered time is earlier. Remove the REMARK statements before the first SELECT if you want to display how this CASE effects the values.
The CASE statement is used twice in the final SELECT: First to display the earliest time of the day and second to calculate the working hours.
/* create table and fill it with sample data */
--create table logins (full_name nvarchar(50), time_register datetime)
--insert into logins
--(FULL_NAME, time_register)
--SELECT
--'ali sever', '01-01-2013 12:50'
--union all select
--'ali sever', '01-01-2013 18:30'
--union all select
--'ali sever', '01-01-2013 15:30'
--union all select
--'ali sever', '01-01-2013 08:30'
--union all select
--'pinar gezer', '01-01-2013 09:20'
--union all select
--'pinar gezer', '01-01-2013 17:50'
--union all select
--'pinar gezer', '01-01-2013 12:50'
--union all select
--'ali sever', '01-02-2013 12:50'
--union all select
--'ali sever', '01-02-2013 18:15'
--union all select
--'ali sever', '01-02-2013 15:30'
--union all select
--'ali sever', '01-02-2013 08:30'
--union all select
--'pinar gezer', '01-02-2013 09:20'
--union all select
--'pinar gezer', '01-02-2013 17:15'
--union all select
--'pinar gezer', '01-02-2013 12:50'

/* display the change if the earliest time is before 09:00 by using a CASE statement */
--select
-- convert(time, time_register)
-- , CASE WHEN convert(time, time_register) < '09:00' -- when time_register is before 09:00...
-- then DATEADD(minute, -- ...then add (in minutes)...
-- datediff(minute, convert(time, time_register), '09:00') -- ...the difference between registered time and 09:00...
-- , time_register) -- ...to time_register
-- else time_register
-- end
--from logins

/* calculate the total working hours per day for each user, using above CASE statement */
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(time_register) 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(time_register)) 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 #1471481
Posted Tuesday, July 9, 2013 3:20 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, July 12, 2013 7:27 AM
Points: 12, Visits: 24
this code brings lots of log_in and log_out time for 1 user.
There are lots of log_in and log_out time for 1 user.But I donot know which is log_in which is log_out.
There will be only one log_in and only one log_out time in same day for 1 user. I want to find log_in and log_out time according to time from this table.Only just 1 colomb here.


I will decide log_in time like this :
1-) if user vdate_time <= 09:00 then set log_in time '09:00'
2-) if user vdate_time > 09:00 then set log_in time 'vdate_time' (not change,stay same)

I will decide log_out time like this:
1-) if user vdate_time <= 18:00 then set log_out time '18:00'
2-) if user vdate_time >18:00 then set log_out time ' vdate_time' (not change,stay same)

Lastly I need total work time in 1 year for 1 user and for all .
Post #1471493
Posted Tuesday, July 9, 2013 3:40 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 10:41 AM
Points: 2,435, Visits: 2,975
cagkan.koru (7/9/2013)
this code brings lots of log_in and log_out time for 1 user.
There are lots of log_in and log_out time for 1 user.But I donot know which is log_in which is log_out.
There will be only one log_in and only one log_out time in same day for 1 user. I want to find log_in and log_out time according to time from this table.Only just 1 colomb here.


I will decide log_in time like this :
1-) if user vdate_time <= 09:00 then set log_in time '09:00'
2-) if user vdate_time > 09:00 then set log_in time 'vdate_time' (not change,stay same)

I will decide log_out time like this:
1-) if user vdate_time <= 18:00 then set log_out time '18:00'
2-) if user vdate_time >18:00 then set log_out time ' vdate_time' (not change,stay same)

Lastly I need total work time in 1 year for 1 user and for all .


You assumptions will not work.
What if the user logged in at 07:30 and logged out at 08:00? You will register both times as a login_time and change it to 09:00. And because most registered times will be before 18:00 you will register them also/again as a logout_time and changing them to 18:00. Hence you will never have a time before 18:00. And what happens when a user logges in at 19:00 and logges out at 21:00 hours. You will register both times a a logout_time.
The best way to handle this (without changing the registration, which would be a much better solution) is to get the earliest time of the day and the latest time of the day. These will be assumed to be the login time and the logout time. That is what is done with my code in the previous post.


** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **
Post #1471504
Posted Tuesday, July 9, 2013 4:36 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, July 12, 2013 7:27 AM
Points: 12, Visits: 24
You assumptions will not work.
What if the user logged in at 07:30 and logged out at 08:00? You will register both times as a login_time and change it to 09:00. And because most registered times will be before 18:00 you will register them also/again as a logout_time and changing them to 18:00. Hence you will never have a time before 18:00. And what happens when a user logges in at 19:00 and logges out at 21:00 hours. You will register both times a a logout_time.
The best way to handle this (without changing the registration, which would be a much better solution) is to get the earliest time of the day and the latest time of the day. These will be assumed to be the login time and the logout time. That is what is done with my code in the previous post.

if the user logged in at 07:30 and logged out at 08:00 : we'll set log_in time 09:00 and log_out time 18:00. when a user logges in at 19:00 and logges out at 21:00 hours : we'll get max value to log_out time. We can set time there is no problem. I can not handle this because there are 50 people and every day there are about 200 records in 1 year there are huge :)
How can solve this problem can you understand me ?
Post #1471527
Posted Tuesday, July 9, 2013 4:37 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, July 12, 2013 7:27 AM
Points: 12, Visits: 24
if the user logged in at 07:30 and logged out at 08:00 : we'll set log_in time 09:00 and log_out time 18:00. when a user logges in at 19:00 and logges out at 21:00 hours : we'll get max value to log_out time. We can set time there is no problem. I can not handle this because there are 50 people and every day there are about 200 records in 1 year there are huge :)
How can solve this problem can you understand me ?
Post #1471529
Posted Tuesday, July 9, 2013 5:16 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 10:41 AM
Points: 2,435, Visits: 2,975
I think we don't understand eachother correctly. Maybe if I give you some examples we can get a better understanding.

SAMPLE DATA (registered time for 1 user on one day, rownumbers added for clarification):
1....ali sever....01-01-2013 13:00
2....ali sever....01-01-2013 18:30
3....ali sever....01-01-2013 15:30
4....ali sever....01-01-2013 07:30
5....ali sever....01-01-2013 08:30
6....ali sever....01-01-2013 19:30

If you order the above data on registered time, the sequence will be: 4, 5, 1, 3, 2, 6. Therefor I would say rows 4, 1 and 2 will be login times and 5, 3 and 6 will be logout times. When there is an odd number of rows, the last logout is not registered.

According to your assumtion "1-) if user vdate_time <= 09:00 then set log_in time '09:00'" the time of rows 4 and 5 will be altered to 09:00 hours. Your assumption "1-) if user vdate_time <= 18:00 then set log_out time '18:00'" will alter the time of rows 1, 3, 4 and 5 to 18:00, but I think you mean only to alter rows 1 and 3.

With the above sample data: what will be the time that needs to be calculated?
Option 1: Is it the time between 4 and 5 plus the time between 1 and 3 plus the time between 2 and 6 => 60+150+60=270 minutes ?
Option 2: Is it the time between 4 (earliest) and 6 (latest) => difference between 07:30 and 19:30 => 720 minutes ?
Option 3: Is it the time of row 4 changed to 09:00 and row 6 (latest) => difference between 09:00 and 19:30 => 630 minutes ?
Or is it something else?

The code in my post above is calculating the time according to the third option.


** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **
Post #1471546
Posted Tuesday, July 9, 2013 6:09 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, July 12, 2013 7:27 AM
Points: 12, Visits: 24
I think we don't understand eachother correctly. Maybe if I give you some examples we can get a better understanding.

SAMPLE DATA (registered time for 1 user on one day, rownumbers added for clarification):
1....ali sever....01-01-2013 13:00
2....ali sever....01-01-2013 18:30
3....ali sever....01-01-2013 15:30
4....ali sever....01-01-2013 07:30
5....ali sever....01-01-2013 08:30
6....ali sever....01-01-2013 19:30

If you order the above data on registered time, the sequence will be: 4, 5, 1, 3, 2, 6. Therefor I would say rows 4, 1 and 2 will be login times and 5, 3 and 6 will be logout times. When there is an odd number of rows, the last logout is not registered.

According to your assumtion "1-) if user vdate_time <= 09:00 then set log_in time '09:00'" the time of rows 4 and 5 will be altered to 09:00 hours. Your assumption "1-) if user vdate_time <= 18:00 then set log_out time '18:00'" will alter the time of rows 1, 3, 4 and 5 to 18:00, but I think you mean only to alter rows 1 and 3.

With the above sample data: what will be the time that needs to be calculated?
Option 1: Is it the time between 4 and 5 plus the time between 1 and 3 plus the time between 2 and 6 => 60+150+60=270 minutes ?
Option 2: Is it the time between 4 (earliest) and 6 (latest) => difference between 07:30 and 19:30 => 720 minutes ?
Option 3: Is it the time of row 4 changed to 09:00 and row 6 (latest) => difference between 09:00 and 19:30 => 630 minutes ?
Or is it something else?

The code in my post above is calculating the time according to the third option.


First of all thank you for your wonderful help.
I'll try to explain one by one.
Lets talk about your wonderful example
SAMPLE DATA (registered time for 1 user on one day, rownumbers added for clarification):
1....ali sever....01-01-2013 13:00
2....ali sever....01-01-2013 18:30
3....ali sever....01-01-2013 15:30
4....ali sever....01-01-2013 07:30
5....ali sever....01-01-2013 08:30
6....ali sever....01-01-2013 19:30

Firstly we'll have only one log_in time and only one log_out time for 1 day. Discard other 4 records,we'll chosee 2 records from 1,2,3,4,5,6. one log_in time and one log_out time

there are 2 rules in log_in time:
1-) When we look example our code will should be set log_in time = 09:00 because user log_in before 09:00 I dont want before,it's not important we'll set 09:00 if user came early.İf user come before 09:00 users log_in time will be 09:00 (we can control it with ıf or case statement ı donot know)
2-) İf user came after 09:00 we'll use registered time,there is not any modification.So log_in time will be register time.

If we look table ....ali sever....01-01-2013 log_in time will be 01-01-2013 09:00 (not 07:30,not 08:30)

After we decided log_in time then we'll decide log_out time

there are also 2 rules in log_out time:
a-) if user register time > 18:00 we'll use registered time,there is not any modification.
b-) if user register time 14:00 < registered_time < 18:00 We'll update log_out time 18:00,we'll change it with 18:00,we set log_out time 18:00

If we look table ....ali sever....01-01-2013 log_out time will be 01-01-2013 19:30 (not 18:00,not 18:30 not15:30 ..)

for example if user have like this records
1....serap genc....01-01-2013 09:30
2....serap genc....01-01-2013 09:50
3....serap genc....01-01-2013 17:45
3....serap genc....01-01-2013 17:55

serap genc log_in time = 01-01-2013 09:30 because user doesnot have any record which before 09:00(look log_in time rule 2)
serap genc log_out time = 01-01-2013 18:00 because user 14:00 < log_out < 18:00 ( look at log_out rule b)

Lastly I want to see total work time .(log_out - log_in)
like this

FULLNAME ---------LOG_IN--------------- LOG_OUT--------------------- WORKINGHOUR
....ali sever........01-01-2013 09:00 ------01-01-2013 19:30 --------------LOG_OUT - LOG_İN = MİN.
....serap genc.... 01-01-2013 09:30----- 01-01-2013 18:00 ---------------LOG_OUT - LOG_İN = MİN

.
.
.
.CONTUNİE FOR ABOUT 50 USER...

Thank you now your best help , its to important for me:)
Post #1471572
Posted Tuesday, July 9, 2013 7:04 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:33 AM
Points: 6,858, Visits: 14,148
You might get some ideas from this?

;WITH SampleData ([FUL NAME], [DATE]) AS (
SELECT 'ali sever', CAST('01-01-2013 12:50' AS DATETIME) UNION ALL
SELECT 'ali sever', '01-01-2013 18:30' UNION ALL
SELECT 'ali sever', '01-01-2013 15:30' UNION ALL
SELECT 'ali sever', '01-01-2013 08:30' UNION ALL
SELECT 'ali sever', '01-22-2013 15:30' UNION ALL
SELECT 'ali sever', '01-22-2013 08:30' UNION ALL
SELECT 'pinar gezer', '01-01-2013 09:20' UNION ALL
SELECT 'pinar gezer', '01-01-2013 17:50' UNION ALL
SELECT 'pinar gezer', '01-01-2013 12:50'
),
ShapedData AS (
SELECT
rn,
[FUL NAME],
[DATE],
[Direction] = CASE rn%2 WHEN 1 THEN 'IN' WHEN 0 THEN 'OUT' END
FROM (
SELECT
[FUL NAME],
[DATE],
rn = ROW_NUMBER() OVER(PARTITION BY [FUL NAME], CAST([DATE] AS DATE) ORDER BY [DATE])
FROM SampleData
) d
) SELECT i.*, x.*
FROM ShapedData i
OUTER APPLY (
SELECT TOP 1 *
FROM ShapedData o
WHERE o.[FUL NAME] = i.[FUL NAME] AND o.DATE > i.DATE AND o.Direction = 'OUT'
ORDER BY o.DATE ASC) x
WHERE i.Direction = 'IN'
ORDER BY i.[FUL NAME], i.[DATE], i.[Direction]



“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
Post #1471599
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse