When I run the below query, I only get back line 1 from the above-mentioned query and this is 100% accurate. What I need help with is in my IF block I have commented out below. My client has asked to return the other Procedures with a toggle control (at her discretion) that would return the other codes from that patient visit. I need help with the logic to bring back the other procedures from the PatientVisit. If I have not explained well enough, let me know and I can elaborate more. Thanks kindly in advance.
FROM cusFinancialAggregates cfa
INNER JOIN PatientVisit pv ON cfa.PatientVisitId = pv.PatientVisitId
LEFT JOIN PatientVisitProcs pvp ON cfa.PatientVisitProcsId = pvp.PatientVisitProcsId
INNER JOIN CusActionType_VR pmt ON cfa.ActionTypeMId = pmt.ActionTypeId AND pmt.ActionName NOT LIKE '%Transfer%'
WHERE
cfa.TransActionType = 'Pmts'
AND cfa.InsuranceAmount + cfa.PatientAmount <> 0
AND pmt.ActionName NOT LIKE '%Transfer%'
AND @DATETYPE = 'Batch Entry' AND cfa.EventDate >= ISNULL(@STARTDATE,'1/1/1900') AND cfa.EventDate < DATEADD ( D , 1 , ISNULL(@ENDDATE,'1/1/3000'))
AND cfa.PatientVisitId = 222728
/*
IF @CPT = 1
BEGIN
---- Grab the other Procedure that was NOT found in the Insert into #Results
ELSE
---- Do nothing and keep as it is
END
WHERE ...
AND (pvp.CPTCode = @inputCPTCode OR @inputCPTCode IS NULL)
If the user passes in a CPT Code - then it will only display that CPT code. If the user does not pass in a code - then it will show all. You can use a default value instead of NULL - depends on how you prompt the user. For example, use -1 for ALL...
WHERE ...
AND (pvp.CPTCode = @inputCPTCode OR @inputCPTCode = -1)