Snippet 006

SELECT
    CASE
        WHEN d.Calculated_Age < 20                  THEN '<20'
        WHEN d.Calculated_Age BETWEEN 20 AND 29   THEN '20-29'
        WHEN d.Calculated_Age BETWEEN 30 AND 39   THEN '30-39'
        WHEN d.Calculated_Age BETWEEN 40 AND 49   THEN '40-49'
        WHEN d.Calculated_Age BETWEEN 50 AND 59   THEN '50-59'
        WHEN d.Calculated_Age BETWEEN 60 AND 69   THEN '60-69'
        WHEN d.Calculated_Age BETWEEN 70 AND 79   THEN '70-79'
        ELSE '80+'
    END AS AgeGroup,
    i.Approved_Name,
    COUNT(*) AS PrescriptionCount
FROM Project_12345..Prescribing_Scripts s
INNER JOIN Project_12345..Prescribing_Scripts_Prescribing_Item i
    ON s.hic_drugID = i.hic_drugID
INNER JOIN Project_12345..Demography_Current d
    ON s.PROCHI = d.PROCHI
GROUP BY
    CASE
        WHEN d.Calculated_Age < 20                  THEN '<20'
        WHEN d.Calculated_Age BETWEEN 20 AND 29   THEN '20-29'
        WHEN d.Calculated_Age BETWEEN 30 AND 39   THEN '30-39'
        WHEN d.Calculated_Age BETWEEN 40 AND 49   THEN '40-49'
        WHEN d.Calculated_Age BETWEEN 50 AND 59   THEN '50-59'
        WHEN d.Calculated_Age BETWEEN 60 AND 69   THEN '60-69'
        WHEN d.Calculated_Age BETWEEN 70 AND 79   THEN '70-79'
        ELSE '80+'
    END,
    i.Approved_Name
ORDER BY PrescriptionCount DESC

with engine.connect() as conn:
    scripts = pd.read_sql("""
        SELECT s.PROCHI, s.hic_drugID
        FROM Project_12345..Prescribing_Scripts s
    """, conn)

    items = pd.read_sql("""
        SELECT hic_drugID, Approved_Name
        FROM Project_12345..Prescribing_Scripts_Prescribing_Item
    """, conn)

    demog = pd.read_sql("""
        SELECT PROCHI, Calculated_Age
        FROM Project_12345..Demography_Current
    """, conn)

# Merge the three tables
df = scripts.merge(items, on="hic_drugID").merge(demog, on="PROCHI")

# Create age bands in 10-year intervals
bins = [0, 20, 30, 40, 50, 60, 70, 80, 200]
labels = ["<20", "20-29", "30-39", "40-49", "50-59", "60-69", "70-79", "80+"]
df["AgeGroup"] = pd.cut(df["Calculated_Age"], bins=bins, labels=labels, right=False)

# Count prescription records per age group and drug
result = (df.groupby(["AgeGroup", "Approved_Name"])
            .size()
            .reset_index(name="PrescriptionCount")
            .sort_values("PrescriptionCount", ascending=False))
result

# Load all three tables
scripts <- dbGetQuery(conn, "
    SELECT PROCHI, hic_drugID
    FROM Project_12345..Prescribing_Scripts
")

items <- dbGetQuery(conn, "
    SELECT hic_drugID, Approved_Name
    FROM Project_12345..Prescribing_Scripts_Prescribing_Item
")

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

# Merge and create age bands
df <- merge(merge(scripts, items, by = "hic_drugID"), demog, by = "PROCHI")

df$AgeGroup <- cut(df$Calculated_Age,
                   breaks = c(0, 20, 30, 40, 50, 60, 70, 80, Inf),
                   labels = c("<20", "20-29", "30-39", "40-49",
                              "50-59", "60-69", "70-79", "80+"),
                   right = FALSE)

# Count prescription records
result <- aggregate(hic_drugID ~ AgeGroup + Approved_Name, data = df, FUN = length)
names(result)[3] <- "PrescriptionCount"
result <- result[order(-result$PrescriptionCount), ]
head(result, 20)