So, assume this:
select * from meddenials
DenialId DenialTypeId DenialCode DenialDescrip
1 1 1420 Not Medically Necessary
2 1 1500 Other transportation available
3 1 1600 No Eligibility Coverage on date of service
4 2 100 Missing Signature
5 2 200 Incomplete Information
6 2 300 Mileage over estimate threshold
And
select * from meddenialtypes
DenialTypeId DenialTypeDescrip
1 Trip Denial
2 Claim Denial
And
select * from medreimbursementdenials where bookingid = 44479
BookingId DenialTypeId DenialId
44479 1 1
44479 2 5
So, for an Encounter file, we wanted to get the top denial reason, so I tried something like this:
SELECT DenialReason.*,md.*
FROM Booking b
LEFT OUTER JOIN ( SELECT TOP 1 DenialId, BookingId FROM MedReimbursementDenials where bookingid = b.bookingid )
AS DenialReason
LEFT OUTER JOIN MedDenials md on md.DenialId = DenialReason.DenialId
where b.bookingid = 44479
but that causes an error:
Msg 156, Level 15, State 1, Line 6
Incorrect syntax near the keyword 'where'.
So I tried moving the where clause out of the "sub table":
SELECT DenialReason.*,md.*
FROM Booking b
LEFT OUTER JOIN ( SELECT TOP 1 DenialId, BookingId FROM MedReimbursementDenials )
AS DenialReason on DenialReason.BookingId = b.BookingId
LEFT OUTER JOIN MedDenials md on md.DenialId = DenialReason.DenialId
where b.bookingid = 44479
Which is ok syntactically, but doesn't return the results the way we want:
DenialId BookingId DenialId DenialTypeId DenialCode DenialDescrip
NULL NULL NULL NULL NULL NULL
So, I googled around and found this page:
which introduced a key word I wasn't familiar with "OUTER APPLY"
SELECT DenialReason.*,md.*
FROM Booking b
OUTER APPLY ( SELECT TOP 1 DenialId, BookingId FROM MedReimbursementDenials where bookingid = b.bookingid )
AS DenialReason
LEFT OUTER JOIN MedDenials md on md.DenialId = DenialReason.DenialId
where b.bookingid = 44479
does the trick:
DenialId BookingId DenialId DenialTypeId DenialCode DenialDescrip
1 44479 1 1 1420 Not Medically Necessary
