[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 300
  • Last Modified:

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

Open in new window

0
Jeff S
Asked:
Jeff S
  • 4
  • 3
  • 2
  • +2
3 Solutions
 
hosneylkCommented:

AND ( pvp.CPTCode BETWEEN '99201' AND '99205'
      OR pvp.CPTCode BETWEEN '99211' AND '99215'
      OR pvp.CPTCode IN ( '20605', '20610' )
     )

Open in new window

0
 
Jeff SAuthor Commented:
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.
0
 
hosneylkCommented:
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
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Jeff SAuthor Commented:
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?
0
 
hosneylkCommented:
nope. i don't get what you want. maybe you could give an example result set?
0
 
Ephraim WangoyaCommented:

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
 
0
 
Rajkumar GsSoftware EngineerCommented:
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 

Open in new window


Full Query should be ike this
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   
        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

Open in new window


Raj
0
 
Jeff SAuthor Commented:
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

Open in new window

0
 
awking00Commented:
select <whatever> from <tablejoin>
where regexp_like(cptcode,'(992[0-1][1-5])(206[05|10])')
0
 
Jeff SAuthor Commented:
Thanks  - Split points to be fair.
0
 
awking00Commented:
Just realized SQL Server uses regexplike (without the underscore) syntax. It might still work.
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 4
  • 3
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now