Solved

SQL 2005 - pull Top office visit for each distinct patient

Posted on 2011-03-09
3
346 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
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

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

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

by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
Introduction Hopefully the following mnemonic and, ultimately, the acronym it represents is common place to all those reading: Please Excuse My Dear Aunt Sally (PEMDAS). Briefly, though, PEMDAS is used to signify the order of operations (http://en.…
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…

770 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