Solved

SQL 2005 - pull Top office visit for each distinct patient

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

Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

Join & Write a Comment

Suggested Solutions

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.…
If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…

746 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

14 Experts available now in Live!

Get 1:1 Help Now