Calculating total working hours exclude launch, smoking...and etc times

Question

Calculating total working hours exclude launch, smoking...and etc times

Sezam

Ten Centuries

Points: 1240
More actions
February 24, 2010 at 11:57 pm

#145518

Hi Forum.
I'm totally new in SQL. This is a database which is imported from Data.txt log file. Log file tracks every employee punch in and punch out time and date, employee name.
I've to create DW dimension called working hours. In working hours should be calculated a person's time spent in office, excluding launch time, smoking time... Person may go out several time from office. Only in office time summary can be working hours.
I've been searching for similar issue in forum, but I get lost.
Please help for solving this issue

Viewing 15 posts - 1 through 15 (of 18 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004414 More actions · Answer 1

Most people like to test their solutions before posting. Please post the data in a readily consumable format. See the first link in my signature line below.

Also, people like to know that you've actually tried. Please post the code you've tried.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Sezam Ten Centuries Points: 1240 More actions · Answer 2

Hi again, here is a code for my Data

--===== Create the test table with

CREATE TABLE [dbo].[Data 00](

[Transaction date] [nvarchar](50) NULL,

[Transaction Time] [nvarchar](50) NULL,

[Transactin Code] [nvarchar](50) NULL,

[Transaction Description] [nvarchar](50) NULL,

[Staff No] [nvarchar](50) NULL,

[Staff Name] [nvarchar](50) NULL,

[Card Number] [nvarchar](50) NULL,

[Department] [nvarchar](50) NULL,

[Job] [nvarchar](50) NULL,

[Shift] [nvarchar](50) NULL,

) ON [PRIMARY]

--===== All Inserts into the IDENTITY column

SET IDENTITY_INSERT [dbo].[Data 00] ON

--===== Insert the test data into the test table

INSERT INTO [dbo].[Data 00]

([Transaction date], [Transaction Time], [Transactin Code], [Transaction Description], [Staff No], [Staff Name],[Card Number], [Department], [Job], [Shift])

SELECT '20100219','124541','Ce','Antipassback Violation (Entry)','DM010','Mahmud','0086054980','SC','Office Hour','Office Hour', UNION ALL

SELECT '20100219','124544','Df','Pulse Door Open','','','','','','', UNION ALL

SELECT '20100219','124550','Cb','Valid Card Exit','DM010','Mahmud','0086054980','SC','Office Hour','Office Hour', UNION ALL

SELECT '20100219','124556','Ca','Valid Card Entry','DM010','Mahmud','0086054980','SC','Office Hour','Office Hour', UNION ALL

SELECT '20100219','124642','Cb','Valid Card Exit','DM010','Mahmud','0086054980','SC','Office Hour','Office Hour', UNION ALL

SELECT '20100219','130615','Cb','Valid Card Exit','DM009','Choy Kok Kgoon','0086054972','SD','Office Hour','Office Hour', UNION ALL

SELECT '20100219','130836','Cb','Valid Card Exit','DM004','Sharof','0086054978','SD','Office Hour','Office Hour', UNION ALL

SELECT '20100219','130844','Ca','Valid Card Entry','DM004','Sharof','0086054978','SD','Office Hour','Office Hour', UNION ALL

SELECT '20100219','130919','Cb','Valid Card Exit','DM004','Sharof','0086054978','SD','Office Hour','Office Hour', UNION ALL

SELECT '20100219','131041','CC','Unknown Card Number (Exit)','','Unlisted User','0052023826','','','', UNION ALL

SELECT '20100219','131057','Cc','Unknown Card Number (Entry)','','Unlisted User','0052023826','','','', UNION ALL

SELECT '20100219','131103','Ca','Valid Card Entry','DM003','Nodir','0086054968','SD','Office Hour','Office Hour', UNION ALL

SELECT '20100219','131112','Cb','Valid Card Exit','DM003','Nodir','0086054968','SD','Office Hour','Office Hour', UNION ALL

SELECT '20100219','131125','Df','Pulse Door Open','','','','','','', UNION ALL

SELECT '20100219','142311','Ca','Valid Card Entry','DM009','Choy Kok Kgoon','0086054972','SD','Office Hour','Office Hour', UNION ALL

SELECT '20100219','142806','Cb','Valid Card Exit','DM009','Choy Kok Kgoon','0086054972','SD','Office Hour','Office Hour', UNION ALL

SELECT '20100219','142821','Ca','Valid Card Entry','DM005','Tan Tyug Tyug','0086054969','SC','Office Hour','Office Hour', UNION ALL

SELECT '20100219','142827','Ca','Valid Card Entry','DM009','Choy Kok Kgoon','0086054972','SD','Office Hour','Office Hour', UNION ALL

SELECT '20100219','142833','Ca','Valid Card Entry','DM015','Ramona Jim','0086054974','SC','Office Hour','Office Hour', UNION ALL

SELECT '20100219','142903','Df','Pulse Door Open','','','','','','', UNION ALL

SELECT '20100219','142912','Ce','Antipassback Violation (Entry)','DM009','Choy Kok Kgoon','0086054972','SD','Office Hour','Office Hour', UNION ALL

SELECT '20100219','142916','Df','Pulse Door Open','','','','','','', UNION ALL

SELECT '20100219','143136','Cb','Valid Card Exit','DM005','Tan Tyug Tyug','0086054969','SC','Office Hour','Office Hour', UNION ALL

SELECT '20100219','143139','Cb','Valid Card Exit','DM015','Ramona Jim','0086054974','SC','Office Hour','Office Hour', UNION ALL

SELECT '20100219','143142','Cb','Valid Card Exit','DM009','Choy Kok Kgoon','0086054972','SD','Office Hour','Office Hour', UNION ALL

SELECT '20100219','153748','Ca','Valid Card Entry','DM002','Liew Wai Foong','0086054966','SC','Office Hour','Office Hour', UNION ALL

SELECT '20100219','153753','Ce','Antipassback Violation (Entry)','DM002','Liew Wai Foong','0086054966','SC','Office Hour','Office Hour', UNION ALL

SELECT '20100219','153814','Cg','Reset Antipassback','DM002','Liew Wai Foong','0086054966','SC','Office Hour','Office Hour', UNION ALL

SELECT '20100219','153827','Df','Pulse Door Open','','','','','','', UNION ALL

SELECT '20100219','153831','Ca','Valid Card Entry','DM002','Liew Wai Foong','0086054966','SC','Office Hour','Office Hour', UNION ALL

SELECT '20100219','153838','Cb','Valid Card Exit','DM002','Liew Wai Foong','0086054966','SC','Office Hour','Office Hour', UNION ALL

SELECT '20100219','153900','Cg','Reset Antipassback','DM002','Liew Wai Foong','0086054966','SC','Office Hour','Office Hour',

--===== Set the identity insert back to normal

SET IDENTITY_INSERT [dbo].[Data 00]

As I stated above, I need to calculate every person's total working time (for everyday) excluding time spent outside of office. Total time during 'Valid Card entry' and 'Valid card Exit''s.

Thanks in advance...

Paul White SSC Guru Points: 150468 More actions · Answer 3

Sigh. And here is the corrected version of the sample-data-generation script.

This one doesn't try to set identity_insert for a table with no identity column, and also has the decided advantage of compiling.

USE tempdb

--===== Create the test table with

CREATE TABLE [dbo].Data_00(

[Transaction date] [nvarchar](50) NULL,

[Transaction Time] [nvarchar](50) NULL,

[Transactin Code] [nvarchar](50) NULL,

[Transaction Description] [nvarchar](50) NULL,

[Staff No] [nvarchar](50) NULL,

[Staff Name] [nvarchar](50) NULL,

[Card Number] [nvarchar](50) NULL,

[Department] [nvarchar](50) NULL,

[Job] [nvarchar](50) NULL,

[Shift] [nvarchar](50) NULL,

) ON [PRIMARY]

--===== Insert the test data into the test table

INSERT INTO [dbo].Data_00

([Transaction date], [Transaction Time], [Transactin Code], [Transaction Description], [Staff No], [Staff Name], [Card Number], [Department], [Job], [Shift])

SELECT '20100219','124541','Ce','Antipassback Violation (Entry)','DM010','Mahmud','0086054980','SC','Office Hour','Office Hour' UNION ALL

SELECT '20100219','124544','Df','Pulse Door Open','','','','','','' UNION ALL

SELECT '20100219','124550','Cb','Valid Card Exit','DM010','Mahmud','0086054980','SC','Office Hour','Office Hour' UNION ALL

SELECT '20100219','124556','Ca','Valid Card Entry','DM010','Mahmud','0086054980','SC','Office Hour','Office Hour' UNION ALL

SELECT '20100219','124642','Cb','Valid Card Exit','DM010','Mahmud','0086054980','SC','Office Hour','Office Hour' UNION ALL

SELECT '20100219','130615','Cb','Valid Card Exit','DM009','Choy Kok Kgoon','0086054972','SD','Office Hour','Office Hour' UNION ALL

SELECT '20100219','130836','Cb','Valid Card Exit','DM004','Sharof','0086054978','SD','Office Hour','Office Hour' UNION ALL

SELECT '20100219','130844','Ca','Valid Card Entry','DM004','Sharof','0086054978','SD','Office Hour','Office Hour' UNION ALL

SELECT '20100219','130919','Cb','Valid Card Exit','DM004','Sharof','0086054978','SD','Office Hour','Office Hour' UNION ALL

SELECT '20100219','131041','CC','Unknown Card Number (Exit)','','Unlisted User','0052023826','','','' UNION ALL

SELECT '20100219','131057','Cc','Unknown Card Number (Entry)','','Unlisted User','0052023826','','','' UNION ALL

SELECT '20100219','131103','Ca','Valid Card Entry','DM003','Nodir','0086054968','SD','Office Hour','Office Hour' UNION ALL

SELECT '20100219','131112','Cb','Valid Card Exit','DM003','Nodir','0086054968','SD','Office Hour','Office Hour' UNION ALL

SELECT '20100219','131125','Df','Pulse Door Open','','','','','','' UNION ALL

SELECT '20100219','142311','Ca','Valid Card Entry','DM009','Choy Kok Kgoon','0086054972','SD','Office Hour','Office Hour' UNION ALL

SELECT '20100219','142806','Cb','Valid Card Exit','DM009','Choy Kok Kgoon','0086054972','SD','Office Hour','Office Hour' UNION ALL

SELECT '20100219','142821','Ca','Valid Card Entry','DM005','Tan Tyug Tyug','0086054969','SC','Office Hour','Office Hour' UNION ALL

SELECT '20100219','142827','Ca','Valid Card Entry','DM009','Choy Kok Kgoon','0086054972','SD','Office Hour','Office Hour' UNION ALL

SELECT '20100219','142833','Ca','Valid Card Entry','DM015','Ramona Jim','0086054974','SC','Office Hour','Office Hour' UNION ALL

SELECT '20100219','142903','Df','Pulse Door Open','','','','','','' UNION ALL

SELECT '20100219','142912','Ce','Antipassback Violation (Entry)','DM009','Choy Kok Kgoon','0086054972','SD','Office Hour','Office Hour' UNION ALL

SELECT '20100219','142916','Df','Pulse Door Open','','','','','','' UNION ALL

SELECT '20100219','143136','Cb','Valid Card Exit','DM005','Tan Tyug Tyug','0086054969','SC','Office Hour','Office Hour' UNION ALL

SELECT '20100219','143139','Cb','Valid Card Exit','DM015','Ramona Jim','0086054974','SC','Office Hour','Office Hour' UNION ALL

SELECT '20100219','143142','Cb','Valid Card Exit','DM009','Choy Kok Kgoon','0086054972','SD','Office Hour','Office Hour' UNION ALL

SELECT '20100219','153748','Ca','Valid Card Entry','DM002','Liew Wai Foong','0086054966','SC','Office Hour','Office Hour' UNION ALL

SELECT '20100219','153753','Ce','Antipassback Violation (Entry)','DM002','Liew Wai Foong','0086054966','SC','Office Hour','Office Hour' UNION ALL

SELECT '20100219','153814','Cg','Reset Antipassback','DM002','Liew Wai Foong','0086054966','SC','Office Hour','Office Hour' UNION ALL

SELECT '20100219','153827','Df','Pulse Door Open','','','','','','' UNION ALL

SELECT '20100219','153831','Ca','Valid Card Entry','DM002','Liew Wai Foong','0086054966','SC','Office Hour','Office Hour' UNION ALL

SELECT '20100219','153838','Cb','Valid Card Exit','DM002','Liew Wai Foong','0086054966','SC','Office Hour','Office Hour' UNION ALL

SELECT '20100219','153900','Cg','Reset Antipassback','DM002','Liew Wai Foong','0086054966','SC','Office Hour','Office Hour'

Paul

Paul White
All articles available on SQL.kiwi
@SQL_Kiwi

Jeff Moden SSC Guru Points: 1004414 More actions · Answer 4

All we need now are some rules for what to do. For example, what do you want to do with the Antipassback entries?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Sezam Ten Centuries Points: 1240 More actions · Answer 5

Here are rules for calculation:

1.If person come in and go out one time: Calculate time duration in the office.

2.If person come in and go out multiple time: Calculate summery of time duration inside office.

3.If person come in and go out after one day: Calculate time duration in the office. And it will be recorded until midnight for the first day, after midnight time for second day data.

4.If person come in and forgot to punch out and punch in again the working hours won’t be calculated as system can’t know how long person was in office. (Antipas back violation/Entry).

5.If person comes in without punch in and tries to punch out the card in detector: the working hours won’t be calculated as system can’t know how long person was in office (Antipas back violation/Exit).

We can skip all the data except 'Valid Card Entry' and 'Valid Card Exit'.

If you need anything else, let me know.

Thanks to Paul for correcting my data, and thanks to all who is going to help on this.

I really do appreciate it.

kevin_nikolai SSCarpal Tunnel Points: 4343 More actions · Answer 6

Hi dehqon, my problem is almost identical to your's. Hope someone can assist us.

Jeff Moden SSC Guru Points: 1004414 More actions · Answer 7

The durations for the time people spend "In" in the test data are ridiculously low which may indicate a problem with the base system. None the less, this will get you started...

WITH

cteDate AS

( --=== Normalize the date for the upcoming calculations and filter to get only the valid card entry/exit rows

SELECT CAST([Transaction date] + ' ' + STUFF(STUFF(RIGHT('000000'+[Transaction Time],6),5,0,':'),3,0,':') AS DATETIME) AS Date,

[Transactin Code],

[Staff No],

[Staff Name]

FROM dbo.Data_00

WHERE [Transactin Code] IN ('Ca','Cb')

),

cteRowNum AS

( --=== Add the quintessential row number column

SELECT ROW_NUMBER() OVER (PARTITION BY [Staff No] ORDER BY Date) AS RowNum,

Date,

[Transactin Code],

[Staff No],

[Staff Name]

FROM cteDate

) --=== Solve the problem using a self join

SELECT lo.[Staff No],

lo.[Staff Name],

lo.Date AS DateIn,

hi.Date AS DateOut,

DATEDIFF(ss,lo.Date,hi.Date) AS DurationInSeconds

FROM cteRowNum lo

INNER JOIN cteRowNum hi

ON lo.RowNum+1 = hi.RowNum

AND lo.[Staff No] = hi.[Staff No]

AND lo.[Transactin Code] = 'Ca'

AND hi.[Transactin Code] = 'Cb'

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Sezam Ten Centuries Points: 1240 More actions · Answer 8

Thank you very much :-):-)

That's exactly what I'm looking for. Think, the rest I can get in Analysis Services.

Thank you so much...

Jeff Moden SSC Guru Points: 1004414 More actions · Answer 9

You're welcome... the only thing that's left is doing a "SUM" by day and the midnight "split". The SUM can be done easily (sorry, don't know why I didn't include it) but I'm not sure it'll do the midnight "split" for you.

If I get the chance, I'll see what I can do.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Sezam Ten Centuries Points: 1240 More actions · Answer 10

Hi Jeff Moden.

I face some problems while building the cube. Need your help to finish the project. Can we sum total seconds by day, and do splitting by day if possible. If splitting is not possible you can just assign it to DateOut day. I need another column (DateNumber) which contains date from DateOut only in YYYYMMDD fromat.

For me SQL is in learning process, hope can get knowledge soon and help to people like you 🙂

Sezam Ten Centuries Points: 1240 More actions · Answer 11

Hi Jeff Moden.

I face some problems while building the cube. Need your help to finish the project. Can we sum total seconds by day, and do splitting by day if possible. If splitting is not possible you can just assign it to DateOut day. I need another column (DateNumber) which contains date from DateOut only in YYYYMMDD fromat.

For me SQL is in learning process, hope can get knowledge soon and help to people like you 🙂

Sezam Ten Centuries Points: 1240 More actions · Answer 12

Hi Jeff Moden.

I face some problems while building the cube. Need your help to finish the project. Can we sum total seconds by day, and do splitting by day if possible. If splitting is not possible you can just assign it to DateOut day. I need another column (DateNumber) which contains date from DateOut only in YYYYMMDD fromat.

For me SQL is in learning process, hope can get knowledge soon and help to people like you 🙂

P.S: Reposting 3rd time, after waiting after half hour

Paul White SSC Guru Points: 150468 More actions · Answer 13

Dehqon D. (3/15/2010)
Reposting 3rd time, after waiting after half hour[/size]

Why post the same thing three times?

If Jeff chooses to assist you again, it will be at his convenience.

Paul White
All articles available on SQL.kiwi
@SQL_Kiwi

Jeff Moden SSC Guru Points: 1004414 More actions · Answer 14

Dehqon D. (3/15/2010)
For me SQL is in learning process, hope can get knowledge soon and help to people like you 🙂
P.S: Reposting 3rd time, after waiting after half hour

Ummm... maybe I need to make something clear here...

I'm not an employee of SQLServerCentral.com and I don't get paid for helping people here. I do it strictly out of the goodness of my heart. With that in mind, I have to warn you that you've just bitten the hand that feeds you.

You're busting my chops because you had to wait a half hour. Not even considering that I may actually work for a living and that my ethics are better than to cruise a forum when I have work to do for my employer or that your post showed up at about 2 in the morning my time, let's take a look back at this thread, shall we?

Your first post was on 2/25 at 1:57 AM my time. The reason why no one responded to your post was because you offered nearly nothing in the form of anything actually usable to help you. I took pity on you on 2/25 at 8:25 AM which was only a little over 6-1/2 hours after your intitial post and just a couple of hours after I had gotten out of bed. [font="Arial Black"]Your next post was two weeks later[/font] and I responded within 2 hours after getting out of bed.

Two days later and against my better judgement because of your slow response, I gave you the majority of a solution to a problem you obviously weren't capable of solving. Your response came two days after that...

Dehqon D. (3/15/2010)
Thank you very much :-):-)
That's exactly what I'm looking for. Think, the rest I can get in Analysis Services.
Thank you so much...

... which means to me (regardless of your 2 day delay) that your sufficiency has been suffonsified and that anything more that I could add would be superfluous.

And now you treat me like I'm some sort of instant help button that's supposed to jump at your beckon call between 1 and 2 in the morning my time all because you waited two weeks to even start your bloody project? I don't think so...

Good luck with your project.

By the way... how do you like your pork chops cooked? 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)