VW_ContinuumPeople

Columns

Column

Type

Size

Nulls

Auto

Default

Children

Parents

Comments

SynthMatchID

bigint

√

null

last_name

varchar

100

√

null

first_name

varchar

100

√

null

middle_initial

varchar

√

null

dob

date

√

null

address_1

varchar

200

√

null

address_2

varchar

200

√

null

city

varchar

100

√

null

state

varchar

100

√

null

zip

varchar

√

null

phone_1

varchar

√

null

phone_2

varchar

√

null

phone_3

varchar

√

null

varchar

100

√

null

gender

varchar

√

null

View Definition

CREATE VIEW dbo.VW_ContinuumPeople AS

/*********************************************************************
Database:		DataCStore
View:			dbo.VW_ContinuumPeople
Created:		1/27/2021
Description:	Show each patient only once.
*********************************************************************/

    WITH primary_lookup AS (
        /* This CTE is used to "fill in" MatchID values below.
           This filling-in is necessary because Karan's algorithm
           only assigns a MatchID value to one "representative" from each system.
           Thus if there are multiple matches within a system, all but one of those
           will contain a 0 value for MatchID. */
        SELECT MAX(MatchID) AS MatchID,
               PrimaryID --Reminder: May not be NULL
        FROM DataScience.dbo.ContinuumPatients_PostMatch_new AS pm
        GROUP BY PrimaryID
    ),

    all_patients AS (
        SELECT CASE WHEN PL.MatchID = 0 THEN (-1) * CP.RecordID ELSE PL.MatchID END AS SynthMatchID,
               CP.RecordID,
               CP.PrimaryID,
               CP.source_system,
               CP.source_system_id,
               CP.ssn,
               CP.medicare_num,
               CP.dob,
               CP.last_name,
               CP.first_name,
               CP.middle_initial,
               CP.address_1,
               CP.address_2,
               CP.city,
               CP.state,
               CP.zip,
               CP.phone_1,
               CP.phone_2,
               CP.phone_3,
               CP.email,
               CP.gender,
               CP.race,
               CP.marital_status,
               CP.created_date,
               CP.revision_date,
               CP.AVA_insdate,
               RANK() OVER(PARTITION BY CASE WHEN PL.MatchID = 0 THEN (-1) * CP.RecordID ELSE PL.MatchID END ORDER BY CP.revision_date DESC, CP.source_system, CP.source_system_id) AS rank_num
        FROM dbo.ContinuumPatients AS CP
        JOIN primary_lookup AS PL
        ON CP.PrimaryID = PL.PrimaryID)

    SELECT SynthMatchID,
           last_name,
           first_name,
           middle_initial,
           dob,
           address_1,
           address_2,
           city,
           state,
           zip,
           phone_1,
           phone_2,
           phone_3,
           email,
           gender
    FROM all_patients
    WHERE rank_num = 1

Columns

View Definition

Possibly Referenced Tables/Views