Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


How to calculate total working hours of all the employees in specified dates.


How to calculate total working hours of all the employees in specified dates.

Author
Message
roja1202
roja1202
SSC Journeyman
SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)

Group: General Forum Members
Points: 78 Visits: 105
Hi,

I'm a learner, please help me by solving the following issue.
I have data of all employees whose login/logout times are different from one day to other day.
I would like to calculate the total working hours (only inside the office excluding out side the office) of
"all the employees" in all the dates from 2011-04-02 to 2012-04-06.
Some part of the data is given below for one particular date (stagingINOUT is the table name) :

CardNo IODate IOTime IOTypeN datetime
116 2011-05-13 09:44:01.0000000 12 2011-05-13 09:44:01.000
117 2011-05-13 09:44:16.0000000 12 2011-05-13 09:44:16.000
117 2011-05-13 09:46:55.0000000 11 2011-05-13 09:46:55.000
116 2011-05-13 09:56:58.0000000 11 2011-05-13 09:56:58.000
111 2011-05-13 09:58:15.0000000 12 2011-05-13 09:58:15.000
116 2011-05-13 09:58:19.0000000 12 2011-05-13 09:58:19.000
117 2011-05-13 09:59:45.0000000 12 2011-05-13 09:59:45.000
116 2011-05-16 18:31:45.0000000 11 2011-05-16 18:31:45.000

In the above Case, Card No is employee's unique identity (100 to 167)
IODate is a Date, IOtime is the different login/ logout timings
in IOTypeN 12 is for ENTRY, 11 is for EXIT.

I 'm looking for the result as (Below data is Randomly given):

CardNo IODate TotalHours Inoffice
116 2011-05-13 8:41:33 6:10:05
117 2011-05-14 10:32:00 8:25:43
118 2011-05-15 12:03:27 7:42:36
...
...
...

Even Stored Procedure or Function can be accepted.
Any help would be much appreciated.
For additional data please find the attached Text file.
Attachments
SQLServerCentral_Query1.txt (27 views, 4.00 MB)
Sean Lange
Sean Lange
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16580 Visits: 17024
Please read the link in my signature about best practices when posting questions. We don't need 75k rows of data in a text file. We need to have create table and insert statements. We don't really need that much data, just enough to represent the issue you are trying to solve.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
ken.willekens
ken.willekens
Old Hand
Old Hand (354 reputation)Old Hand (354 reputation)Old Hand (354 reputation)Old Hand (354 reputation)Old Hand (354 reputation)Old Hand (354 reputation)Old Hand (354 reputation)Old Hand (354 reputation)

Group: General Forum Members
Points: 354 Visits: 802
Well, I gave it a go. I used the sample data in the post to create two functions to convert time to seconds and the other way around to convert seconds back to time. I then used a GROUP BY to aggregate the data on CardNo and IODate. With the sample data I was able to create a query that returned a decent result.

Unfortunately I used a couple of assumptions to calculate the 'InOffice' value, namely that every IN scan has one OUT scan and that both the IN and OUT scan occur the same day (so not usable in an 24 hour time registration situation). After importing your data into a temp table I discovered that my assumptions were wrong. Just one example IODate 2012-04-03, CardNo 100 has numerous IN scans without an OUT scan. There are numerous other 'Data annomalies'. Because of this the 'InOffice' value will be wrong in my query.

I therefore don't think it is possible, in any easy way, to get the result you want without first improving the data quality. Below is the code that worked for the sample data. Perhaps you, or somebody else, can use it to find a solution. I will be following the thread to see what sharper minds then my own can make of it.

SELECT 
StIn.CardNo
,StIn.IODate
,dbo.fnSecondsToTime(DateDiff(ss,MIN(StIn.IOTime), MAX(StOut.IOTime))) as TotalHours
,dbo.fnSecondsToTime(SUM(dbo.fnTimeToSeconds(StOut.IOTime)) - SUM(dbo.fnTimeToSeconds(StIn.IOTime))) as Inoffice
FROM
stagingINOUT StIn,
stagingINOUT StOut
WHERE
StIn.IOTypeN = 12
AND StOut.IOTypeN = 11
AND StIn.CardNo = StOut.CardNo
GROUP BY
StIn.CardNo,
StIn.IODate
ORDER BY
CardNo
,IODate


Sean Lange
Sean Lange
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16580 Visits: 17024
Ken it looks like you tried something to help here. Unfortunately you have a couple of scalar functions in your code and you didn't post the definition of them. BTW, scalar functions are notoriously slow, even worse when you have them nested like you do. ;-)

I am certain that we can resolve the problem but without table definitions it is challenging. At best we can guess what the datatypes are. If the OP comes back and posts that there probably be a number of innovative ways to tackle this.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
ken.willekens
ken.willekens
Old Hand
Old Hand (354 reputation)Old Hand (354 reputation)Old Hand (354 reputation)Old Hand (354 reputation)Old Hand (354 reputation)Old Hand (354 reputation)Old Hand (354 reputation)Old Hand (354 reputation)

Group: General Forum Members
Points: 354 Visits: 802
You are completely right. The function definition isn't included because I am not able to post them. For some reason it keeps timing out if I try to post or preview them. I'll try again later.

You are also right about the scalar function performance. Their performance is terrible and particularly in larger data sets. This was just an attempt to get the result first. Performance can later be improved by putting the functionality of the functions directly in the query.

I will wait to see if the OP can post some additional information that can help in solving this problem.
roja1202
roja1202
SSC Journeyman
SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)

Group: General Forum Members
Points: 78 Visits: 105
Thanks allot for your replies.

I can provide you the definition of stagingINOUT table's Definition.

CREATE TABLE dbo.StagingINOUT (CardNo int ,
IODate date ,
IOTime time (7),
IOTypeN int,
DATETIME datetime)

If any other information is required, please let me know.
Waiting for the better solution

Thankyou
ken.willekens
ken.willekens
Old Hand
Old Hand (354 reputation)Old Hand (354 reputation)Old Hand (354 reputation)Old Hand (354 reputation)Old Hand (354 reputation)Old Hand (354 reputation)Old Hand (354 reputation)Old Hand (354 reputation)

Group: General Forum Members
Points: 354 Visits: 802
Here are the function definitions that were missing in my previous post. Perhaps it can be of use.

The extra information about the table isn't any help to me. I still feel the data quality isn't optimal which makes the problem a lot harder. But somebody else can probably figure it out and I am interested to see how they will do it.

CREATE FUNCTION fnSecondsToTime (@TotalSeconds int)
RETURNS varchar(8)
AS
BEGIN
RETURN (SELECT CONVERT(char(8), DATEADD(second, convert(int,@TotalSeconds), '19000101'), 108))
END
GO

CREATE FUNCTION fnTimeToSeconds (@TimeToConvert time)
RETURNS int
AS
BEGIN
RETURN (SELECT (DATEPART(hour, @TimeToConvert) * 3600) +(DATEPART(minute, @TimeToConvert) * 60) + DATEPART(second, @TimeToConvert))
END
GO



roja1202
roja1202
SSC Journeyman
SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)

Group: General Forum Members
Points: 78 Visits: 105
Ken,
When i tried to execute the select query, It took around 3 mints to execute and getting the following error.

Msg 8115, Level 16, State 2, Line 1
Arithmetic overflow error converting expression to data type int.
ken.willekens
ken.willekens
Old Hand
Old Hand (354 reputation)Old Hand (354 reputation)Old Hand (354 reputation)Old Hand (354 reputation)Old Hand (354 reputation)Old Hand (354 reputation)Old Hand (354 reputation)Old Hand (354 reputation)

Group: General Forum Members
Points: 354 Visits: 802
The running time is because of the functions. You could put the function logic in the main query for better performance but you would be sacrificing readibility.

The arithmic overflow error is because of the asumptions I made as I explained in my first post and happens at the 'InOffice' field. If you comment that out the query will run without error. But you would still have to fix the 'InOffice' calculation.
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