Display time clock data in daily columns.
-
Here's a simplified version of my problem.
I have time clock information for two employees. When I run my query (below) I get multiple rows for each employee. What I would like to have is just one row for each employee -- UNLESS THAT EMPLOYEE HAS CLOCKED IN & OUT MULTIPLE TIMES ON THE SAME DAY. For example, you'll see below see that employee 2 has put in two shifts on day 6 -- I need to continue to show that as a separate row. Can someone help?
DECLARE @TimeClock TABLE
(
EmpNum INT,
ClockInDate DATETIME,
ClockInTime DECIMAL(6,2),
ClockOutTime DECIMAL(6,2)
)
INSERT INTO @TimeClock VALUES(1,'2008-10-20',7.25,14.42)
INSERT INTO @TimeClock VALUES(1,'2008-10-21',6.25,13.42)
INSERT INTO @TimeClock VALUES(1,'2008-10-22',7.00,12.02)
INSERT INTO @TimeClock VALUES(1,'2008-10-23',7.25,14.42)
INSERT INTO @TimeClock VALUES(1,'2008-10-24',7.25,11.42)
INSERT INTO @TimeClock VALUES(1,'2008-10-25',7.45,14.00)
INSERT INTO @TimeClock VALUES(1,'2008-10-26',7.03,12.33)
INSERT INTO @TimeClock VALUES(2,'2008-10-20',8.05,15.42)
INSERT INTO @TimeClock VALUES(2,'2008-10-21',6.13,12.35)
INSERT INTO @TimeClock VALUES(2,'2008-10-22',7.04,14.02)
INSERT INTO @TimeClock VALUES(2,'2008-10-23',8.05,15.15)
INSERT INTO @TimeClock VALUES(2,'2008-10-24',6.22,11.58)
INSERT INTO @TimeClock VALUES(2,'2008-10-25',5.45,11.00)
INSERT INTO @TimeClock VALUES(2,'2008-10-25',12.03,16.33)
-- Make Monday the start of the week.
SET DATEFIRST 1;
-- Declare variables
DECLARE @StartDate DATETIME
,@EndDate DATETIME
,@Day1Int AS TINYINT
,@Day2Int AS TINYINT
,@Day3Int AS TINYINT
,@Day4Int AS TINYINT
,@Day5Int AS TINYINT
,@Day6Int AS TINYINT
,@Day7Int AS TINYINT
SET @EndDate = '2008-10-26'
SET @StartDate = DATEADD(week,-1, @EndDate)
-- To populate columns based on day-of-week of payroll date on labor record
SET @Day1Int = DATEPART(dw,DATEADD(d,-6,@EndDate))
SET @Day2Int = DATEPART(dw,DATEADD(d,-5,@EndDate))
SET @Day3Int = DATEPART(dw,DATEADD(d,-4,@EndDate))
SET @Day4Int = DATEPART(dw,DATEADD(d,-3,@EndDate))
SET @Day5Int = DATEPART(dw,DATEADD(d,-2,@EndDate))
SET @Day6Int = DATEPART(dw,DATEADD(d,-1,@EndDate))
SET @Day7Int = DATEPART(dw,@EndDate)
SELECT EmpNum
,CASE WHEN DATEPART (dw,ClockInDate)= @Day1Int THEN ClockIntime END AS InDay1
,CASE WHEN DATEPART (dw,ClockInDate)= @Day1Int THEN ClockOutTime END AS OutDay1
,CASE WHEN DATEPART (dw,ClockInDate)= @Day2Int THEN ClockIntime END AS InDay2
,CASE WHEN DATEPART (dw,ClockInDate)= @Day2Int THEN ClockOutTime END AS OutDay2
,CASE WHEN DATEPART (dw,ClockInDate)= @Day3Int THEN ClockIntime END AS InDay3
,CASE WHEN DATEPART (dw,ClockInDate)= @Day3Int THEN ClockOutTime END AS OutDay3
,CASE WHEN DATEPART (dw,ClockInDate)= @Day4Int THEN ClockIntime END AS InDay4
,CASE WHEN DATEPART (dw,ClockInDate)= @Day4Int THEN ClockOutTime END AS OutDay4
,CASE WHEN DATEPART (dw,ClockInDate)= @Day5Int THEN ClockIntime END AS InDay5
,CASE WHEN DATEPART (dw,ClockInDate)= @Day5Int THEN ClockOutTime END AS OutDay5
,CASE WHEN DATEPART (dw,ClockInDate)= @Day6Int THEN ClockIntime END AS InDay6
,CASE WHEN DATEPART (dw,ClockInDate)= @Day6Int THEN ClockOutTime END AS OutDay6
,CASE WHEN DATEPART (dw,ClockInDate)= @Day7Int THEN ClockIntime END AS InDay7
,CASE WHEN DATEPART (dw,ClockInDate)= @Day7Int THEN ClockOutTime END AS OutDay7
FROM @TimeClock
WHERE ClockInDate BETWEEN @StartDate AND @EndDate
ORDER BY ClockInDate,EmpNum
TIA
Ahmet
-
Thanks for the DDL and sample data, especially in Table Variable format, it makes things so much easier. How about something like this?
[font="Courier New"]SELECT EmpNum
,SUM(CASE WHEN DATEPART (dw,ClockInDate)= @Day1Int THEN ClockIntime END) AS InDay1
,SUM(CASE WHEN DATEPART (dw,ClockInDate)= @Day1Int THEN ClockOutTime END) AS OutDay1
,SUM(CASE WHEN DATEPART (dw,ClockInDate)= @Day2Int THEN ClockIntime END) AS InDay2
,SUM(CASE WHEN DATEPART (dw,ClockInDate)= @Day2Int THEN ClockOutTime END) AS OutDay2
,SUM(CASE WHEN DATEPART (dw,ClockInDate)= @Day3Int THEN ClockIntime END) AS InDay3
,SUM(CASE WHEN DATEPART (dw,ClockInDate)= @Day3Int THEN ClockOutTime END) AS OutDay3
,SUM(CASE WHEN DATEPART (dw,ClockInDate)= @Day4Int THEN ClockIntime END) AS InDay4
,SUM(CASE WHEN DATEPART (dw,ClockInDate)= @Day4Int THEN ClockOutTime END) AS OutDay4
,SUM(CASE WHEN DATEPART (dw,ClockInDate)= @Day5Int THEN ClockIntime END) AS InDay5
,SUM(CASE WHEN DATEPART (dw,ClockInDate)= @Day5Int THEN ClockOutTime END) AS OutDay5
,SUM(CASE WHEN DATEPART (dw,ClockInDate)= @Day6Int THEN ClockIntime END) AS InDay6
,SUM(CASE WHEN DATEPART (dw,ClockInDate)= @Day6Int THEN ClockOutTime END) AS OutDay6
,SUM(CASE WHEN DATEPART (dw,ClockInDate)= @Day7Int THEN ClockIntime END) AS InDay7
,SUM(CASE WHEN DATEPART (dw,ClockInDate)= @Day7Int THEN ClockOutTime END) AS OutDay7
FROM @TimeClock
WHERE ClockInDate BETWEEN @StartDate AND @EndDate
GROUP BY EmpNum
ORDER BY EmpNum[/font]
-
Thanks for your suggestion, Seth.
It works beautifully unless an employee has clocked in & out more than once on a given day -- I realized last night, while I was tossing and turning in bed, that I hadn't made this clear in my original post so I updated it this morning.
Any other ideas... anybody?
Ahmet
-
Yeah, I had a feeling that was going to happen. We always has the same annoyance with our time clock stuff. Worse, ours were constantly missing login/logout data. IE. We'd have a login without a log out and then another log in the next day. Do you have this happen as well?
-
Our guys are getting pretty good about clocking out. Occasionally we have employees doing double shifts -- that's what causes the multiple rows for the same day/same employee.
-
Can you show what your desired output would be if they had 2+ clockin/outs for the same day? You have columns designed for a 1 to 1 relationship of clock in and outs per day per employee. If you want to display the same information and retain only 1 line per employee, somethings gotta give! 😛
-
WITH CTE AS (
SELECT EmpNum,ClockInDate,ClockInTime,ClockOutTime,
ROW_NUMBER() OVER(PARTITION BY EmpNum,DATEPART (dw,ClockInDate) ORDER BY ClockInDate) AS rn
FROM @TimeClock)
SELECT EmpNum
,MAX(CASE WHEN DATEPART (dw,ClockInDate)= @Day1Int THEN ClockIntime END) AS InDay1
,MAX(CASE WHEN DATEPART (dw,ClockInDate)= @Day1Int THEN ClockOutTime END) AS OutDay1
,MAX(CASE WHEN DATEPART (dw,ClockInDate)= @Day2Int THEN ClockIntime END) AS InDay2
,MAX(CASE WHEN DATEPART (dw,ClockInDate)= @Day2Int THEN ClockOutTime END) AS OutDay2
,MAX(CASE WHEN DATEPART (dw,ClockInDate)= @Day3Int THEN ClockIntime END) AS InDay3
,MAX(CASE WHEN DATEPART (dw,ClockInDate)= @Day3Int THEN ClockOutTime END) AS OutDay3
,MAX(CASE WHEN DATEPART (dw,ClockInDate)= @Day4Int THEN ClockIntime END) AS InDay4
,MAX(CASE WHEN DATEPART (dw,ClockInDate)= @Day4Int THEN ClockOutTime END) AS OutDay4
,MAX(CASE WHEN DATEPART (dw,ClockInDate)= @Day5Int THEN ClockIntime END) AS InDay5
,MAX(CASE WHEN DATEPART (dw,ClockInDate)= @Day5Int THEN ClockOutTime END) AS OutDay5
,MAX(CASE WHEN DATEPART (dw,ClockInDate)= @Day6Int THEN ClockIntime END) AS InDay6
,MAX(CASE WHEN DATEPART (dw,ClockInDate)= @Day6Int THEN ClockOutTime END) AS OutDay6
,MAX(CASE WHEN DATEPART (dw,ClockInDate)= @Day7Int THEN ClockIntime END) AS InDay7
,MAX(CASE WHEN DATEPART (dw,ClockInDate)= @Day7Int THEN ClockOutTime END) AS OutDay7
FROM CTE
WHERE ClockInDate BETWEEN @StartDate AND @EndDate
GROUP BY EmpNum,rn
ORDER BY EmpNum,rn
____________________________________________________
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
-
Here is how I'd like to see my output (Sorry I couldn't line up the columns better, but I hope you get the idea). In this sample data, employee 2 was the only one that clocked in twice on the same day. Notice the extra row for Employee 2 on day 6:
Emp# InDay1 OutDay1 InDay2 OutDay2 … InDay6 OutDay6 InDay7 OutDay7
1 7.25 14.42 6.25 13.42 … 7.45 14.00 7.03 12.33
2 8.05 15.42 6.13 12.35 … 5.45 11.00 NULL NULL
2 NULL NULL NULL NULL … 12.03 16.33 NULL NULL
What do you think?
Ahmet
-
Mark,
That looks exactly like what I need!
Thanks,
Ahmet
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply