[RESOLVED] sql server aggregate query

[MMock]

Here are some rows from my table. You can see they are all for GroupID 57. I am interested in the StatusID = 5 and 6 records. I have to produce a report saying how many days the item spent in between status 5 and 6. So the first pair of 5/6 records (RowID 122 and 674) don't count because there's another newer pair. I want to build a table that has a row with three columns something like 57 - 11:02:17 - 11:02:59 (I left off the mm/dd/yy for the sake of brevity).

RowId GroupID StatusID CreateDate
120 57 1 2011-09-02 06:29:32.290
121 57 3 2011-09-02 06:36:32.437
122 57 5 2011-09-02 06:38:37.633
674 57 6 2011-09-20 11:02:01.830
675 57 5 2011-09-20 11:02:17.340
676 57 6 2011-09-20 11:02:59.697



However, it's possible there could be another 5 record even newer, such as this:
RowId GroupID StatusID CreateDate
120 57 1 2011-09-02 06:29:32.290
121 57 3 2011-09-02 06:36:32.437
122 57 5 2011-09-02 06:38:37.633
674 57 6 2011-09-20 11:02:01.830
675 57 5 2011-09-20 11:02:17.340
676 57 6 2011-09-20 11:02:59.697
677 57 5 2011-09-20 11:05:36.990

I don't want any row at all in that case, because I can't calculate the time since it's still status 5.

I started to write a query that looks like this, but you can see where I am having a problem:

	select 
		egs.groupid,
		MAX(egs.groupstatusid) 
	from GroupStatus egs 
	
	inner join 
	(
		select 
			groupid,
			MAX(groupstatusid) as x
		from GroupStatus egs 
		where statusid=6 
		group by Groupid
	)EGS1
	
	on egs.GroupID = EGS1.GroupID
	where egs.StatusID = 5
	-- you need something in here so you get 675 and not 677
	group by egs.GroupID

What do I need to do here?