February 14, 2012 at 9:57 am
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.
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
February 14, 2012 at 10:12 am
Please try to post your question in accordance with forum etiquette, you can find the link at the bottom of my signature.
You can use following query to get the list of Patients who visited Doctor 6800 in case if they also visited some other doctors
;with pp
AS
(
SELECT pv.PatientProfileID
FROM PatientVisit pv
GROUP BY pv.PatientProfileID
HAVING COUNT(DISTINCT pv.DoctorID) > 1
)
SELECT DISTINCT pv.PatientProfileID
FROM PatientVisit pv
JOIN pp ON pp.PatientProfileID = pv.PatientProfileID
WHERE pv.DoctorID = 6800
February 14, 2012 at 10:21 am
Thank you kindly, I have the results i need.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply