quote:

---

Originally posted by helixpoint

Exactly. The release date would be it, but some will have null if they have not been released they would have an active Case_StatusCd
of 80040. I need to account if there are no active ids and just take the last DOC_ReleaseDt

Dave
Helixpoint Web Development
http://www.helixpoint.com

---

IF
	(SELECT COUNT(*)
	FROM YourTable
	WHERE Case_StatusCd = '80040')
>= 1
	BEGIN
		SELECT TOP 1 Case_Id
		FROM YourTable
		WHERE Case_StatusCd = '80040'
		ORDER BY DOC_ReleaseDt DESC
	END
	ELSE
		SELECT TOP 1 Case_Id
		FROM YourTable
		WHERE Case_StatusCd = '80041'
		ORDER BY DOC_ReleaseDt DESC;

SELECT
	(SELECT TOP 1 Case_Id
	FROM YourTable
	WHERE Case_Id = '80040'
	ORDER BY DOC_ReleaseDt DESC) AS 'Latest Active',
	(SELECT TOP 1 Case_Id
	FROM YourTable
	WHERE Case_Id = '80041'
	ORDER BY DOC_ReleaseDt DESC) AS 'Latest Inactive';