This post has the code and an Excel Workbook with results and expected results (Red text)

My query works for the desired results, except for the last two columns (they were added at the 11th hour).What I need is a query to capture is a single MIN and MAX date based from the values found for each PATIENT_ID. If you look at the excel sheet, you can see there are 7 records. I need MIN date from DATE_WRITTEN and MAX date from EXPIRES_ON. It's OK that they repeat, because the query is moving into Crystal Reports and Crystal is forgiving with duplicates.

Since DATE was not recognized (Type DATE is not a defined system type.), I changed it to DATETIME and used CONVERT.

CONVERT(VARCHAR(12),CAST(MIN([Rxo].[DATE_WRITTEN]) OVER ( PARTITION BY [Rxo].[PATIENT_ID] ) AS DATETIME),110) AS MinStart, CONVERT(VARCHAR(12),CAST(MAX([Rxo].[EXPIRES]) OVER ( PARTITION BY [Rxo].[PATIENT_ID] ) AS DATETIME),110) AS MaxStart

SQL_Enthusiast (3/11/2013)Thank you Lynn, that was exactly what I needed!

Since DATE was not recognized (Type DATE is not a defined system type.), I changed it to DATETIME and used CONVERT.

CONVERT(VARCHAR(12),CAST(MIN([Rxo].[DATE_WRITTEN]) OVER ( PARTITION BY [Rxo].[PATIENT_ID] ) AS DATETIME),110) AS MinStart, CONVERT(VARCHAR(12),CAST(MAX([Rxo].[EXPIRES]) OVER ( PARTITION BY [Rxo].[PATIENT_ID] ) AS DATETIME),110) AS MaxStart

You must be using SQL Server 2005. This was posted in a SQL Server 2008 forum, so I gave you a SQL Server 2008 answer.