Why Check Fields Together?
A record can contain values that each look possible on their own but conflict when considered together. A hospital admission date of May 12 is a possible date, and a discharge date of May 9 is also a possible date. Yet, if both dates describe the same uninterrupted hospital stay, their order is inconsistent. Checking fields in isolation would not reveal the problem.
A cross-variable consistency check tests whether values in two or more fields satisfy a logical or arithmetic relationship that should hold for the kind of record being analyzed. Such checks help identify records that need review before analysis. A failed check is a reason to verify the record, not automatic proof that a particular field is wrong.
State the Rule Before Applying It
A useful check begins with a clear statement of what relationship is expected and when that expectation applies. For example, “discharge date is on or after admission date” may be appropriate for records describing one completed, uninterrupted stay. It may not apply unchanged if the fields refer to different episodes, if one date uses a different convention, or if an unusual administrative process affects how dates are recorded.
Write a rule so that a reviewer can tell which fields it uses, what values satisfy it, and what should happen when a value is missing or the rule does not apply. In general, if a record has fields \(x_1,\ldots,x_k\), a check can be represented as a condition \(C(x_1,\ldots,x_k)\). A record satisfies the check when the condition is true. It is flagged when the condition is false and the check is applicable. If required values are missing, the check may be unevaluable rather than failed.
This distinction matters. A blank discharge date might mean that the patient was still admitted when the data were extracted, not that the discharge date violates an ordering rule. A missing value should not be replaced with an invented one just to make the check calculable.
Worked Example: Admission and Discharge Dates
Consider a hypothetical file in which each row is intended to describe one completed, uninterrupted hospital stay. The stated rule is that the discharge date must be on or after the admission date. The following five records are illustrative.
| Record | Admission date | Discharge date |
|---|---|---|
| A | 2025-03-04 | 2025-03-07 |
| B | 2025-03-12 | 2025-03-12 |
| C | 2025-03-18 | 2025-03-16 |
| D | 2025-03-21 | Missing |
| E | 2025-03-29 | 2025-04-02 |
For a record with both dates present, calculate the elapsed days as discharge date minus admission date. Under the stated rule, the record passes when that difference is zero or greater:
Record A: March 7 minus March 4 is \(3\) days, so \(D=3\) and the check passes.
Record B: March 12 minus March 12 is \(0\) days. Because the rule allows discharge on the admission date, \(D=0\) passes. If same-day discharge were not permitted by the data definition, the rule would need to state that instead.
Record C: March 16 minus March 18 is \(-2\) days. Since \(-2<0\), the record is flagged for verification.
Record D: The discharge date is missing, so \(D\) cannot be calculated. The record is not a demonstrated failure of the date-order rule; it is unevaluable by this check.
Record E: March 29 to April 2 is \(4\) days, so \(D=4\) and the check passes. This example assumes the dates have already been represented consistently as dates, rather than compared as text.
The flag on Record C does not show whether the admission date, discharge date, or record assignment is responsible. The appropriate next step is to verify the information against an authoritative source or ask a knowledgeable reviewer.
Why the Date-Order Check Works
The justification follows directly from the meaning of elapsed time. For a completed stay recorded with the same date convention, discharge occurs at the end of, or after, the stay’s admission date. Therefore the elapsed time from admission to discharge cannot be negative. A negative result contradicts the stated rule, while a zero result represents two events recorded on the same calendar date. Whether zero is acceptable depends on the definition of the fields.
In symbols, let \(a\) be the admission date and \(d\) the discharge date, expressed on a common calendar scale. The rule \(d\geq a\) is equivalent to \(d-a\geq 0\). Thus:
This equivalence explains the calculation, but it does not establish that every record is one completed stay. The meaning of the observational unit and the field definitions determine whether the rule is appropriate in the first place.
Worked Example: Checking Age Against Dates
Suppose a hypothetical registry records a person’s date of birth, date of assessment, and age in completed years on the assessment date. The expected age is the number of birthdays reached by that date. For these data, calculate the year difference and subtract one if the birthday has not yet occurred in the assessment year.
Record A: Date of birth is August 20, 2004; assessment date is June 10, 2024; recorded age is 19. The year difference is \(2024-2004=20\). August 20 has not yet occurred by June 10, so subtract one: \(20-1=19\). The recorded age agrees with the dates.
Record B: Date of birth is February 3, 2001; assessment date is February 3, 2024; recorded age is 24. The year difference is \(2024-2001=23\). The birthday falls on the assessment date, so it has occurred by that date; no subtraction is needed. The expected age is \(23-0=23\), which does not match the recorded age of 24. Flag the record for verification.
Record C: The date of birth is missing, while the assessment date and recorded age are present. The expected age cannot be calculated from the available fields, so this check is unevaluable for this record.
This rule is valid only when age means completed years on the assessment date and the date fields use the intended calendar conventions. If age is recorded in bands, rounded, or measured at a different time, the comparison must be adjusted to match that definition. A mismatch is still a verification signal, not a basis for silently changing the recorded age.
Checks Can Use Arithmetic Relationships
Some consistency rules compare a reported value with components from which it should be calculated. For a record with a total and two components, a possible rule is that the total equals the sum of the components. Whether exact equality is appropriate depends on how the values are recorded. For example, currency amounts recorded to the nearest cent can usually be compared at cent precision if the total is defined as their sum. Measurements rounded at an earlier stage may need a documented rounding allowance; an arbitrary tolerance should not be added merely to make records pass.
Worked Example: Verifying a Reported Total
Imagine a hypothetical billing file with three fields: supply charge, service charge, and reported total. The file’s definition states that the total is the sum of the two charges, all recorded to the nearest cent. The check is therefore supply charge plus service charge equals reported total.
| Record | Supply charge | Service charge | Reported total |
|---|---|---|---|
| 1 | $125.00 | $40.00 | $165.00 |
| 2 | $80.00 | $15.00 | $92.60 |
| 3 | $24.50 | Missing | $24.50 |
Record 1: \(125.00+40.00=165.00\). The calculated sum equals the reported total, so the check passes.
Record 2: \(80.00+15.00=95.00\). The reported total is $92.60, and \(95.00\neq92.60\), so the record is flagged for verification.
Record 3: The service charge is missing, so the sum cannot be checked. The reported total happens to equal the supply charge, but that equality does not prove the missing service charge was zero. The rule is unevaluable unless the data definition explicitly establishes how a missing service charge should be interpreted.
The check depends on the stated billing definition: if the reported total includes additional fees not represented by these two fields, this particular equality is not the right rule. Defining the fields and the expected arithmetic relationship is as important as performing the calculation.
A Practical Review Process
Apply checks in a consistent sequence so that their results can be interpreted and repeated. The following process keeps the logic visible.
Confirm what one row represents and what each relevant field means. A rule for a single stay may not apply to a row that combines multiple stays.
State the condition in plain language and, when useful, as an equation or inequality. Specify whether equality is allowed and whether a rounding tolerance is justified by the measurement or recording process.
Decide which kinds of records the rule covers. State whether missing inputs make a check unevaluable instead of failed.
Record which rows pass, fail, or cannot be evaluated. Keep the original field values available for review rather than altering them during the check.
Use the appropriate source or review process to determine whether the rule applies and whether any value needs correction. A flag alone cannot answer those questions.
Common Mistakes and Why These Checks Matter
Cross-variable checks can catch contradictions that a range check on one field at a time will miss. They are particularly useful when fields have a known relationship: dates that define an interval, a recorded age tied to a date of birth, or a total tied to component amounts. They also make data review more systematic because the expected relationship is stated explicitly rather than judged by intuition record by record.
- Treating every flag as a confirmed error. A negative date interval is a contradiction under the stated assumptions, but the record still needs verification. The wrong field is not identified by the check.
- Applying a plausible rule without confirming its scope. “Discharge follows admission” may not fit a row that combines episodes or uses different event definitions. Confirm what the record and fields represent.
- Marking missing inputs as failures. When a required value is absent, the relationship may be impossible to evaluate. Distinguish “failed” from “unevaluable.”
- Adding an unsupported tolerance. A tolerance should follow from the measurement scale, rounding, or data definition. Choosing one simply because it reduces flags can hide real inconsistencies.
- Automatically replacing one value with another. A failed equality or ordering check does not reveal which field is correct. Preserve the record for review instead of guessing.
The central discipline is to separate a logical test from a conclusion about the data. A check says, “These fields do not satisfy this stated relationship under these conditions.” Verification determines what should happen next.
Check Your Understanding
For each question, say what the rule can establish and what still requires judgment or verification.
- A record describes one completed stay, with admission date June 14 and discharge date June 11. What does the date-order check show? Does it identify which date is wrong?
- A rule compares recorded age in completed years with date of birth and assessment date. What should happen if the date of birth is missing?
- A total is defined as the sum of two components, but one component is missing. Is the check a pass, a failure, or unevaluable from the available fields? Explain.
- Why should a consistency rule state whether equality is allowed, as in a same-day admission and discharge?
- A check flags a reported total that differs from the sum of its parts. Give one reason the rule might not apply and one appropriate next step.