Show time card data in daily columns.

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Show time card data in daily columns.

Author

Topic

ahmeterispaha
Starting Member

19 Posts

Posted - 2008-10-28 : 08:28:05

Here's a simplified version of my problem.

I have time card 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 those as multiple rows. Can someone help?


DECLARE @TimeCard TABLE
        (
        EmpNum INT,
        ClockInDate DATETIME,
                ClockInTime DECIMAL(6,2),
                ClockOutTime DECIMAL(6,2)
		)        

 INSERT INTO @TimeCard VALUES(1,'2008-10-20',7.25,14.42)
 INSERT INTO @TimeCard VALUES(1,'2008-10-21',6.25,13.42)
 INSERT INTO @TimeCard VALUES(1,'2008-10-22',7.00,12.02)
 INSERT INTO @TimeCard VALUES(1,'2008-10-23',7.25,14.42)
 INSERT INTO @TimeCard VALUES(1,'2008-10-24',7.25,11.42)
 INSERT INTO @TimeCard VALUES(1,'2008-10-25',7.45,14.00)
 INSERT INTO @TimeCard VALUES(1,'2008-10-26',7.03,12.33)
 INSERT INTO @TimeCard VALUES(2,'2008-10-20',8.05,15.42)
 INSERT INTO @TimeCard VALUES(2,'2008-10-21',6.13,12.35)
 INSERT INTO @TimeCard VALUES(2,'2008-10-22',7.04,14.02)
 INSERT INTO @TimeCard VALUES(2,'2008-10-23',8.05,15.15)
 INSERT INTO @TimeCard VALUES(2,'2008-10-24',6.22,11.58)
 INSERT INTO @TimeCard VALUES(2,'2008-10-25',5.45,11.00)
 INSERT INTO @TimeCard 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 @TimeCard
  WHERE ClockInDate BETWEEN @StartDate AND @EndDate
  ORDER BY ClockInDate,EmpNum

TIA
Ahmet

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-28 : 10:06:06

make it like this

SELECT EmpNum
                ,MAX(CASE WHEN DATEPART (dw,ClockInDate)= @Day1Int THEN ClockIntime ELSE NULL END) AS InDay1
                ,MAX(CASE WHEN DATEPART (dw,ClockInDate)= @Day1Int THEN ClockOutTime ELSE NULL END) AS OutDay1
                ,MAX(CASE WHEN DATEPART (dw,ClockInDate)= @Day2Int THEN ClockIntime ELSE NULL END) AS InDay2
                ,MAX(CASE WHEN DATEPART (dw,ClockInDate)= @Day2Int THEN ClockOutTime ELSE NULL END) AS OutDay2
                ,MAX(CASE WHEN DATEPART (dw,ClockInDate)= @Day3Int THEN ClockIntime ELSE NULL END) AS InDay3
                ,MAX(CASE WHEN DATEPART (dw,ClockInDate)= @Day3Int THEN ClockOutTime ELSE NULL END) AS OutDay3
                ,MAX(CASE WHEN DATEPART (dw,ClockInDate)= @Day4Int THEN ClockIntime ELSE NULL END) AS InDay4
                ,MAX(CASE WHEN DATEPART (dw,ClockInDate)= @Day4Int THEN ClockOutTime ELSE NULL END) AS OutDay4
                ,MAX(CASE WHEN DATEPART (dw,ClockInDate)= @Day5Int THEN ClockIntime ELSE NULL END) AS InDay5
                ,MAX(CASE WHEN DATEPART (dw,ClockInDate)= @Day5Int THEN ClockOutTime ELSE NULL END) AS OutDay5
                ,MAX(CASE WHEN DATEPART (dw,ClockInDate)= @Day6Int THEN ClockIntime ELSE NULL END) AS InDay6
                ,MAX(CASE WHEN DATEPART (dw,ClockInDate)= @Day6Int THEN ClockOutTime ELSE NULL END) AS OutDay6
                ,MAX(CASE WHEN DATEPART (dw,ClockInDate)= @Day7Int THEN ClockIntime ELSE NULL END) AS InDay7
                ,MAX(CASE WHEN DATEPART (dw,ClockInDate)= @Day7Int THEN ClockOutTime ELSE NULL END) AS OutDay7
   FROM @TimeCard
  WHERE ClockInDate BETWEEN @StartDate AND @EndDate
GROUP BY EmpNum,DATEADD(dd,DATEDIFF(dd,0,ClockInDate),0)
  ORDER BY DATEADD(dd,DATEDIFF(dd,0,ClockInDate),0),EmpNum

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-10-28 : 10:26:44

quote:

Originally posted by visakh16

SELECT EmpNum
                ,MAX(CASE WHEN DATEPART (dw,ClockInDate)= @Day1Int THEN ClockIntime ELSE NULL END) AS InDay1
                ,MAX(CASE WHEN DATEPART (dw,ClockInDate)= @Day1Int THEN ClockOutTime ELSE NULL END) AS OutDay1
                ,MAX(CASE WHEN DATEPART (dw,ClockInDate)= @Day2Int THEN ClockIntime ELSE NULL END) AS InDay2
                ,MAX(CASE WHEN DATEPART (dw,ClockInDate)= @Day2Int THEN ClockOutTime ELSE NULL END) AS OutDay2
                ,MAX(CASE WHEN DATEPART (dw,ClockInDate)= @Day3Int THEN ClockIntime ELSE NULL END) AS InDay3
                ,MAX(CASE WHEN DATEPART (dw,ClockInDate)= @Day3Int THEN ClockOutTime ELSE NULL END) AS OutDay3
                ,MAX(CASE WHEN DATEPART (dw,ClockInDate)= @Day4Int THEN ClockIntime ELSE NULL END) AS InDay4
                ,MAX(CASE WHEN DATEPART (dw,ClockInDate)= @Day4Int THEN ClockOutTime ELSE NULL END) AS OutDay4
                ,MAX(CASE WHEN DATEPART (dw,ClockInDate)= @Day5Int THEN ClockIntime ELSE NULL END) AS InDay5
                ,MAX(CASE WHEN DATEPART (dw,ClockInDate)= @Day5Int THEN ClockOutTime ELSE NULL END) AS OutDay5
                ,MAX(CASE WHEN DATEPART (dw,ClockInDate)= @Day6Int THEN ClockIntime ELSE NULL END) AS InDay6
                ,MAX(CASE WHEN DATEPART (dw,ClockInDate)= @Day6Int THEN ClockOutTime ELSE NULL END) AS OutDay6
                ,MAX(CASE WHEN DATEPART (dw,ClockInDate)= @Day7Int THEN ClockIntime ELSE NULL END) AS InDay7
                ,MAX(CASE WHEN DATEPART (dw,ClockInDate)= @Day7Int THEN ClockOutTime ELSE NULL END) AS OutDay7
   FROM @TimeCard
  WHERE ClockInDate BETWEEN @StartDate AND @EndDate
GROUP BY EmpNum,DATEADD(dd,DATEDIFF(dd,0,ClockInDate),0)
  ORDER BY DATEADD(dd,DATEDIFF(dd,0,ClockInDate),0),EmpNum

No difference from OP query.

Have a look at this.

DECLARE	@Sample TABLE
	(
		empNum INT,
		clockInDate DATETIME,
		clockInTime DECIMAL(6, 2),
		clockOutTime DECIMAL(6, 2)
	)        

INSERT	@Sample
SELECT	1, '2008-10-20',  7.25, 14.42 UNION ALL
SELECT	1, '2008-10-21',  6.25, 13.42 UNION ALL
SELECT	1, '2008-10-22',  7.00, 12.02 UNION ALL
SELECT	1, '2008-10-23',  7.25, 14.42 UNION ALL
SELECT	1, '2008-10-24',  7.25, 11.42 UNION ALL
SELECT	1, '2008-10-25',  7.45, 14.00 UNION ALL
SELECT	1, '2008-10-26',  7.03, 12.33 UNION ALL
SELECT	2, '2008-10-20',  8.05, 15.42 UNION ALL
SELECT	2, '2008-10-21',  6.13, 12.35 UNION ALL
SELECT	2, '2008-10-22',  7.04, 14.02 UNION ALL
SELECT	2, '2008-10-23',  8.05, 15.15 UNION ALL
SELECT	2, '2008-10-24',  6.22, 11.58 UNION ALL
SELECT	2, '2008-10-25',  5.45, 11.00 UNION ALL
SELECT	2, '2008-10-25', 12.03, 16.33

DECLARE	@Date DATETIME

SET	@Date = '20081023'

SET	@Date = DATEADD(DAY, DATEDIFF(DAY, '19000101', @Date) / 7 * 7, '19000101')

SELECT		empNum,
		MAX(CASE WHEN ClockInDate = DATEADD(DAY, 0, @Date) THEN ClockInTime ELSE NULL END) AS inDay1,
		MAX(CASE WHEN ClockInDate = DATEADD(DAY, 0, @Date) THEN ClockOutTime ELSE NULL END) AS outDay1,
		MAX(CASE WHEN ClockInDate = DATEADD(DAY, 1, @Date) THEN ClockInTime ELSE NULL END) AS inDay2,
		MAX(CASE WHEN ClockInDate = DATEADD(DAY, 1, @Date) THEN ClockOutTime ELSE NULL END) AS outDay2,
		MAX(CASE WHEN ClockInDate = DATEADD(DAY, 2, @Date) THEN ClockInTime ELSE NULL END) AS inDay3,
		MAX(CASE WHEN ClockInDate = DATEADD(DAY, 2, @Date) THEN ClockOutTime ELSE NULL END) AS outDay3,
		MAX(CASE WHEN ClockInDate = DATEADD(DAY, 3, @Date) THEN ClockInTime ELSE NULL END) AS inDay4,
		MAX(CASE WHEN ClockInDate = DATEADD(DAY, 3, @Date) THEN ClockOutTime ELSE NULL END) AS outDay4,
		MAX(CASE WHEN ClockInDate = DATEADD(DAY, 4, @Date) THEN ClockInTime ELSE NULL END) AS inDay5,
		MAX(CASE WHEN ClockInDate = DATEADD(DAY, 4, @Date) THEN ClockOutTime ELSE NULL END) AS outDay5,
		MAX(CASE WHEN ClockInDate = DATEADD(DAY, 5, @Date) THEN ClockInTime ELSE NULL END) AS inDay6,
		MAX(CASE WHEN ClockInDate = DATEADD(DAY, 5, @Date) THEN ClockOutTime ELSE NULL END) AS outDay6,
		MAX(CASE WHEN ClockInDate = DATEADD(DAY, 6, @Date) THEN ClockInTime ELSE NULL END) AS inDay7,
		MAX(CASE WHEN ClockInDate = DATEADD(DAY, 6, @Date) THEN ClockOutTime ELSE NULL END) AS outDay7
FROM		(
			SELECT	empNum,
				clockInDate,
				clockInTime,
				clockOutTime,
				ROW_NUMBER() OVER (PARTITION BY empNum, clockInDate ORDER BY clockInTime) AS recID
			FROM	@Sample
			WHERE	clockInDate >= @Date
				AND clockInDate <= DATEADD(DAY, 6, @date)
		) AS s
GROUP BY	empNum,
		recID
ORDER BY	empNum

E 12°55'05.63"
N 56°04'39.26"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-10-28 : 10:32:15

Output from my suggestion is this

empNum	inDay1	outDay1	inDay2	outDay2	inDay3	outDay3	inDay4	outDay4	inDay5	outDay5	inDay6	outDay6	inDay7	outDay7
1	7.25	14.42	6.25	13.42	7.00	12.02	7.25	14.42	7.25	11.42	7.45	14.00	7.03	12.33
2	8.05	15.42	6.13	12.35	7.04	14.02	8.05	15.15	6.22	11.58	5.45	11.00	NULL	NULL
2	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	12.03	16.33	NULL	NULL

And my suggestion is safe from SET DATEFIRST setting too.

E 12°55'05.63"
N 56°04'39.26"

ahmeterispaha
Starting Member

19 Posts

Posted - 2008-10-28 : 10:42:03

Great suggestions!

Thank you Peso and visakh16.

Ahmet

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources