1-- Patients WITH at least one haematology result2SELECT COUNT(DISTINCT d.PROCHI) AS WithLabs3FROM Project_12345..Demography_Current d4INNER JOIN Project_12345..HaematologyRestructured h5    ON d.PROCHI = h.PROCHI6 7-- Patients WITHOUT any haematology result8SELECT COUNT(DISTINCT d.PROCHI) AS WithoutLabs9FROM Project_12345..Demography_Current d10LEFT JOIN Project_12345..HaematologyRestructured h11    ON d.PROCHI = h.PROCHI12WHERE h.PROCHI IS NULL13 14-- Combined summary in a single query15SELECT16    COUNT(DISTINCT d.PROCHI) AS TotalCohort,17    COUNT(DISTINCT h.PROCHI) AS WithLabs,18    COUNT(DISTINCT d.PROCHI) - COUNT(DISTINCT h.PROCHI) AS WithoutLabs19FROM Project_12345..Demography_Current d20LEFT JOIN Project_12345..HaematologyRestructured h21    ON d.PROCHI = h.PROCHI
1with engine.connect() as conn:2    demog = pd.read_sql("""3        SELECT DISTINCT PROCHI4        FROM Project_12345..Demography_Current5    """, conn)6 7    lab_prochis = pd.read_sql("""8        SELECT DISTINCT PROCHI9        FROM Project_12345..HaematologyRestructured10    """, conn)11 12# Patients with and without lab results13has_labs = demog["PROCHI"].isin(lab_prochis["PROCHI"])14 15print(f"Total cohort:  {len(demog)}")16print(f"With labs:     {has_labs.sum()}")17print(f"Without labs:  {(~has_labs).sum()}")
1demog <- dbGetQuery(conn, "2    SELECT DISTINCT PROCHI3    FROM Project_12345..Demography_Current4")5 6lab_prochis <- dbGetQuery(conn, "7    SELECT DISTINCT PROCHI8    FROM Project_12345..HaematologyRestructured9")10 11has_labs <- demog$PROCHI %in% lab_prochis$PROCHI12 13cat("Total cohort:", nrow(demog), "\n")14cat("With labs:   ", sum(has_labs), "\n")15cat("Without labs:", sum(!has_labs), "\n")