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

SELECT pv.PatientProfileID
FROM PatientVisit pv
JOIN pp ON pp.PatientProfileID = pv.PatientProfileID
GROUP BY pv.PatientProfileID
HAVING SUM(CASE WHEN pv.DoctorID = 6800 THEN 1 ELSE 0 END) > 0
AND MIN(pv.DoctorID) <> MAX(pv.DoctorID)