BloodTestAndRelevantRange

Columns

Column

Type

Size

Nulls

Auto

Default

Children

Parents

Comments

ResultId

int

null

DisRadiologyTestResults.ResultId

Implied ConstraintR

ReferralId

int

null

InvestigationId

int

null

DatePerformed

datetime

16,3

√

null

DateReported

datetime

16,3

√

null

BloodTestId

int

√

null

BloodTestName

nvarchar

100

√

null

BloodTestPortalCode

nvarchar

√

null

TestPanelId

int

√

null

TestPanelName

nvarchar

1073741823

√

null

TestPanelAbbreviation

nvarchar

1073741823

√

null

OperatorId

int

√

null

Operator

nvarchar

1073741823

√

null

StringResult

nvarchar

√

null

DecimalResult

decimal

22,5

√

null

UnitId

int

√

null

UnitDescription

nvarchar

√

null

OutcomeId

int

√

null

OutcomeDescription

nvarchar

1073741823

√

null

RangeEffectiveDate

datetime

16,3

√

null

UpperBound

decimal

9,2

√

null

LowerBound

decimal

9,2

√

null

UpperBoundComparison

int

√

null

LowerBoundComparison

int

√

null

IsOnThreshold

bit

√

null

IsOutOfSafeRange

bit

√

null

Relationships

Close relationships within degrees of separation

View Definition

CREATE VIEW [dbo].[BloodTestAndRelevantRange] AS

WITH DefinedTestsAndRanges AS (
SELECT
    btre.Id AS ResultId,
    i.ReferralId,
    i.Id AS InvestigationId,
    i.DatePerformed AS DatePerformed,
    i.DateReported AS DateReported,
    btrd.BloodTestId,	
    ISNULL(bt.Description, btro.BloodTestDescription) AS BloodTestName,
    bt.PortalCode AS BloodTestPortalCode,
    btre.TestPanelId,
    btp.Description AS TestPanelName,
    btp.Abbreviation AS TestPanelAbbreviation,
    btre.OperatorId,
    ro.Description AS Operator,
    btre.StringResult,
    btre.DecimalResult,
    btrd.UnitId,
    ISNULL(uom.Description, btro.UnitOfMeasurement) AS UnitDescription,
    btre.OutcomeId,
    rout.Description AS OutcomeDescription,
    btra.EffectiveDate AS RangeEffectiveDate,
    btra.UpperBound,
    btra.LowerBound,
    --ROW_NUMBER() OVER (PARTITION BY i.Id, btrd.BloodTestId ORDER BY btra.EffectiveDate DESC, btra.Id DESC) r
	ROW_NUMBER() OVER (PARTITION BY i.Id, btre.Id ORDER BY btra.EffectiveDate DESC, btra.Id DESC) r
FROM
    BloodTestResults btre
INNER JOIN Investigations_NotDeleted_BTARR i WITH (NOEXPAND) ON
    i.Id = btre.InvestigationId
LEFT JOIN BloodTestResultDefined btrd ON
    btre.Id = btrd.Id
LEFT JOIN BloodTestResultOther btro ON
    btro.Id = btre.Id
LEFT JOIN BloodTestRanges_NotDeleted btra WITH (NOEXPAND) ON
    btra.ReferralId = i.ReferralId AND btra.BloodTestId = btrd.BloodTestId AND btra.UnitId = btrd.UnitId AND btra.EffectiveDate <= i.DatePerformed
LEFT JOIN BloodTests bt ON
    bt.Id = btrd.BloodTestId
LEFT JOIN ResultOperators ro ON
    ro.Id= btre.OperatorId
LEFT JOIN ResultOutcomes rout ON
    rout.Id = btre.OutcomeId
LEFT JOIN UnitOfMeasurements uom ON
    uom.Id = btrd.UnitId
LEFT JOIN BloodTestPanels btp ON
    btp.Id = btre.TestPanelId
), WithBoundComparisons AS (
SELECT
    ResultId,
    ReferralId,
    InvestigationId,
    DatePerformed,
    DateReported,
    BloodTestId,
    BloodTestName,
    BloodTestPortalCode,
    TestPanelId,
    TestPanelName,
    TestPanelAbbreviation,
    OperatorId,
    Operator,
    StringResult,
    DecimalResult,
    UnitId,
    UnitDescription,
    OutcomeId,
    OutcomeDescription,
    RangeEffectiveDate,
    UpperBound,
    LowerBound,
    CASE
        WHEN UpperBound IS NULL THEN -1 -- if no upper bound, assume below
		WHEN OperatorId IS NULL THEN CAST(SIGN(DecimalResult - UpperBound) as int)
		WHEN OperatorId = 2 AND DecimalResult >= UpperBound THEN 1
		WHEN OperatorId = 2 THEN NULL
		WHEN OperatorId = 1 AND DecimalResult <= UpperBound THEN -1
		WHEN OperatorId = 1 THEN NULL
    END AS UpperBoundComparison,
    CASE
        WHEN LowerBound IS NULL THEN 1 -- if no lower bound, assume above
        WHEN OperatorId IS NULL THEN CAST(SIGN(DecimalResult - LowerBound) AS int)
		WHEN OperatorId = 2 AND DecimalResult >= LowerBound THEN 1
		WHEN OperatorId = 2 THEN NULL
		WHEN OperatorId = 1 AND DecimalResult <= LowerBound THEN -1
		WHEN OperatorId = 1 THEN NULL
    END AS LowerBoundComparison
FROM
    DefinedTestsAndRanges
WHERE   
r = 1
    /*
    NULLs are all considered the same group, so other-type tests or defined tests with no threshold are all rolled into one.
    But if there is no threshold, then there can never be dupes caused by having multiple thresholds; let them all through
    */
    OR BloodTestId IS NULL 
)
SELECT
    ResultId,
    ReferralId,
    InvestigationId,
    DatePerformed,
    DateReported,
    BloodTestId,
    BloodTestName,
    BloodTestPortalCode,
    TestPanelId,
    TestPanelName,
    TestPanelAbbreviation,
    OperatorId,
    Operator,
    StringResult,
    DecimalResult,
    UnitId,
    UnitDescription,
    OutcomeId,
    OutcomeDescription,
    RangeEffectiveDate,
    UpperBound,
    LowerBound,
    UpperBoundComparison,
    LowerBoundComparison,
/*
		uses convention from c-style comparison function
		https://www.gnu.org/software/libc/manual/html_node/Comparison-Functions.html

		+1 means result is over boundary
		0 means equal to boundary
		-1 means under boundary

		NULL is unknown
	*/
        CAST(CASE
        WHEN UpperBoundComparison = 0 OR LowerBoundComparison = 0 THEN 1
		WHEN UpperBoundComparison IS NULL OR LowerBoundComparison IS NULL THEN NULL
        ELSE 0
    END AS BIT) AS IsOnThreshold,
    CAST(CASE
        WHEN UpperBoundComparison = 1 OR LowerBoundComparison = -1 THEN 1
		WHEN UpperBoundComparison IS NULL OR LowerBoundComparison IS NULL THEN NULL
        ELSE 0
    END AS BIT) AS IsOutOfSafeRange
FROM
    WithBoundComparisons

Columns

Relationships

Close relationships within degrees of separation

View Definition

Possibly Referenced Tables/Views