Solved

Help with a SQL Query

Posted on 2010-11-25
11
285 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
 
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
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 
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 31

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 31

Expert Comment

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

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Suggested Solutions

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

762 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now