/*
--------------------------------------------------------------------------------------------------
QUERY DOCUMENTATION: Monthly Admission Rate Calculation
--------------------------------------------------------------------------------------------------

--> Purpose: Summarize monthly % admission rate seasonal trends for ALL visits to the emergency department
--> Output: Summary table with Month Name, Month Number, and % Admission Rate

*/

let
    Source = Table.Combine({dept_b_discharge, dept_b_admit}),
    #"Filtered Rows" = Table.SelectRows(Source, each ([gender] = "Female") and ([insurance_status] = "Medicaid")),
    //Create new custom column to convert text month name to month number (1 to 12)
    #"Add MonthNumber" = Table.AddColumn(#"Filtered Rows", "Month", each Date.Month(Date.fromText("1"&[arrivalmonth]))),
    //Convert Month number to numeric type
    #"Convert MonthNumber to numeric" = Table.TransformColumnTypes(#"Add MonthNumber",{{"Month", Int64.Type}}),
    //Group rows by "arrivalmonth", "Month" and "disposition",and group by row count
    #"Grouped Rows" = Table.Group(#"Convert MonthNumber to numeric", {"arrivalmonth", "Month", "disposition"}, {{"Total Visits", each Table.RowCount(_), Int64.Type}}),
    //Pivot column to display total visits by disposition
    #"Pivoted Column" = Table.Pivot(#"Grouped Rows", List.Distinct(#"Grouped Rows"[disposition]), "disposition", "Total Visits", List.Sum),
    //Create a custom column to calculate % Admitted
    #"Calculate Admission Rate" = Table.AddColumn(#"Pivoted Column", "% Admitted", each [Admit]/([Admit]+[Discharge])),
    //Convert type to percentage
    #"Convert Admission Rate to percentage" = Table.TransformColumnTypes(#"Calculate Admission Rate",{{"% Admitted", Percentage.Type}}),
    //Sort rows by Month number
    #"Sort by Month Number" = Table.Sort(#"Convert Admission Rate to percentage",{{"Month", Order.Ascending}}),
in
    #"Sort by Month Number"