Calculating total working hours exclude launch, smoking...and etc times
-
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
-
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) -
Hi again, here is a code for my Data
--===== Create the test table withCREATE 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...
-
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
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi -
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) -
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.
-
Hi dehqon, my problem is almost identical to your's. Hope someone can assist us.
-
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...
WITHcteDate 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) -
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...
-
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) -
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 🙂
-
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 🙂
-
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
-
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
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi -
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)
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply