I am using the following query against a TFS database. The workitem in question goes from Active to other states and back to active. I'd like to use Rank() Over Partition to treat the two sets of "Active" states as separate groups rather than continuing the numbering of the second Active group where it left off with the first Active group.
;with cte as
(
SELECT
dense_rank() over(partition by ID, State order by ID, Rev) as rn
, ID
, Rev
, State
, Reason
, NamePart
--, *
FROM dbo.WorkItemsWere Hist
Left JOIN Constants Cs WITH (nolock)
ON Hist.[Changed by] = Cs.ConstID
WHERE ID = 38728
)
SELECT *
FROM cte
--WHERE rn = 1
ORDER BY ID, Rev
Below is the resultset I get with an additional column of "Desired rn" thrown in to show what I'd like it to be:
Desired rn rn ID Rev State
---------- ----- ----------- ----------- -----------------------------------
1 1 38728 1 Proposed
1 1 38728 2 Active
2 2 38728 3 Active
3 3 38728 4 Active
4 4 38728 5 Active
5 5 38728 6 Active
6 6 38728 7 Active
7 7 38728 8 Active
1 1 38728 9 Dev Complete
1 1 38728 10 Resolved; Queued for Build to Test
2 2 38728 11 Resolved; Queued for Build to Test
3 3 38728 12 Resolved; Queued for Build to Test
1 8 38728 13 Active
2 9 38728 14 Active
1 2 38728 15 Dev Complete
Can this be done by tweaking the syntax?