Expiring Today—Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

SQL 2005 - pull Top office visit for each distinct patient

Posted on 2011-03-09
3
Medium Priority
?
354 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 2000 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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…

730 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