Solved

Help with a SQL Query

Posted on 2010-11-25

3 solutions

Medium Priority

297 Views

Last Modified: 2012-05-10

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

Comment

Question by:Jeff S

[X]

Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

Help others & share knowledge
Earn cash & points
Learn & ask questions

Join The Community

11 Comments

LVL 10

Expert Comment

by:hosneylk

ID: 342150102010-11-25

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

Open in new window

LVL 7

Author Comment

by:Jeff S

ID: 342150182010-11-25

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.

LVL 10

Expert Comment

by:hosneylk

ID: 342150282010-11-25

but you're checking for the same column CPTCode. and neither '20605' nor '20610' is in between the ranges ('99201' - '99205') or ('99211' - '99215')

LVL 7

Author Comment

by:Jeff S

ID: 342150342010-11-25

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?

LVL 10

Assisted Solution

by:hosneylk

hosneylk earned 664 total points

ID: 342150422010-11-25

nope. i don't get what you want. maybe you could give an example result set?

LVL 32

Accepted Solution

by:Ephraim Wangoya

Ephraim Wangoya earned 668 total points

ID: 342150582010-11-25

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

LVL 23

Assisted Solution

by:Rajkumar Gs

Rajkumar Gs earned 668 total points

ID: 342154582010-11-25

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

LVL 7

Author Comment

by:Jeff S

ID: 342180722010-11-26

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

LVL 32

Expert Comment

by:awking00

ID: 342186872010-11-26

select <whatever> from <tablejoin>
where regexp_like(cptcode,'(992[0-1][1-5])(206[05|10])')

LVL 7

Author Closing Comment

by:Jeff S

ID: 342982542010-12-07

Thanks - Split points to be fair.

LVL 32

Expert Comment

by:awking00

ID: 343012232010-12-08

Just realized SQL Server uses regexplike (without the underscore) syntax. It might still work.

Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

SQL Server database migration - The Backup & Restore method

Article by: Vitor

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.

Tableau - How to Compute and Filter Prior Business Day

Article by: Brian

This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.

Network monitoring: how to automate fixing common network or server issues?

Video by: AdRem

Michael from AdRem Software outlines event notifications and Automatic Corrective Actions in network monitoring. Automatic Corrective Actions are scripts, which can automatically run upon discovery of a certain undesirable condition in your network.…

Why Use Three Nodes in a Database Cluster?

Video by: Percona

In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…