Solved

Help with a SQL Query

Posted on 2010-11-25
11
290 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

0
Comment
Question by:Jeff S
  • 4
  • 3
  • 2
  • +2
11 Comments
 
LVL 10

Expert Comment

by:hosneylk
ID: 34215010

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
 
LVL 7

Author Comment

by:Jeff S
ID: 34215018
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
 
LVL 10

Expert Comment

by:hosneylk
ID: 34215028
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
NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

 
LVL 7

Author Comment

by:Jeff S
ID: 34215034
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
 
LVL 10

Assisted Solution

by:hosneylk
hosneylk earned 166 total points
ID: 34215042
nope. i don't get what you want. maybe you could give an example result set?
0
 
LVL 32

Accepted Solution

by:
ewangoya earned 167 total points
ID: 34215058

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
 
LVL 23

Assisted Solution

by:Rajkumar Gs
Rajkumar Gs earned 167 total points
ID: 34215458
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
 
LVL 7

Author Comment

by:Jeff S
ID: 34218072
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
 
LVL 32

Expert Comment

by:awking00
ID: 34218687
select <whatever> from <tablejoin>
where regexp_like(cptcode,'(992[0-1][1-5])(206[05|10])')
0
 
LVL 7

Author Closing Comment

by:Jeff S
ID: 34298254
Thanks  - Split points to be fair.
0
 
LVL 32

Expert Comment

by:awking00
ID: 34301223
Just realized SQL Server uses regexplike (without the underscore) syntax. It might still work.
0

Featured Post

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

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

'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

803 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question