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 Modifier4FROM 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.MedListsIdWHERE 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
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')
0
The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.
One of a set of tools we're offering as a way of saying thank you for being a part of the community.
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?
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' )
OR ( pvp.CPTCode IN ( '20605', '20610' )))
ORDER BY pv.TicketNumber
If you meant to retrieve PatientVisits with Status 'Progress' (2) & 'Approved' (5) that comes with CPTCode BETWEEN '99201' AND '99205' OR BETWEEN '99211' AND '99215'
Plus
Those with Status 'Billed' I don't know the status for Billed. Assume it is 6 in this query) with CPTCode - '20605' OR '20610', then try this WHERE condition
WHERE AND ((pvp.CPTCode BETWEEN '99201' AND '99205' OR pvp.CPTCode BETWEEN '99211' AND '99215') AND pv.BillStatus IN ( 2, 5 ) ) -- In Progress or Approved Status only OR (pvp.CPTCode IN ( '20605', '20610') AND pv.BillStatus = 6 ) -- Billed
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 Modifier4FROM 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.MedListsIdWHERE AND ((pvp.CPTCode BETWEEN '99201' AND '99205' OR pvp.CPTCode BETWEEN '99211' AND '99215') AND pv.BillStatus IN ( 2, 5 ) ) -- In Progress or Approved Status only OR (pvp.CPTCode IN ( '20605', '20610') AND pv.BillStatus = 6 ) -- Billed ORDER BY pv.TicketNumber
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 ModifierCREATE 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 ModifierFROM 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.ListOrderDROP TABLE #BillDROP TABLE #Inj
Open in new window