1SELECT2 CASE3 WHEN d.Calculated_Age < 20 THEN '<20'4 WHEN d.Calculated_Age BETWEEN 20 AND 29 THEN '20-29'5 WHEN d.Calculated_Age BETWEEN 30 AND 39 THEN '30-39'6 WHEN d.Calculated_Age BETWEEN 40 AND 49 THEN '40-49'7 WHEN d.Calculated_Age BETWEEN 50 AND 59 THEN '50-59'8 WHEN d.Calculated_Age BETWEEN 60 AND 69 THEN '60-69'9 WHEN d.Calculated_Age BETWEEN 70 AND 79 THEN '70-79'10 ELSE '80+'11 END AS AgeGroup,12 i.Approved_Name,13 COUNT(*) AS PrescriptionCount14FROM Project_12345..Prescribing_Scripts s15INNER JOIN Project_12345..Prescribing_Scripts_Prescribing_Item i16 ON s.hic_drugID = i.hic_drugID17INNER JOIN Project_12345..Demography_Current d18 ON s.PROCHI = d.PROCHI19GROUP BY20 CASE21 WHEN d.Calculated_Age < 20 THEN '<20'22 WHEN d.Calculated_Age BETWEEN 20 AND 29 THEN '20-29'23 WHEN d.Calculated_Age BETWEEN 30 AND 39 THEN '30-39'24 WHEN d.Calculated_Age BETWEEN 40 AND 49 THEN '40-49'25 WHEN d.Calculated_Age BETWEEN 50 AND 59 THEN '50-59'26 WHEN d.Calculated_Age BETWEEN 60 AND 69 THEN '60-69'27 WHEN d.Calculated_Age BETWEEN 70 AND 79 THEN '70-79'28 ELSE '80+'29 END,30 i.Approved_Name31ORDER BY PrescriptionCount DESC
1with engine.connect() as conn:2 scripts = pd.read_sql("""3 SELECT s.PROCHI, s.hic_drugID4 FROM Project_12345..Prescribing_Scripts s5 """, conn)6 7 items = pd.read_sql("""8 SELECT hic_drugID, Approved_Name9 FROM Project_12345..Prescribing_Scripts_Prescribing_Item10 """, conn)11 12 demog = pd.read_sql("""13 SELECT PROCHI, Calculated_Age14 FROM Project_12345..Demography_Current15 """, conn)16 17# Merge the three tables18df = scripts.merge(items, on="hic_drugID").merge(demog, on="PROCHI")19 20# Create age bands in 10-year intervals21bins = [0, 20, 30, 40, 50, 60, 70, 80, 200]22labels = ["<20", "20-29", "30-39", "40-49", "50-59", "60-69", "70-79", "80+"]23df["AgeGroup"] = pd.cut(df["Calculated_Age"], bins=bins, labels=labels, right=False)24 25# Count prescription records per age group and drug26result = (df.groupby(["AgeGroup", "Approved_Name"])27 .size()28 .reset_index(name="PrescriptionCount")29 .sort_values("PrescriptionCount", ascending=False))30result1# Load all three tables2scripts <- dbGetQuery(conn, "3 SELECT PROCHI, hic_drugID4 FROM Project_12345..Prescribing_Scripts5")6 7items <- dbGetQuery(conn, "8 SELECT hic_drugID, Approved_Name9 FROM Project_12345..Prescribing_Scripts_Prescribing_Item10")11 12demog <- dbGetQuery(conn, "13 SELECT PROCHI, Calculated_Age14 FROM Project_12345..Demography_Current15")16 17# Merge and create age bands18df <- merge(merge(scripts, items, by = "hic_drugID"), demog, by = "PROCHI")19 20df$AgeGroup <- cut(df$Calculated_Age,21 breaks = c(0, 20, 30, 40, 50, 60, 70, 80, Inf),22 labels = c("<20", "20-29", "30-39", "40-49",23 "50-59", "60-69", "70-79", "80+"),24 right = FALSE)25 26# Count prescription records27result <- aggregate(hic_drugID ~ AgeGroup + Approved_Name, data = df, FUN = length)28names(result)[3] <- "PrescriptionCount"29result <- result[order(-result$PrescriptionCount), ]30head(result, 20)