Help with SQL Syntax for a Command 1

[steve053]

Crystal 10
Microsoft SQL 2000 database

Here is my SQL Query

 SELECT "PATIENT"."PAT_ID" as "PATIENT_PAT_ID", "AH_ASTHMA_PTS"."PAT_ID" as "AH_ASTHMA_PAT_ID", "PATIENT"."EPI_ID_TYPE_0", "PATIENT"."PAT_NAME", "PAT_ENC_CURR_MEDS"."LINE", "PAT_ENC_CURR_MEDS"."PAT_ENC_CSN_ID", "PAT_ENC_CURR_MEDS"."CONTACT_DATE", "PAT_ENC_CURR_MEDS"."IS_ACTIVE_YN", "RX_MED_TWO"."MEDICATION_NAME", "RX_MED_TWO"."MEDICATION_ID", "ORDER_MED"."START_DATE", "ORDER_MED"."END_DATE", "ORDER_MED"."ORDERING_DATE", "ORDER_MED"."ORDER_MED_ID", "PAT_ENC_CURR_MEDS"."CURRENT_MED_ID"

 FROM   ((("Clarity"."dbo"."AH_ASTHMA_PTS" "AH_ASTHMA_PTS" LEFT OUTER JOIN "Clarity"."dbo"."PATIENT" "PATIENT" ON "AH_ASTHMA_PTS"."PAT_ID"="PATIENT"."PAT_ID") LEFT OUTER JOIN "Clarity"."dbo"."PAT_ENC_CURR_MEDS" "PAT_ENC_CURR_MEDS" ON "PATIENT"."PAT_ID"="PAT_ENC_CURR_MEDS"."PAT_ID") LEFT OUTER JOIN "Clarity"."dbo"."RX_MED_TWO" "RX_MED_TWO" ON "PAT_ENC_CURR_MEDS"."MEDICATION_ID"="RX_MED_TWO"."MEDICATION_ID") LEFT OUTER JOIN "Clarity"."dbo"."ORDER_MED" "ORDER_MED" ON "PAT_ENC_CURR_MEDS"."CURRENT_MED_ID"="ORDER_MED"."ORDER_MED_ID"

 WHERE  "PATIENT"."PAT_ID" LIKE '21003360' AND "PAT_ENC_CURR_MEDS"."CONTACT_DATE" <= {?Contact_Date}

 ORDER BY "AH_ASTHMA_PAT_ID","PAT_ENC_CURR_MEDS"."CURRENT_MED_ID", "PAT_ENC_CURR_MEDS"."CONTACT_DATE"

I will remove the "PATIENT"."PAT_ID" LIKE '21003360' from the WHERE clause after my initial testing.

Here is sample output from the above:

PAT_ID   ORDER_MED_ID  CONTACT_DATE  IS_ACTIVE_YN
1231     99213         6/1/2004       Y
1231     99213         8/1/2004       N
1231     99213         1/15/2005      Y
1231     99902         6/15/2004      Y
1231     99902         6/30/2004      N

Here is what I would like to see:

PAT_ID   ORDER_MED_ID  CONTACT_DATE  IS_ACTIVE_YN
1231     99213         1/15/2005      Y
1231     99902         6/30/2004      N

Is there a way that I can filter the "PAT_ENC_CURR_MEDS"."CURRENT_MED_ID" to show only the most recent date for each ORDER_MED_ID?

I know that I can create groups in Crystal and use formulas to get to the most recent information for each line, but would prefer to have SQL do as much as possible.

I have little experience with SQL, so I do appreciate your assistance.

Steve

 

[synapsevampire]

Why are you returning all of these columns and only displaying 4?

PAT_ID ORDER_MED_ID CONTACT_DATE IS_ACTIVE_YN

Try:

SELECT
"PATIENT"."PAT_ID" as "PATIENT_PAT_ID",
"ORDER_MED"."ORDER_MED_ID",
maximum("PAT_ENC_CURR_MEDS"."CONTACT_DATE")
FROM ((("Clarity"."dbo"."AH_ASTHMA_PTS" "AH_ASTHMA_PTS" LEFT OUTER JOIN "Clarity"."dbo"."PATIENT" "PATIENT" ON "AH_ASTHMA_PTS"."PAT_ID"="PATIENT"."PAT_ID") LEFT OUTER JOIN "Clarity"."dbo"."PAT_ENC_CURR_MEDS" "PAT_ENC_CURR_MEDS" ON "PATIENT"."PAT_ID"="PAT_ENC_CURR_MEDS"."PAT_ID") LEFT OUTER JOIN "Clarity"."dbo"."RX_MED_TWO" "RX_MED_TWO" ON "PAT_ENC_CURR_MEDS"."MEDICATION_ID"="RX_MED_TWO"."MEDICATION_ID") LEFT OUTER JOIN "Clarity"."dbo"."ORDER_MED" "ORDER_MED" ON "PAT_ENC_CURR_MEDS"."CURRENT_MED_ID"="ORDER_MED"."ORDER_MED_ID"
WHERE "PATIENT"."PAT_ID" LIKE '21003360' AND "PAT_ENC_CURR_MEDS"."CONTACT_DATE" <= {?Contact_Date}
GROUP BY
"PATIENT"."PAT_ID" as "PATIENT_PAT_ID",
"ORDER_MED"."ORDER_MED_ID")

Now join this to the PAT_ENC_CURR_MEDS on the id and the contact date to get the "PAT_ENC_CURR_MEDS"."IS_ACTIVE_YN" using a derived table join, or create a View to do it.

-k

 

[synapsevampire]

btw, this is really a SQL Server query question, I suggest that you use a SQL Server forum. You can do this within Crystal as well using grouping, but I suggest doing the right thing and performing the processing on the database server.

-k

 

[satinsilhouette]

You could have used a maximum on the contact date as well. That would have only grabbed the latest date.

And Synapsevampire is correct, we solved this one by getting max date and then grouping on that, but it is better for the server to handle the amount of data you are going to try to plow through.

 

[steve053]

synapsevampire-

Thank you for taking the time to answer my question.

To answer your question:
Why are you returning all of these columns and only displaying 4?

I am using all of the colums, just didn't have enough room to post them all and should have explaind that. I just wanted to show what I considered relevant to the post as the other columns return the same value within the order_med_id grouping.

I will give your solution a try and post follow-up SQL quesitons in the proper SQL Server forum.

Thanks again for your help.

Steve

 

[synapsevampire]

The same theory applieas, use the SQL I generated to join to your original SQL, each as derived tables, and use the IDs and the maximum(date) as the joins.

-k