Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Single value MIN and MAX dates from multiple rows Expand / Collapse
Author
Message
Posted Friday, March 08, 2013 3:34 PM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, March 24, 2014 9:05 PM
Points: 387, Visits: 418
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]




  Post Attachments 
Book2.xlsx (14 views, 9.99 KB)
Post #1428800
Posted Friday, March 08, 2013 3:45 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 9:47 PM
Points: 22,472, Visits: 30,138
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]






Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1428802
Posted Sunday, March 10, 2013 9:29 PM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Sunday, April 13, 2014 9:51 PM
Points: 4,570, Visits: 8,315
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.
Post #1429056
Posted Monday, March 11, 2013 7:39 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, March 24, 2014 9:05 PM
Points: 387, Visits: 418
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

Post #1429264
Posted Monday, March 11, 2013 7:52 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 9:47 PM
Points: 22,472, Visits: 30,138
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.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1429275
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse