Having posted last night about row numbers and ranking, I realised in the shower this morning that there's actually an easy way to get the first and last rows per group, just by using ROW_NUMBER. The trick is to PARTITION, meaning that the row number is reset per partition. So I could do:
SELECT Employee, TransactionTime,
ROW_NUMBER() OVER (PARTITION BY Employee, dbo.DateOnly(TransactionTime) ORDER BY TransactionTime) AS Row1, ROW_NUMBER() OVER (PARTITION BY Employee, dbo.DateOnly(TransactionTime) ORDER BY TransactionTime DESC) AS Row2
FROM ActualData
The result set is:
| Employee |
TransactionTime |
Row1 |
Row2 |
| Dave |
01 Jan 08 09:00 |
1 |
4 |
| Dave |
01 Jan 08 13:00 |
2 |
3 |
| Dave |
01 Jan 08 14:00 |
3 |
2 |
| Dave |
01 Jan 08 18:00 |
4 |
1 |
| Dave |
02 Jan 08 09:00 |
1 |
2 |
| Dave |
02 Jan 08 17:30 |
2 |
1 |
| Alex |
01 Jan 08 09:00 |
1 |
2 |
| Alex |
01 Jan 08 17:30 |
2 |
1 |
So now the first row per group is where Row1 = 1 and the last row per group is where Row2 = 1. Easy.