VW_Base10_Employee_Tests_Export

Columns

Column

Type

Size

Nulls

Auto

Default

Children

Parents

Comments

FacilityCode

varchar

√

null

FacilityId

int

√

null

PCC_Cache_FacID.FacilityID

Implied ConstraintR

EmployeeId

varchar

√

null

FirstName

varchar

100

√

null

MiddleName

varchar

100

√

null

LastName

varchar

100

√

null

DOB

varchar

√

null

TestCode

varchar

√

null

Test

varchar

null

AdministeredBy

varchar

100

√

null

TestDate

nvarchar

8000

√

null

TestResult

varchar

null

AntigenTest

varchar

null

Relationships

Close relationships within degrees of separation

View Definition

CREATE VIEW dbo.VW_Base10_Employee_Tests_Export AS
    /*********************************************************************
    Database:		DataCStore
    View:			dbo.VW_Base10_Employee_Tests_Export
    Created:		4/19/2021
    Description:
    *********************************************************************/

    /* First pass: isolate most recent date for first vaccine (or in case of J&J, only) */
    WITH test_codes AS (
        SELECT 'ACVNEG'                         AS TestCode,
               'A-Covid-19 Binax Test Negative' AS Test,
               'Negative'                       AS TestResult,
               'Binax'                          AS AntigenTest
        UNION
        SELECT 'ACVPOS'                         AS TestCode,
               'A-Covid-19 Binax Test Positive' AS Test,
               'Positive'                       AS TestResult,
               'Binax'                          AS AntigenTest
        UNION
        SELECT 'ACVVNG'                                AS TestCode,
               'A-Covid-19 Veritor Plus Test Negative' AS Test,
               'Negative'                              AS TestResult,
               'Veritor'                               AS AntigenTest
        UNION
        SELECT 'ACVVPS'                                AS TestCode,
               'A-Covid-19 Veritor Plus Test Positive' AS Test,
               'Positive'                              AS TestResult,
               'Veritor'                               AS AntigenTest
        UNION
        SELECT 'ACVQNG'                            AS TestCode,
               'A-Covid-19 Quickvue Test Negative' AS Test,
               'Negative'                          AS TestResult,
               'Quickvue'                          AS AntigenTest
        UNION
        SELECT 'ACVQPS'                            AS TestCode,
               'A-Covid-19 Quickvue Test Positive' AS Test,
               'Positive'                          AS TestResult,
               'Quickvue'                          AS AntigenTest
    )
/*
 FacilityId,EmployeeId,FirstName,MiddleName,LastName,DOB,Gender,Race,Ethnicity,Status,
 Email,Phone,Street1,Street2,City,State,Zip,
 CovidVaccineType,CovidVaccineDate1,CovidVaccineDate2
 */
    SELECT DISTINCT
           ee.FacilityId AS FacilityCode,
           NULL       AS FacilityId,
           ee.EmployeeId,
           ee.FirstName,
           ee.MiddleName,
           ee.LastName,
           CAST(ee.DOB AS VARCHAR) AS DOB,
           tests.TestCode,
           test_codes.Test,
           NULLIF(tests.AdministeredBy, '') AS AdministeredBy,
           FORMAT(tests.Date, 'yyyy-MM-dd') AS TestDate,
           test_codes.TestResult,
           test_codes.AntigenTest
    FROM DataCStore.dbo.Base10_Export_Employees AS ee
    JOIN dbo.Base10_UltiPro_Tests AS tests
    ON ee.EmployeeId = tests.EmployeeNumber
    JOIN test_codes
    ON tests.TestCode = test_codes.TestCode
    WHERE ee.FacilityId NOT IN ('', '429', '470')

Columns

Relationships

Close relationships within degrees of separation

View Definition

Possibly Referenced Tables/Views