Jeff S
asked on
Help with a SQL Query
I need back the visits where I have a pvp.CPTCode between ('99201' - '99205') or ('99211' - '99215'). After I get these visits, I need the ones that also have either '20605' or '20610' billed. I tried doing it with the - AND ( pvp.CPTCode IN ( '20605', '20610' ) ) but did not get my values back I need. Any suggestions is appreciated.
SELECT pv.patientvisitid,
pv.TicketNumber,
pvp.Code,
ISNULL(CONVERT(VARCHAR(4), ml2.Code), ' ') AS Modifier1,
ISNULL(CONVERT(VARCHAR(4), ml3.Code), ' ') AS Modifier2,
ISNULL(CONVERT(VARCHAR(4), ml4.Code), ' ') AS Modifier3,
ISNULL(CONVERT(VARCHAR(4), ml5.Code), ' ') AS Modifier4
FROM PatientVisit pv
INNER JOIN PatientVisitProcs pvp ON pv.PatientVisitId = pvp.PatientVisitId
LEFT OUTER JOIN MedLists ml2 ON pvp.Modifier1MId = ml2.MedListsId
LEFT OUTER JOIN MedLists ml3 ON pvp.Modifier2MId = ml3.MedListsId
LEFT OUTER JOIN MedLists ml4 ON pvp.Modifier3MId = ml4.MedListsId
LEFT OUTER JOIN MedLists ml5 ON pvp.Modifier4MId = ml5.MedListsId
WHERE pv.BillStatus IN ( 2, 5 ) -- In Progress or Approved Status only
AND ( pvp.CPTCode BETWEEN '99201' AND '99205'
OR pvp.CPTCode BETWEEN '99211' AND '99215'
)
-- AND ( pvp.CPTCode IN ( '20605', '20610' ) )
ORDER BY pv.TicketNumber
ASKER
This gave me back the visits with ('99201' - '99205') or ('99211' - '99215') and those that did not have the codes '20605' or '20610' and those that did. I need only the ones that had a code in ('99201' - '99205') or ('99211' - '99215') with '20605' or '20610'. If the visit did not contain the second set of codes, I don't want that visit result returned to my dataset.
but you're checking for the same column CPTCode. and neither '20605' nor '20610' is in between the ranges ('99201' - '99205') or ('99211' - '99215')
ASKER
So could I do two passes then? I am trying thinking maybe selection of the distinct patientvisitId where the coderange between ('99201' - '99205') or ('99211' - '99215') the INNER JOIN them and ask it to then give me the ones that have the '20605' or '20610'. Then compile them together for my visits with both. Sound right?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Below is actually the query that seems to work for me. I needed two code combinations checked and this seems right. Anyone see any glaring issues?
SET NOCOUNT ON
----- E & M Codes missing Modifier
CREATE TABLE #Bill ( patientvisitid INT )
INSERT #Bill
SELECT DISTINCT
pv.patientvisitid
FROM PatientVisit pv
INNER JOIN PatientVisitProcs pvp ON pv.PatientVisitId = pvp.PatientVisitId
WHERE
(pvp.CPTCode BETWEEN '99201' AND '99205'
OR pvp.CPTCode BETWEEN '99211' AND '99215')
AND --Filter on Modifier
(
pvp.Modifier1MId IS NULL AND
pvp.Modifier2MId IS NULL AND
pvp.Modifier3MId IS NULL AND
pvp.Modifier4MId IS NULL
)
----- Injection Charges
CREATE TABLE #Inj ( patientvisitid INT )
INSERT #Inj
SELECT DISTINCT
pv.patientvisitid
FROM PatientVisit pv
INNER JOIN PatientVisitProcs pvp ON pv.PatientVisitId = pvp.PatientVisitId
WHERE
pvp.CPTCode IN ( '20605', '20610' )
SELECT
pp.PatientId,
dbo.FormatName(pp.Prefix, pp.First, pp.Middle, pp.Last, pp.Suffix) AS PatientName,
pv.TicketNumber,
fac.ListName AS [Facility],
doc.ListName AS [Doctor],
pvp.CPTCode,
ISNULL(CONVERT(varchar(4), ml2.Code), ' ')
+ ' ' + ISNULL(CONVERT(varchar(4), ml3.Code), ' ')
+ ' ' + ISNULL(CONVERT(varchar(4), ml4.Code), ' ')
+ ' ' + ISNULL(CONVERT(varchar(4), ml5.Code), ' ') AS Modifier
FROM
PatientVisit pv
INNER JOIN #Bill b ON pv.PatientVisitId = b.patientvisitid
INNER JOIN #Inj inj ON pv.PatientVisitId = inj.PatientVisitID
INNER JOIN PatientProfile pp ON pv.PatientProfileId = pp.PatientProfileId
INNER JOIN PatientVisitProcs pvp ON pv.PatientVisitId = pvp.PatientVisitId
LEFT OUTER JOIN MedLists ml2 ON pvp.Modifier1MId = ml2.MedListsId
LEFT OUTER JOIN MedLists ml3 ON pvp.Modifier2MId = ml3.MedListsId
LEFT OUTER JOIN MedLists ml4 ON pvp.Modifier3MId = ml4.MedListsId
LEFT OUTER JOIN MedLists ml5 ON pvp.Modifier4MId = ml5.MedListsId
INNER JOIN DoctorFacility fac ON pv.FacilityId = fac.DoctorFacilityId
INNER JOIN DoctorFacility doc ON pv.DoctorId = doc.DoctorFacilityId
WHERE
pv.BillStatus IN ( 2, 5 ) -- 'In Progress'' or 'Approved'' Status only
ORDER BY
pv.TicketNumber,
pvp.ListOrder
DROP TABLE #Bill
DROP TABLE #Inj
select <whatever> from <tablejoin>
where regexp_like(cptcode,'(992[ 0-1][1-5]) (206[05|10 ])')
where regexp_like(cptcode,'(992[
ASKER
Thanks - Split points to be fair.
Just realized SQL Server uses regexplike (without the underscore) syntax. It might still work.
Open in new window