...
select quarter_id, reason_id, count(reason_id) as count1 from txfirst_dashboarddata left join txfirst_quarter on quarter_id = txfirst_quarter.id where unit_id = 4 and txfirst_quarter.quarter = 201801 and list_status = "working_up" group by reason_id order by quarter_id
Result:
quarter_id | reason_id | count1 |
2 | 1 | 4 |
2 | 2 | 1 |
2 | 4 | 2 |
Then the bars of Preston at quarter 201801 and list_status=”working_up” depict the following:
...
Absolute numbers (Graph7)
Same calculation as Graph4 with function get_reasons and arguments list_status="no_decision", percentage=False.
Main functions used for the graphs 4-7 called by get_reasons function
get_reason_counts function – sum of all dashboarddata records per reason_id, unit_id, quarter_id (all quarters: >= some quarter_id) in a unit
Ex: select quarter_id, reason_id, count(reason_id) as count1 from txfirst_dashboarddata left join txfirst_quarter on quarter_id = txfirst_quarter.id where unit_id = 4 and txfirst_quarter.quarter >= 201601 and list_status = "working_up" group by quarter_id, reason_id order by quarter_id
Similar to:
select quarter_id, reason_id, count(reason_id) as count1 from txfirst_dashboarddata where unit_id = 4 and quarter_id >= 1 and list_status = "working_up" group by quarter_id, reason_id order by quarter_id
get_total_counts function – sum of all dashboarddata records per list_status and quarter_id >= some quarter_id in a unit
Ex. select quarter_id, count(quarter_id) as count1 from txfirst_dashboarddata left join txfirst_quarter on quarter_id = txfirst_quarter.id where unit_id = 4 and txfirst_quarter.quarter >= 201601 and list_status = "working_up" group by quarter_id order by quarter_id
or:
select quarter_id, count(quarter_id) as count1 from txfirst_dashboarddata where unit_id = 4 and list_status = "working_up" and quarter_id >= 1 group by quarter_id order by quarter_id