March 8, 2013 at 3:34 pm
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.
Here is the query
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]
March 8, 2013 at 3:45 pm
No promises as you did not provide the DDL for the tables or sample data:
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]
March 10, 2013 at 9:29 pm
Replace "Not working" part with this:
INNER JOIN (
SELECT PATIENT_ID, MIN(Date_Written) MIN_START, MAX(EXPIRES_ON) MAX_START
FROM [dbo].[PT_CASE]
GROUP BY PATIENT_ID ) AS Ptc ON [Pat].[PATIENT_ID] = [Ptc].[PATIENT_ID]
and simply add MIN_START, MAX_START to your SELECT.
_____________
Code for TallyGenerator
March 11, 2013 at 7:39 am
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
March 11, 2013 at 7:52 am
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.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply