Snippet 004

-- Step 1: Build the grouped counts into a temp table
SELECT sex,
       CASE
           WHEN Calculated_Age < 30                      THEN '<30'
           WHEN Calculated_Age BETWEEN 30 AND 39       THEN '30-39'
           WHEN Calculated_Age BETWEEN 40 AND 49       THEN '40-49'
           ELSE '50+'
       END AS AgeGroup,
       COUNT(DISTINCT PROCHI) AS PatientCount
INTO #temp
FROM Project_12345..Demography_Current
GROUP BY sex,
       CASE
           WHEN Calculated_Age < 30                      THEN '<30'
           WHEN Calculated_Age BETWEEN 30 AND 39       THEN '30-39'
           WHEN Calculated_Age BETWEEN 40 AND 49       THEN '40-49'
           ELSE '50+'
       END

-- Step 2: Pivot to wide format
SELECT AgeGroup,
       SUM(IIF(sex = 'M', PatientCount, 0)) AS Male,
       SUM(IIF(sex = 'F', PatientCount, 0)) AS Female
FROM #temp
GROUP BY AgeGroup

-- Clean up
DROP TABLE #temp

with engine.connect() as conn:
    df = pd.read_sql("""
        SELECT sex, Calculated_Age, PROCHI
        FROM Project_12345..Demography_Current
    """, conn)

bins = [0, 30, 40, 50, 200]
labels = ["<30", "30-39", "40-49", "50+"]
df["AgeGroup"] = pd.cut(df["Calculated_Age"], bins=bins, labels=labels, right=False)

pivot = df.groupby(["AgeGroup", "sex"])["PROCHI"].nunique().unstack(fill_value=0)
pivot.rename(columns={"M": "Male", "F": "Female"})

df <- dbGetQuery(conn, "
    SELECT sex, Calculated_Age, PROCHI
    FROM Project_12345..Demography_Current
")

df$AgeGroup <- cut(df$Calculated_Age,
                   breaks = c(0, 30, 40, 50, Inf),
                   labels = c("<30", "30-39", "40-49", "50+"),
                   right = FALSE)

table(df$AgeGroup, df$sex)