IF OBJECT_ID('tempdb..#data') IS NOT NULL DROP TABLE #data
GO
CREATE TABLE #data (
	[FormID] INT
	, [EditTime] DATETIME
	, [FinallyApproved] BIT
	)
INSERT #data ([FormID], [EditTime], [FinallyApproved])
VALUES
	  (1, '2012-01-01T00:01:00', 0)
	, (1, '2012-01-01T04:21:00', 1) -- YES
	, (2, '2012-01-02T04:00:00', 0)
	, (2, '2012-01-02T08:00:00', 1) -- YES
	, (2, '2012-01-02T12:00:00', 1)
	, (3, '2012-01-03T00:00:00', 1)
	, (3, '2012-01-03T01:00:00', 0)
	, (3, '2012-01-03T02:00:00', 1) -- YES
	, (3, '2012-01-03T03:00:00', 1)
	
CREATE INDEX IX_FormID_EditTime_Approved ON #data ([FinallyApproved], [FormID], [EditTime])

SELECT * FROM #data

SELECT
	[FormID]
	, MIN([EditTime])
FROM
	(
	SELECT
		[FormID]
		, [EditTime]
	FROM
		#data AS d
	WHERE
		[FinallyApproved] = 1
		AND NOT EXISTS (
			SELECT 1
			FROM #data AS d2
			WHERE d2.[FormID] = d.[FormID]
			  AND d2.[FinallyApproved] = 0
			  AND d2.[EditTime] > d.[EditTime]
			  )
	)
	AS samp
GROUP BY
	samp.[FormID]

quote:

---

Originally posted by IanMoyes

Apologies for the terrible subject heading - and I guess this might be a problem that has been solved before. I have a system used to view, edit and finally approve forms. Every time a form is edited, a new row is created in a SQL table, which has the formID, the datetime of the edit, and whether the form has been finally approved.

Assume I've sorted this dataset by FormID and then ascending EditTime (so it's chronologically in order as you go down.)

FormID EditTime FinallyApproved
-----------------------------------
1 [Some Datetime] NO
1 [Some Datetime] YES *
2 [Some Datetime] NO
2 [Some Datetime] YES *
2 [Some Datetime] YES
3 [Some Datetime] YES
3 [Some Datetime] NO
3 [Some Datetime] YES *
3 [Some Datetime] YES

I want to know, for each FormID, the earliest EditTime where the form was finally approved - where FinallyApproved = YES. I've used a * to show the row I want to know in that sample set.

Easy enough for forms 1 and 2. But in form 3, a mistake was made. Someone approved the form, changed their mind later, and only on the third edit was the form really finished. That's why I want the second ocurrance of YES for that particular form.

So, I'd like to select the earliest YES value for each form that with no NO values occurring later. And that's where I'm stumped!

If anyone's got some tips as to how to go about this, I'd be very grateful. Thanks everyone!

---

SELECT t.FormID,MIN(t.EditTime) AS EarliestApprovedDate
FROM table t
OUTER APPLY (SELECT COUNT(*) AS cnt
             FROM table
             WHERE FormID = t.FormID
             AND FinallyApproved='No'
             AND editTime > t.EditTime)t1
WHERE COALESCE(t1.cnt,0)=0
AND t.FinallyApproved='Yes'
GROUP BY t.FormID