Advanced time setting on PA filter cards not working between inclusion and exclusion

[maggie-li-yd]

Related to #2075

Generated SQL Pattern (Simplified)

In Inclusion report, for two filter cards — T1 (with advance time referencing T2) and T2 — when T1 is excluded (bit=0):

SELECT COUNT(DISTINCT P.pid)
FROM patient P
INNER JOIN interaction_T2 T2 ON P.pid = T2.pid AND T2.code = 'X'
LEFT JOIN interaction_T1 T1 ON P.pid = T1.pid AND T1.code = 'Y'
    AND DAYS_BETWEEN(T1.start_date, T2.start_date) BETWEEN 0 AND 30
WHERE T1.INTERACTION_ID IS NULL

Why It Fails

Consider a patient with multiple T2 records (1:N relationship):

Patient	T2 Record	T1 Record	DAYS_BETWEEN Match?
P1	T2_row_A	T1_row_1	✅ Yes (within 30 days)
P1	T2_row_B	T1_row_1	❌ No (outside 30 days)

The LEFT JOIN evaluates per row pair:

• Row (P1, T2_row_A, T1_row_1): temporal condition matches → T1.INTERACTION_ID IS NOT NULL → filtered out by WHERE
• Row (P1, T2_row_B, T1_row_1): temporal condition fails → LEFT JOIN produces NULL → T1.INTERACTION_ID IS NULL → row survives

After COUNT(DISTINCT pid), P1 is counted in the exclude group (bit=0).

Meanwhile, in the include group (bit=1, using INNER JOIN):

• Row (P1, T2_row_A, T1_row_1): temporal condition matches → P1 counted in include group too.

Result: P1 appears in both bitmap groups → baseCount > totalPatientCount.

Why Non-Temporal Exclusions Don't Have This Problem

Without temporal filters, the LEFT JOIN condition is only on pid (and optionally code):

LEFT JOIN T1 ON P.pid = T1.pid AND T1.code = 'Y'
WHERE T1.INTERACTION_ID IS NULL

This is a pure patient-level check: either the patient has a matching T1 record or they don't. There's no cross-table reference that could produce mixed results across different rows of another table.