CODEWITHSUNDEEP
sqlsql-servert-sql
user6568810
user6568810

Reputation:

Numbering visits for each patient in a query

I have this query:

SELECT  R.ID,
        R.clm_FirstName + ' ' + R.clm_Surname2 AS 'Name and surname',
        R.clm_Age2 AS 'Age',
        V.ID AS 'VISIT ID',
        CONVERT(varchar, V.clm_VisitDate, 103) AS 'Visit date',
        M.clm_BMI AS 'BMI'
FROM    tblData_Registration R
        INNER JOIN tblData_Visit V ON R.ID=V.RegistrationID
        INNER JOIN tblData_Measurements M ON V.ID=M.VisitID

which gives me:

Query results:

I am trying to number VisitId as 1,2,3.... for each different patient, but I'm at a firm deadlock. Not sure where to go from here. Any pointers?

Thanks a million in advance. Gav :)

Upvotes: 1

Views: 180

Answers (2)

MatBailie
MatBailie

Reputation: 86706

SELECT  R.ID,
        R.clm_FirstName + ' ' + R.clm_Surname2 AS 'Name and surname',
        R.clm_Age2 AS 'Age',
        V.ID AS 'VISIT ID',
        CONVERT(varchar, V.clm_VisitDate, 103) AS 'Visit date',
        M.clm_BMI AS 'BMI',
        ROW_NUMBER() OVER (PARTITION BY R.ID
                               ORDER BY V.clm_VisitDate,
                                        V.ID
                          )
                            AS clm_visit_number
FROM    tblData_Registration R
        INNER JOIN tblData_Visit V ON R.ID=V.RegistrationID
        INNER JOIN tblData_Measurements M ON V.ID=M.VisitID

ROW_NUMBER() will create sequences from 1.

The PARTITION BY R.ID says to have separate sequences for each R.ID.

The ORDER BY determines the order to process the rows when assigning the sequential values. Having V.clm_VisitDate, first ensure that they're in date order. As some dates have more than one visit, an additional field is also added to the ordering; V.ID. This means that when multiple visits occur on the same date (for the same person) the ones with the lowest V.ID get the lowest sequence number.

Upvotes: 2

Michał Turczyn
Michał Turczyn

Reputation: 37367

Use rank window function to achieve your goal :)

SELECT  R.ID,
    R.clm_FirstName + ' ' + R.clm_Surname2 AS 'Name and surname',
    R.clm_Age2 AS 'Age',
    rank() over (partition by R.ID order by V.ID) AS 'VISIT ID',
    CONVERT(varchar, V.clm_VisitDate, 103) AS 'Visit date',
    M.clm_BMI AS 'BMI'
FROM    tblData_Registration R
    INNER JOIN tblData_Visit V ON R.ID=V.RegistrationID
    INNER JOIN tblData_Measurements M ON V.ID=M.VisitID

Upvotes: 1

Related Questions