Jeff S
asked on
SQL 2005 - pull Top office visit for each distinct patient
What I need in this report is to just pull the top record from the PatientVisit table (based off the PatientVisitId) for each distinct Patient - (based off the patientprofileId). Example, patient Judy Smith has 5 visits. I only want her most recent visit and its values. I do not care to see the other information. Any assistance is appreciated.
Please note, the SQL is handled dynamically by the SQL server, so items in my WHERE clause may look odd. Please disregard these. They are not an issue.
Please note, the SQL is handled dynamically by the SQL server, so items in my WHERE clause may look odd. Please disregard these. They are not an issue.
SET NOCOUNT ON
DECLARE @startdate DATETIME,
@enddate DATETIME,
@groupby1 VARCHAR(60),
@groupby2 VARCHAR(60)
SET @groupby1 = CONVERT(VARCHAR(60), 'Diagnosis')
SET @groupby2 = CONVERT(VARCHAR(60), '(None)')
SET @startdate = ISNULL(NULL, '1/1/1900')
SET @enddate = ISNULL(NULL, '1/1/3000')
DECLARE @DOBFrom DATETIME,
@DOBTo DATETIME
SET @DOBFrom = ISNULL(NULL, '1/1/1900')
SET @DOBTo = ISNULL(NULL, '1/1/3000')
IF 1 = 1
BEGIN
SELECT DISTINCT
pv.PatientVisitId,
pp.PatientId,
ISNULL(pp.MedicalRecordNumber, '') AS PatientMRN,
pp.PatientProfileId,
pp.[Last] + ', ' + pp.[First] + ' ' + ISNULL(pp.[Middle], '') AS Name,
ISNULL(CONVERT(VARCHAR(20), pp.birthdate, 101), 'No DOB') AS PatientDOB,
FLOOR(DATEDIFF(DAY, pp.birthdate, GETDATE()) / 365.25) AS [Patient Age],
pp.Zip,
ISNULL(pp.sex, '') AS Gender,
ISNULL(race.Description, 'Unknown') AS Race,
dbo.cusfn_GetCPTCode(pv.PatientVisitId) AS CPTCode,
dbo.cusfn_GetDXCode(pv.PatientVisitId) AS code,
fac.OrgName AS Facility,
dr.ListName,
pv.Visit AS Visit,
pv.TicketNumber,
ISNULL(fc.description, 'No Fin. Class') AS FinancialClass,
ISNULL(pt.Description, 'No Policy Type') AS PolicyType,
CONVERT(VARCHAR(20), GETDATE(), 101)
+ UPPER(pp.PatientProfileId) AS VisitKey,
CASE @groupby1
WHEN 'Diagnosis' THEN pvd.code
WHEN 'CPT Code' THEN ISNULL(pvp.CPTCode, 'No CPT Code')
ELSE 'None'
END AS Group1,
CASE @groupby2
WHEN 'Facility' THEN ISNULL(fac.listname, 'No Facility')
WHEN 'Provider' THEN ISNULL(dr.listname, 'No Provider')
WHEN 'Policy Type'
THEN ISNULL(pt.Description, 'No Policy Type')
ELSE 'None'
END AS Group2
INTO #Tmp
FROM PatientVisit pv
INNER JOIN PatientProfile pp ON pv.PatientProfileId = pp.PatientProfileId
INNER JOIN DoctorFacility dr ON pv.DoctorId = dr.DoctorFacilityId
INNER JOIN DoctorFacility fac ON pv.FacilityId = fac.DoctorFacilityId
LEFT JOIN medlists fc ON pv.financialclassMID = fc.medlistsID
LEFT OUTER JOIN InsuranceCarriers ic ON pv.PrimaryInsuranceCarriersID = ic.InsuranceCarriersID
LEFT OUTER JOIN MedLists pt ON ic.PolicyTypeMID = pt.MedListsID
LEFT JOIN Medlists race ON pp.RaceMId = race.medlistsid
INNER JOIN PatientVisitProcs pvp ON pv.PatientVisitId = pvp.PatientVisitId
INNER JOIN PatientVisitDiags pvd ON pv.patientvisitid = pvd.patientvisitid
WHERE ISNULL(pvp.Voided, 0) = 0
AND ( ( '1' = '1'
AND pvp.DateOfServiceFrom >= @startdate
AND pvp.DateOfServiceFrom < @enddate + 1
)
OR ( '1' = '2'
AND pvp.dateofentry >= @startdate
AND pvp.dateofentry < @enddate + 1
)
)
ORDER BY pp.[Last] + ', ' + pp.[First] + ' ' + ISNULL(pp.[Middle], '')
SELECT *
FROM #Tmp
WHERE --Filter on Age
( ([Patient Age] >= ( '-1' )
AND [Patient Age] <= ( '125' ))
)
DROP TABLE #Tmp
END
ELSE
BEGIN
SELECT DISTINCT
pv.PatientVisitId,
pp.PatientId,
ISNULL(pp.MedicalRecordNumber, '') AS PatientMRN,
pp.PatientProfileId,
pp.[Last] + ', ' + pp.[First] + ' ' + ISNULL(pp.[Middle], '') AS Name,
ISNULL(CONVERT(VARCHAR(20), pp.birthdate, 101), 'No DOB') AS PatientDOB,
FLOOR(DATEDIFF(DAY, pp.birthdate, GETDATE()) / 365.25) AS [Patient Age],
pp.Zip,
ISNULL(pp.sex, '') AS Gender,
ISNULL(race.Description, 'Unknown') AS Race,
dbo.cusfn_GetCPTCode(pv.PatientVisitId) AS CPTCode,
dbo.cusfn_GetDXCode(pv.PatientVisitId) AS code,
fac.OrgName AS Facility,
dr.ListName,
pv.Visit AS Visit,
pv.TicketNumber,
ISNULL(fc.description, 'No Fin. Class') AS FinancialClass,
ISNULL(pt.Description, 'No Policy Type') AS PolicyType,
CONVERT(VARCHAR(20), GETDATE(), 101)
+ UPPER(pp.PatientProfileId) AS VisitKey,
CASE @groupby1
WHEN 'Diagnosis' THEN pvd.code
WHEN 'CPT Code' THEN ISNULL(pvp.CPTCode, 'No CPT Code')
ELSE 'None'
END AS Group1,
CASE @groupby2
WHEN 'Facility' THEN ISNULL(fac.listname, 'No Facility')
WHEN 'Provider' THEN ISNULL(dr.listname, 'No Provider')
WHEN 'Policy Type'
THEN ISNULL(pt.Description, 'No Policy Type')
ELSE 'None'
END AS Group2
FROM PatientVisit pv
INNER JOIN PatientProfile pp ON pv.PatientProfileId = pp.PatientProfileId
INNER JOIN DoctorFacility dr ON pv.DoctorId = dr.DoctorFacilityId
INNER JOIN DoctorFacility fac ON pv.FacilityId = fac.DoctorFacilityId
LEFT JOIN medlists fc ON pv.financialclassMID = fc.medlistsID
LEFT OUTER JOIN InsuranceCarriers ic ON pv.PrimaryInsuranceCarriersID = ic.InsuranceCarriersID
LEFT OUTER JOIN MedLists pt ON ic.PolicyTypeMID = pt.MedListsID
LEFT JOIN Medlists race ON pp.RaceMId = race.medlistsid
INNER JOIN PatientVisitProcs pvp ON pv.PatientVisitId = pvp.PatientVisitId
INNER JOIN PatientVisitDiags pvd ON pv.patientvisitid = pvd.patientvisitid
WHERE ISNULL(pvp.Voided, 0) = 0
AND ( ( '1' = '1'
AND pvp.DateOfServiceFrom >= @startdate
AND pvp.DateOfServiceFrom < @enddate + 1
)
OR ( '1' = '2'
AND pvp.dateofentry >= @startdate
AND pvp.dateofentry < @enddate + 1
)
)
AND --Filter on DOBDate
( pp.Birthdate >= @DOBFrom
AND pp.Birthdate <= @DOBTo
)
ORDER BY pp.[Last] + ', ' + pp.[First] + ' ' + ISNULL(pp.[Middle], '')
END
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Nice, thanks!
If you want only 1 row (the top most) of each patient you can do it by JOIN your patient Table against the PatientVisit Table, here is an example to give you the idea
Open in new window