TSQL Problem -- Cal the actual working hours for a particular users
-
I have a table that show the working log for a particular users. We can see the Date, Time, CardNumber
Name, StaffNumber and TranCode. TranCode indicate in/out for a particular record.
0 is In while 9 is exit.
Sample data as below:
Date Time CardNumber Name StaffNumber TranCode
------------------------------------------------------------------------------------------
2011-01-04 09:41 1000 Ken 001 0
2011-01-04 10:09 1000 Ken 001 9
2011-01-04 10:26 1000 Ken 001 0
2011-01-04 12:10 1000 Ken 001 9
2011-01-04 12:51 1000 Ken 001 0
2011-01-04 19:00 1000 Ken 001 9
2011-01-04 09:40 1001 Jim 002 0
2011-01-04 11:00 1001 Jim 002 9
2011-01-04 12:00 1001 Jim 002 0
2011-01-04 12:52 1001 Jim 002 9
2011-01-04 14:10 1001 Jim 002 0
2011-01-04 18:30 1001 Jim 002 9
2011-01-05 09:00 1000 Ken 001 0
2011-01-05 19:00 1000 Ken 001 9
my challenge is to come out a report showing the actual working hours for a particular users.
Expected output as below:
Date StarTime EndTime CardNumber Name StaffNumber Duration
----------------------------------------------------------------------------------------------------
2011-01-04 09:41 10:09 1000 Ken 001 0:28
2011-01-04 10:26 12:10 1000 Ken 001 1:44
2011-01-04 12:51 19:00 1000 Ken 001 6:09
2011-01-04 09:40 11:00 1001 Jim 002 1:20
2011-01-04 12:00 12:52 1001 Jim 002 0:52
2011-01-04 14:10 18:30 1001 Jim 002 4:20
2011-01-05 09:00 19:00 1000 Ken 001 10:00
DDL Script as below
declare @logrec table
(Date datetime,
[Time] datetime,
CardNumber varchar(10),
[Name] varchar(50),
StaffNumber varchar(10),
TranCode varchar(2))
insert into @logrec
select '2011-01-04', '09:41','1000','Ken', '001','0' union
select '2011-01-04', '10:09','1000','Ken','001','9'union
select '2011-01-04', '10:26','1000','Ken','001','0'union
select '2011-01-04', '12:10','1000','Ken','001','9'union
select '2011-01-04', '12:10','1000','Ken','001','9'union
select '2011-01-04', '12:51','1000','Ken','001','0'union
select '2011-01-04', '19:00','1000','Ken','001','9'union
select '2011-01-04', '09:40','1001','Jim','002','0'union
select '2011-01-04', '11:00','1001','Jim','002','9'union
select '2011-01-04', '12:00','1001','Jim','002','0'union
select '2011-01-04', '12:52','1001','Jim','002','9'union
select '2011-01-04', '14:10','1001','Jim','002','0'union
select '2011-01-04', '18:30','1001','Jim','002','9'union
select '2011-01-05', '09:00','1000','Ken','001','0'union
select '2011-01-05', '19:00','1000','Ken','001','9'
I have tried many ways but still unable to find out the solutions. Appreciate if you can give me a hand..
if you have a difficulty to view the sample data, please download the attachments.
Thanks
-
SELECT CONVERT(CHAR(10),s.Date,120) AS Date,
CONVERT(CHAR(5),s.Time,108) AS StarTime,
CONVERT(CHAR(5),e.Time,108) AS EndTime,
s.CardNumber,
s.Name,
s.StaffNumber,
CAST(DATEDIFF(minute,s.Time,e.Time)/60 AS VARCHAR(10)) + ':' +
RIGHT(CAST(100+DATEDIFF(minute,s.Time,e.Time)%60 AS VARCHAR(3)),2) AS Duration
FROM @logrec s
CROSS APPLY (SELECT TOP 1 e.Time FROM @logrec e
WHERE e.TranCode='9'
AND e.Date=s.Date
AND e.CardNumber=s.CardNumber
AND e.Name=s.Name
AND e.Time>s.Time
ORDER BY e.Time) e
WHERE s.TranCode='0'
ORDER BY s.Date,s.CardNumber,s.Time;
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537
-
Thanks for the solutions .. now i having a new issues, i try to summarizing data with rollup in order to get the total amount of durations for each employees, but received an error message because sum function only works with numeric data types. Anyone can give a hand ??
-
sqlbaby2 (1/17/2011)
Thanks for the solutions .. now i having a new issues, i try to summarizing data with rollup in order to get the total amount of durations for each employees, but received an error message because sum function only works with numeric data types. Anyone can give a hand ??Sure... let's really get off on the right foot, though. Please read the first link in my signature line below. If you post data that way, it will really help us help you a lot.,
Second, post the code you've tried. It will first let us see what you're doing (right or wrong) and it will also convince us that you've given it the ol' college try. 🙂
--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) -
I already found the solutions ...thanks
-
sqlbaby2 (1/17/2011)
I already found the solutions ...thanksIn that case, would you mind sharing them? Two way street here... 😉
And... you should still read the article I pointed you to. It'll help you get better, tested solutions faster no matter which forum you post on. :w00t:
--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) -
SELECT A.Date,A.CardNumber,A.Name,A.StaffNumber,
CASE WHEN LEN(CAST(SUM(A.DurNum)/60 AS VARCHAR(10))) = 1
THEN '0'+LTRIM(STR(CAST(SUM(A.DurNum)/60 AS VARCHAR(10)),3)) +':'+
CAST(SUM(A.DurNum)%60 AS VARCHAR(3))
ELSE LTRIM(STR(CAST(SUM(A.DurNum)/60 AS VARCHAR(10)),3)) +':'+
RIGHT(100+CAST(SUM(A.DurNum)%60 AS VARCHAR(3)),2)
END N
FROM (
SELECT CONVERT(CHAR(10),s.Date,120) AS Date,
CONVERT(CHAR(5),s.Time,108) AS StarTime,
CONVERT(CHAR(5),e.Time,108) AS EndTime,
s.CardNumber,
s.Name,
s.StaffNumber,
CASE WHEN LEN(CAST(DATEDIFF(minute,s.Time,e.Time)/60 AS VARCHAR(10))) = 1
THEN '0'+LTRIM(STR(CAST(DATEDIFF(minute,s.Time,e.Time)/60 AS VARCHAR(10)),2))+':'+
RIGHT(100+CAST(DATEDIFF(minute,s.Time,e.Time)%60 AS VARCHAR(3)),2)
ELSE LTRIM(STR(CAST(DATEDIFF(minute,s.Time,e.Time)/60 AS VARCHAR(10)),2))+':'+
RIGHT(100+CAST(DATEDIFF(minute,s.Time,e.Time)%60 AS VARCHAR(3)),2)
END Duration,
CAST(DATEDIFF(minute,s.Time,e.Time)/60 AS INT)*60+
CAST(RIGHT(CAST(DATEDIFF(minute,s.Time,e.Time)%60 AS VARCHAR(3)),2) AS INT) AS DurNum
FROM @logrec s
CROSS APPLY (SELECT TOP 1 e.Time FROM @logrec e
WHERE e.TranCode='9'
AND e.Date=s.Date
AND e.CardNumber=s.CardNumber
AND e.Name=s.Name
AND e.Time>s.Time
ORDER BY e.Time) e
WHERE s.TranCode='0') A
GROUP BY A.Date,A.CardNumber,A.Name,A.StaffNumber
ORDER BY a.Date,a.CardNumber;
-
Thanks for posting the code.
--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 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply