in
Questions and answers to issues related to Microsoft: Windows, Applications, Development, Hardware, Server, Internet Protocols, Database, Exchange . » Applications » Visual Interdev » Query to Group Time Punch Data by Date

Query to Group Time Punch Data by Date

I've been playing with this for a few weeks but haven't solved the main problem yet.  When I query the time punch table I get a column with punch dates and a column with associated punch times but what I want to display is a single row for each date with that days punches in separate columns.  See image for example.

 
PunchTimes.gif (18 KB) (File Type Details) 
Column data vs. table layout
309950
 


As seen in the image the number of punches per day can be different which might pose an issue.   Here is the query I've built so far.  The dates in the WHERE clause are from the previous weeks Sunday to the current Saturday.

1:
2:
3:
4:

SELECT convert(char(11),dat) AS PunchDate, convert(char(8),dateadd(n,punch,0),108) as [PunchTime]
FROM TiaBdata...jtm4012
WHERE badge = 69975 AND dat BETWEEN DateAdd(dd, ((DateDiff(dd, '20000102', GetDate()) -8) / 7)*7, '20000102') AND DateAdd(dd, ((DateDiff(dd, '20000101', GetDate()) +6) / 7)*7, '20000101')
ORDER BY dat, punch


I was looking at PIVOT but can't figure out an aggregate function that would apply.

Thanks for your help,
David
Movie Stars

Solution: Query to Group Time Punch Data by Date

For example:
1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:

SELECT	Date, 
		PunchTime1, 
		PunchTime2, 
		PunchTime3, 
		PunchTime4, 
		PunchTime5, 
		PunchTime6,
		PunchTime7,
		PunchTime8
FROM (
SELECT Date, Time, 'PunchTime' + CAST(ROW_NUMBER() OVER (PARTITION BY Date ORDER BY Time) as varchar(10)) RowID
FROM YouTable
) L
PIVOT (MAX(Time) FOR RowID IN ([PunchTime1], [PunchTime2], [PunchTime3], [PunchTime4], [PunchTime5], [PunchTime6], [PunchTime7], [PunchTime8])) P
Toggle Highlighting
Open in New Window
Select All