InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comWed, 22 May 2013 04:38:59 GMT20http://www.sqlservercentral.com/Forums/Topic1428800-392-1.aspx[quote][b]SQL_Enthusiast (3/11/2013)[/b][hr]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. [code="sql"]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[/code][/quote]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.Mon, 11 Mar 2013 07:52:04 GMTLynn Pettishttp://www.sqlservercentral.com/Forums/Topic1428800-392-1.aspxThank 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. [code="sql"]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[/code]Mon, 11 Mar 2013 07:39:47 GMTSQL_Enthusiasthttp://www.sqlservercentral.com/Forums/Topic1428800-392-1.aspxReplace "Not working" part with this:[code="sql"] INNER JOIN (SELECT PATIENT_ID, MIN(Date_Written) MIN_START, MAX(EXPIRES_ON) MAX_STARTFROM [dbo].[PT_CASE] GROUP BY PATIENT_ID ) AS Ptc ON [Pat].[PATIENT_ID] = [Ptc].[PATIENT_ID][/code]and simply add MIN_START, MAX_START to your SELECT.Sun, 10 Mar 2013 21:29:29 GMTSergiyhttp://www.sqlservercentral.com/Forums/Topic1428800-392-1.aspxNo promises as you did not provide the DDL for the tables or sample data:[code="sql"]SELECT DISTINCT [Rxo].[SYS_ID] , [Rxo].[PATIENT_ID] , [Rxo].[DESCRIPTION] , [Rxo].[RX_NUMBER] , [JRxf].[MAX_FILL_NUM] , [Rxo].[PT_CASE_PHYSICIAN_SYS_ID] , CONVERT(VARCHAR(12) , [Rxo].[DATE_WRITTEN] , 110) AS DATE_WRITTEN , CONVERT(VARCHAR(12) , [Rxo].[EXPIRES] , 110) AS EXPIRES_ON , CONVERT(VARCHAR(12) , [Rx3].[MIN_START] , 110) AS MIN_START , cast(min([Rxo].[DATE_WRITTEN]) over (partition by [Rxo].[PATIENT_ID]) as DATE) as MinStart , cast(max([Rxo].[DATE_EXPIRES]) over (partition by [Rxo].[PATIENT_ID]) as DATE) as MaxStart FROM [dbo].[RX_ORDER] AS Rxo LEFT JOIN ( SELECT MAX([Rxf].[REFILL_NUMBER]) AS [MAX_FILL_NUM] , [Rxf].[RX_ORDER_SYS_ID] FROM [dbo].[RX_FILL] AS Rxf GROUP BY [Rxf].[RX_ORDER_SYS_ID] ) JRxf ON [Rxo].[SYS_ID] = [JRxf].[RX_ORDER_SYS_ID] LEFT JOIN [dbo].[PATIENT] AS Pat ON [Rxo].[PATIENT_ID] = [Pat].[PATIENT_ID] LEFT JOIN [dbo].[PT_CASE] AS Ptc ON [Pat].[PATIENT_ID] = [Ptc].[PATIENT_ID] WHERE [Ptc].[SITE_ID] = '0001' AND [Ptc].[CASE_STATUS_CODE] = 'A' AND [Rxo].[STATUS] = 'A' AND [Ptc].[PATIENT_ID] = 2000000000 ORDER BY [Rxo].[PATIENT_ID] , [Rxo].[RX_NUMBER][/code]Fri, 08 Mar 2013 15:45:12 GMTLynn Pettishttp://www.sqlservercentral.com/Forums/Topic1428800-392-1.aspxThis 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. Here is the query[code="sql"]SELECT DISTINCT [Rxo].[SYS_ID] , [Rxo].[PATIENT_ID] , [Rxo].[DESCRIPTION] , [Rxo].[RX_NUMBER] , [JRxf].[MAX_FILL_NUM] , [Rxo].[PT_CASE_PHYSICIAN_SYS_ID] , CONVERT(VARCHAR(12) , [Rxo].[DATE_WRITTEN] , 110) AS DATE_WRITTEN , CONVERT(VARCHAR(12) , [Rxo].[EXPIRES] , 110) AS EXPIRES_ON , CONVERT(VARCHAR(12) , [Rx3].[MIN_START] , 110) AS MIN_START FROM [dbo].[RX_ORDER] AS Rxo LEFT JOIN ( SELECT MAX([Rxf].[REFILL_NUMBER]) AS [MAX_FILL_NUM] , [Rxf].[RX_ORDER_SYS_ID] FROM [dbo].[RX_FILL] AS Rxf GROUP BY [Rxf].[RX_ORDER_SYS_ID] ) JRxf ON [Rxo].[SYS_ID] = [JRxf].[RX_ORDER_SYS_ID] LEFT JOIN [dbo].[PATIENT] AS Pat ON [Rxo].[PATIENT_ID] = [Pat].[PATIENT_ID] LEFT JOIN [dbo].[PT_CASE] AS Ptc ON [Pat].[PATIENT_ID] = [Ptc].[PATIENT_ID] /*Not working*/ LEFT JOIN ( SELECT DISTINCT MIN([Rxo2].[DATE_WRITTEN]) AS MIN_START , [Rxo2].[PATIENT_ID] , [Rxo2].[RX_NUMBER] FROM [dbo].[RX_ORDER] AS Rxo2 GROUP BY [Rxo2].[PATIENT_ID] , [Rxo2].[RX_NUMBER] ) Rx3 ON [Rxo].[PATIENT_ID] = [Rx3].[PATIENT_ID] AND [Rxo].[RX_NUMBER] = [Rx3].[RX_NUMBER] WHERE [Ptc].[SITE_ID] = '0001' AND [Ptc].[CASE_STATUS_CODE] = 'A' AND [Rxo].[STATUS] = 'A' AND [Ptc].[PATIENT_ID] = 2000000000 ORDER BY [Rxo].[PATIENT_ID] , [Rxo].[RX_NUMBER][/code]Fri, 08 Mar 2013 15:34:28 GMTSQL_Enthusiast