Solved

SQL 2005 - pull Top office visit for each distinct patient

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

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

Suggested Solutions

Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
Windows 10 is mostly good. However the one thing that annoys me is how many clicks you have to do to dial a VPN connection. You have to go to settings from the start menu, (2 clicks), Network and Internet (1 click), Click VPN (another click) then fi…

920 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now