Select First Record of the Same Record ID Within Multiple Joined Queries

Question

I wrote a query with multiple join statements and would like to only select the top 'PT_FIN' where there are multiple of the same PT_FINs. I'm having an issue in how to embed the code that selects only one PT_FIN within the code I have already written.

I found a few links that provide the answer in selecting only the top row but like I said, I'm having issues making these answers work within my code:

Select the first instance of a record

Selecting the first record out of each nested grouped record

USE EMTCQIData

DECLARE @StartDate Date
DECLARE @EndDate Date

Set @StartDate = '03/01/2018'
Set @EndDate = '03/25/2018'


Select ORD.PT_FIN, NOTE.Tracking_GROUP, NOTE.AUTHOR, 
FORMAT(ORD.CHECKIN_DT_TM, 'MM/dd/yyyy') as DOV, ORD.Order_Mnemonic, 
ORD.order_status,

CASE WHEN ORDER_MNEMONIC LIKE '%ketamine%' THEN 'YES' ELSE 'NO' END 
[KETAMINE ORDERED], ORD.DOSE,


CASE
       WHEN NOTE.RESULT LIKE '%99143%' THEN 'YES'
       ELSE 'NO'
END BILLED_SEDATION,


CASE
   WHEN NOTE2.RESULT LIKE '%Sedation%' THEN 'YES' Else 'NO' END 
 POWERNOTE_SEDATION

FROM [ED_Orders_Import_Master] AS ORD

INNER JOIN 

(
Select *
FROM [ED_NOTES_MASTER] AS NOTE
Where RESULT_TITLE_TEXT = 'ED Physician Charges' AND RESULT_DT_TM > 
@StartDate and RESULT_DT_TM < @EndDate

)
as NOTE ON NOTE.PT_FIN = ORD.PT_FIN

INNER JOIN 
(

Select *
FROM [ED_NOTES_MASTER] AS NOTE2
Where NOTE_TYPE like '%PowerNote ED%' AND RESULT_DT_TM > @StartDate and 
RESULT_DT_TM < @EndDate
)
as NOTE2 ON NOTE2.PT_FIN = ORD.PT_FIN
WHERE [Checkin_dt_tm] > @StartDate and [Checkin_dt_tm] < @EndDate AND 
ORDER_MNEMONIC LIKE '%ketamine%' and ORIG_ORD_AS like '%Normal%' and 
ORDER_STATUS like '%complete%'
ORDER by ORD.PT_FIN

I would like the results to look like this:

PT_FIN     Order       Billed Sedation       Power Note Sedation 
1         Ketamine         yes                     Yes
2         Ketamine         yes                     no 
3         Ketamine         yes                     Yes

DB schema and sample input would be helpful – Daniel Brughera Mar 25 '19 at 20:55 — Daniel Brughera, Mar 25 '19 at 20:55

Daniel Brughera · Answer 1 · 2019-03-25T21:14:13.527

The database schema and sample input is missing in the question, but it can be an approach, as you only need to validate the existence of the notes, i think that exists can solve your problem. Apply would help too if you need to get more than one value from the correlated sub-queries

DECLARE @StartDate Date
DECLARE @EndDate Date

Set @StartDate = '03/01/2018'
Set @EndDate = '03/25/2018'


Select ORD.PT_FIN, ORDER_MNEMONIC, 
CASE WHEN EXISTS(SELECT 1 FROM [ED_NOTES_MASTER] WHERE RESULT_TITLE_TEXT = 'ED Physician' AND PT_FIN = ORD.PT_FIN AND RESULT LIKE '%99143%') THEN 'Yes' ELSE 'NO' END AS BILLED_SEDATION,
CASE WHEN EXISTS(SELECT 1 FROM [ED_NOTES_MASTER] WHERE NOTE_TYPE like '%PowerNote ED%' PT_FIN = ORD.PT_FIN AND RESULT LIKE '%Sedation%') THEN 'Yes' ELSE 'NO' END AS POWER_NOTE_SEDATION
FROM [ED_Orders_Import_Master] AS ORD
WHERE [Checkin_dt_tm] > @StartDate and [Checkin_dt_tm] < @EndDate AND 
ORDER_MNEMONIC LIKE '%ketamine%' and ORIG_ORD_AS like '%Normal%' and 
ORDER_STATUS like '%complete%'
ORDER by ORD.PT_FIN

Using apply to get the note only if is the first one, I got your current approach and switched your INNER JOINs into OUTER APPLYs with TOP 1 to accomplish what you are asking for in the title

Select ORD.PT_FIN, NOTE.Tracking_GROUP, NOTE.AUTHOR, 
FORMAT(ORD.CHECKIN_DT_TM, 'MM/dd/yyyy') as DOV, ORD.Order_Mnemonic, 
ORD.order_status,

CASE WHEN ORDER_MNEMONIC LIKE '%ketamine%' THEN 'YES' ELSE 'NO' END 
[KETAMINE ORDERED], ORD.DOSE,


CASE
       WHEN NOTE.RESULT LIKE '%99143%' THEN 'YES'
       ELSE 'NO'
END BILLED_SEDATION,


CASE
   WHEN NOTE2.RESULT LIKE '%Sedation%' THEN 'YES' Else 'NO' END 
 POWERNOTE_SEDATION

FROM [ED_Orders_Import_Master] AS ORD

OUTER APPLY
(
Select TOP 1 *
FROM [ED_NOTES_MASTER] 
Where  PT_FIN = ORD.PT_FIN
ANd RESULT_TITLE_TEXT = 'ED Physician Charges' AND RESULT_DT_TM > 
@StartDate and RESULT_DT_TM < @EndDate
ORDER BY RESULT_DT_TM
)
as NOTE
OUTER APPLY
(
Select TOP 1 *
FROM [ED_NOTES_MASTER]
Where PT_FIN = ORD.PT_FIN NOTE_TYPE like '%PowerNote ED%' AND RESULT_DT_TM > @StartDate and 
RESULT_DT_TM < @EndDate
ORDER BY RESULT_DT_TM
)
WHERE [Checkin_dt_tm] > @StartDate and [Checkin_dt_tm] < @EndDate AND 
ORDER_MNEMONIC LIKE '%ketamine%' and ORIG_ORD_AS like '%Normal%' and 
ORDER_STATUS like '%complete%'
ORDER by ORD.PT_FIN

Select First Record of the Same Record ID Within Multiple Joined Queries

1 Answers1