Results 1 to 2 of 2
  1. #1
    Join Date
    Feb 2012
    Posts
    2

    Question Unanswered: SQL Query Assistance - Filter for Specific

    What I am hoping to accomplish in this query is the following:

    1. To find a list of patients that have seen a specific MD (thus the insert of patientprofileId's into #Bill).
    2. If that patient has only seen this one specific MD, I don't want them in this report. I need a list of patients that have seen the specific doctor I choose and if they saw another MD, I want them returned in this report. If they have only seen this one MD exclusively, I want those patients removed.

    Any help is deeply appreciated. Please message me if I did not make something clear.

    Code:
    SET NOCOUNT ON 
    
    DECLARE 
    	@startdate datetime,
        	@enddate datetime
    
    SET @startdate = ISNULL('01/01/2007','1/1/1900') 
    SET @enddate = ISNULL('01/31/2012','1/1/3000')   
    
    CREATE TABLE #Bill ( PatientProfileId INT ) 
    
    INSERT  INTO #Bill
            SELECT
                v.PatientProfileID
            FROM
                (
    	-- patients seeing this doctor
                  SELECT DISTINCT
                    pv.PatientProfileID
                  FROM
                    PatientVisit pv
                  WHERE
                    pv.DoctorID = 6800 ) v
        
    SELECT
        	pp.PatientProfileId ,
        	pp.PatientId ,
        	ISNULL(pp.[Last] , '') AS [Patient Last Name] ,
        	ISNULL(pp.[First] , '') AS [Patient First Name] ,
        	ISNULL(pp.Middle , '') AS [Patient Middle Initial] ,
        	ISNULL(pp.Suffix , '') AS [Suffix] ,
        	ISNULL(pp.Address1 , '') AS [Address1] ,
        	ISNULL(pp.Address2 , '') AS [Address2] ,
        	ISNULL(pp.City , '') AS [City] ,
        	ISNULL(pp.[State] , '') AS [State] ,
        	ISNULL(pp.Zip , '') AS [Zip] ,
        	ISNULL(CONVERT(VARCHAR(20) , pp.birthdate , 101) , '') AS [DOB] ,
        	ISNULL(SUBSTRING(pp.SSN , 1 , 3) + '-' + SUBSTRING(pp.SSN , 4 , 2) + '-' + SUBSTRING(pp.SSN , 6 , 4) , '') AS [SSN] ,
        	doc.ListName AS [Visit Doctor] ,
        	ISNULL(pvd.Code , '') AS Diag1 ,
        	ISNULL(pvd1.Code , '') AS Diag2 ,
        	ISNULL(pvd2.Code , '') AS Diag3 ,
        	ISNULL(pvd3.Code , '') AS Diag4 ,
        	pvp.DateofServiceFrom ,
        	( SELECT TOP 1
        	    visit
        	  FROM
        	    patientvisit pv
        	  WHERE
        	    visit >= ISNULL(NULL , '1/1/1900')
        	    AND visit < DATEADD(d , 1 , ISNULL(NULL , '1/1/3000'))
        	    AND pp.patientprofileid = pv.PatientProfileID
        	    AND DATEDIFF(day , GETDATE() , visit) <= 0
        	  ORDER BY
        	    visit DESC ) AS [Last Visit Date] ,
        	(SELECT TOP 1
    		ISNULL(pc.[First] , '') + ' ' + ISNULL(pc.Middle , '') + ' ' + ISNULL(pc.[Last] , '')
    		FROM
    			PatientRelationship pr
    			INNER JOIN PatientContacts pc ON pr.RelatedPartyId = pc.PatientContactsId
    		WHERE
    			pr.PatientProfileId = pv.PatientProfileId AND pr.Type = '5'
    		ORDER BY
    			pc.Created DESC ) AS ContactsInfo,
    		ISNULL(refdr.First,'') AS [Ref MD First],
    		ISNULL(refdr.Last,'') AS [Ref MD Last],
    		ISNULL(refdr.Suffix,'') AS [Ref MD Suffix],
    		ISNULL(refdr.Address1,'') AS [Ref MD Address1],
    		ISNULL(refdr.Address2, '') AS [Ref MD Address2],
    		ISNULL(refdr.City,'') AS [Ref MD City],
    		ISNULL(refdr.State,'') AS [Ref MD State],
    		ISNULL(refdr.Zip,'') AS [Ref MD Zip]
    INTO
        #Temp
    
    FROM
        	PatientVisit pv
        	INNER JOIN #Bill b ON pv.PatientProfileId = b.PatientProfileId
        	INNER JOIN DoctorFacility doc ON pv.DoctorId = doc.DoctorFacilityId
    		LEFT JOIN DoctorFacility refdr on pv.ReferringDoctorID = refdr.doctorfacilityID
        	INNER JOIN PatientProfile pp ON pv.PatientProfileId = pp.PatientProfileId
        	INNER JOIN PatientVisitProcs pvp ON pv.PatientVisitId = pvp.PatientVisitId
        	LEFT OUTER JOIN PatientVisitDiags pvd ON pv.PatientVisitId = pvd.PatientVisitId AND pvp.PatientVisitDiags1 = pvd.ListOrder
        	LEFT OUTER JOIN PatientVisitDiags pvd1 ON pv.PatientVisitId = pvd1.PatientVisitId AND pvp.PatientVisitDiags2 = pvd1.ListOrder
        	LEFT OUTER JOIN PatientVisitDiags pvd2 ON pv.PatientVisitId = pvd2.PatientVisitId AND pvp.PatientVisitDiags3 = pvd2.ListOrder
        	LEFT OUTER JOIN PatientVisitDiags pvd3 ON pv.PatientVisitId = pvd3.PatientVisitId AND pvp.PatientVisitDiags4 = pvd3.ListOrder
    
    WHERE
        	ISNULL(pvp.Voided , 0) = 0 -- Filter out voided visits  
    		AND pvp.DateOfServiceFrom >= @startdate AND pvp.DateOfServiceFrom < @enddate+1
    
    ORDER BY
        	PatientProfileId
        	
        	
    ;WITH CTC AS 
    (
    SELECT
                v.PatientProfileID
            FROM
                (
    	-- patients seeing this doctor
                  SELECT DISTINCT
                    pv.PatientProfileID
                  FROM
                    PatientVisit pv
                  WHERE
                    pv.DoctorID = 6800 ) v
                LEFT OUTER JOIN (
    	-- patients seeing other doctors
                                  SELECT DISTINCT
                                    pv.PatientProfileID
                                  FROM
                                    PatientVisit pv
                                  WHERE
                                    pv.DoctorID != 6800 ) nv ON nv.PatientProfileID = v.PatientProfileID
            WHERE
                nv.PatientProfileID IS NULL
                )   	
        
    SELECT
        	*
    FROM
        	( SELECT
        	    * ,
        	    CONVERT(VARCHAR(30) , DateofServiceFrom , 101) AS DateOnlyAsText ,
        	    row_number() OVER ( PARTITION BY PatientProfileId ORDER BY DateofServiceFrom DESC ) rn
        	  FROM
        	    #Temp ) a
        	    LEFT OUTER JOIN CTC c ON a.PatientProfileId = c.PatientProfileId
        	    
    WHERE
        	rn = 1 
        	
    
    DROP TABLE #Bill
    DROP TABLE #Temp

  2. #2
    Join Date
    Feb 2012
    Posts
    2
    I have gotten the answers I needed on this Query, please disregard.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •