Snippet 009

-- Patients WITH at least one haematology result
SELECT COUNT(DISTINCT d.PROCHI) AS WithLabs
FROM Project_12345..Demography_Current d
INNER JOIN Project_12345..HaematologyRestructured h
    ON d.PROCHI = h.PROCHI

-- Patients WITHOUT any haematology result
SELECT COUNT(DISTINCT d.PROCHI) AS WithoutLabs
FROM Project_12345..Demography_Current d
LEFT JOIN Project_12345..HaematologyRestructured h
    ON d.PROCHI = h.PROCHI
WHERE h.PROCHI IS NULL

-- Combined summary in a single query
SELECT
    COUNT(DISTINCT d.PROCHI) AS TotalCohort,
    COUNT(DISTINCT h.PROCHI) AS WithLabs,
    COUNT(DISTINCT d.PROCHI) - COUNT(DISTINCT h.PROCHI) AS WithoutLabs
FROM Project_12345..Demography_Current d
LEFT JOIN Project_12345..HaematologyRestructured h
    ON d.PROCHI = h.PROCHI

with engine.connect() as conn:
    demog = pd.read_sql("""
        SELECT DISTINCT PROCHI
        FROM Project_12345..Demography_Current
    """, conn)

    lab_prochis = pd.read_sql("""
        SELECT DISTINCT PROCHI
        FROM Project_12345..HaematologyRestructured
    """, conn)

# Patients with and without lab results
has_labs = demog["PROCHI"].isin(lab_prochis["PROCHI"])

print(f"Total cohort:  {len(demog)}")
print(f"With labs:     {has_labs.sum()}")
print(f"Without labs:  {(~has_labs).sum()}")

demog <- dbGetQuery(conn, "
    SELECT DISTINCT PROCHI
    FROM Project_12345..Demography_Current
")

lab_prochis <- dbGetQuery(conn, "
    SELECT DISTINCT PROCHI
    FROM Project_12345..HaematologyRestructured
")

has_labs <- demog$PROCHI %in% lab_prochis$PROCHI

cat("Total cohort:", nrow(demog), "\n")
cat("With labs:   ", sum(has_labs), "\n")
cat("Without labs:", sum(!has_labs), "\n")