Solved

SQL 2005 - pull Top office visit for each distinct patient

Posted on 2011-03-09
3
352 Views
Last Modified: 2012-08-14
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.

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

Open in new window

0
Comment
Question by:Jeff S
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 13

Expert Comment

by:gamarrojgq
ID: 35088344
Hi,

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


SELECT A.PatientId, B.*
FROM Patient A
	INNER JOIN PatientVisit B ON B.PatientId = A.PatientId
WHERE B.PatientVisitId = (SELECT TOP 1 C.PatientVisitId FROM PatientVisit C WHERE C.PatientId = A.PatientId ORDER BY C.PatientVisitId DESC) --GETTIN THE LAST PATIENTVISITID FROM THE PATIENT

Open in new window

0
 
LVL 50

Accepted Solution

by:
Lowfatspread earned 500 total points
ID: 35088694
use a window fuinction e.g. row_number()

to obtain just the latest data per patient then

e.g.


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')  

 SELECT top 1
                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    #PatientData
        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 0=1

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
                         )
                    )                   
        
	
	    Insert into #patientData
        SELECT  *
        FROM    #Tmp
        WHERE   --Filter on Age
                ( ([Patient Age] >= ( '-1' )
                  AND [Patient Age] <= ( '125' ))
                )      

        DROP TABLE #Tmp

    END
ELSE 
    BEGIN
        Insert into #patientData
        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
                )
        

    END
    
    Select      PatientVisitId,
                PatientId,
                PatientMRN,
                PatientProfileId,
                Name,
                PatientDOB,
               [Patient Age],
               Zip,
               Gender,
               Race,
               CPTCode,
               code,
               Facility,
               ListName,
               Visit,
               TicketNumber,
               FinancialClass,
               PolicyType,
               VisitKey,
               Group1,
               Group2
      from (
    Select * 
           ,ROW_NUMBER() over (partition by PatientProfileId order by patientvisitid desc) as rn
      from #PatientData
           ) as x
     Where rn=1
     Order by Name      
     
     drop table #PatientData

Open in new window

0
 
LVL 7

Author Closing Comment

by:Jeff S
ID: 35090227
Nice, thanks!
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…

617 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