The following table is the DimWorkItem table, to query the latest work per day for each task
| Id |
Title |
Changeddate |
Person |
Todayhours |
| 1 |
Bank card Withholding Agreement signing |
2015/3/2 16:34:21 |
Tom |
2 |
| 1 |
Bank card Withholding Agreement signing |
2015/3/3 10:14:37 |
Tom |
3 |
| 1 |
Bank card Withholding Agreement signing |
2015/3/3 15:03:56 |
Tom |
2.5 |
| 2 |
Password change |
2015/3/2 09:03:20 |
John doe |
4 |
| 2 |
Password change |
2015/3/3 14:40:51 |
John doe |
2 |
Select T.id,t.title,t.changeddate,t.person,t.todayhours
From
(Select A.id,a.title,a.person,
CONVERT (varchar), a.changeddate, 111) Changeddate,
A.changeddate,
A.todayhours,
--Grouped by task ID and date changed, sorted by date changed, get first row
Row_number () over (partition by Id,convert (varchar (+), a.changeddate, 111) Order BY changeddate Desc) RK
From Tfs_Warehouse.dbo.DimWorkItem a) t
where T.rk = 1
The query results are as follows:
| Id |
Title |
Changeddate |
Person |
Todayhours |
| 1 |
Bank card Withholding Agreement signing |
2015/3/2 16:34:21 |
Tom |
2 |
| 1 |
Bank card Withholding Agreement signing |
2015/3/3 15:03:56 |
Tom |
2.5 |
| 2 |
Password change |
2015/3/2 09:03:20 |
John doe |
4 |
| 2 |
Password change |
2015/3/3 14:40:51 |
John doe |
2 |
Get the first row in a group