Hi Folks,
WCB and the Governor’s office will be doing a formal press announcement on WCB’s virtual hearings in the coming weeks. This is very exciting. Our PIO is requesting some basic stats to weave into the announcement. Would it be possible for you guys to run a few queries to capture the following (time period is week of 10/9/2017 thru present) ?
Total virtual hearings conducted to date Total virtual hearings conducted with nobody attending virtually Total virtual hearings where 1 party attended virtually Total virtual hearings where 2 parties attended virtually Total virtual hearings where 3 parties attended virtually Total virtual hearings where more than 3 parties attended virtually
[ 2 + 3 + 4 + 5 + 6 = 1 ]
Also, the following role specific counts:
Total virtual hearings where the claimant attorney attended virtually Total virtual hearings where the carrier attorney/rep attended virtually Total virtual hearings where the injured worker (claimant) attended virtually
Thanks!!
TIM PURCELL | (518) 369-2538
Project Director, WCB BPR/Modernization
Completed Hearings where each lead hearing and its associated hearings are counted as 1 hearing: 3432 (as of 1/11/2018)
(Hearing.where("hearings.claimant_last_name != 'Nys wcb fake case' AND hearings.status = 'COMPLETE'").sort.uniq - Hearing.where("hearings.claimant_last_name != 'Nys wcb fake case' AND hearings.status = 'COMPLETE' AND hearings.is_lead_hearing = true").map{|h| h.associated_hearings}.flatten).count
October (2017): 70
(Hearing.where("hearings.claimant_last_name != 'Nys wcb fake case' AND hearings.status = 'COMPLETE' AND hearings.date_time > ? AND hearings.date_time < ?", Time.zone.local(2017, 'oct').beginning_of_month, Time.zone.local(2017, 'oct').end_of_month).sort.uniq - Hearing.where("hearings.claimant_last_name != 'Nys wcb fake case' AND hearings.status = 'COMPLETE' AND hearings.is_lead_hearing = true AND hearings.date_time > ? AND hearings.date_time < ?", Time.zone.local(2017, 'oct').beginning_of_month, Time.zone.local(2017, 'oct').end_of_month).map{|h| h.associated_hearings}.flatten).count
November (2017): 518
(Hearing.where("hearings.claimant_last_name != 'Nys wcb fake case' AND hearings.status = 'COMPLETE' AND hearings.date_time > ? AND hearings.date_time < ?", Time.zone.local(2017, 'nov').beginning_of_month, Time.zone.local(2017, 'nov').end_of_month).sort.uniq - Hearing.where("hearings.claimant_last_name != 'Nys wcb fake case' AND hearings.status = 'COMPLETE' AND hearings.is_lead_hearing = true AND hearings.date_time > ? AND hearings.date_time < ?", Time.zone.local(2017, 'nov').beginning_of_month, Time.zone.local(2017, 'nov').end_of_month).map{|h| h.associated_hearings}.flatten).count
December (2017): 1649
(Hearing.where("hearings.claimant_last_name != 'Nys wcb fake case' AND hearings.status = 'COMPLETE' AND hearings.date_time > ? AND hearings.date_time < ?", Time.zone.local(2017, 'dec').beginning_of_month, Time.zone.local(2017, 'dec').end_of_month).sort.uniq - Hearing.where("hearings.claimant_last_name != 'Nys wcb fake case' AND hearings.status = 'COMPLETE' AND hearings.is_lead_hearing = true AND hearings.date_time > ? AND hearings.date_time < ?", Time.zone.local(2017, 'dec').beginning_of_month, Time.zone.local(2017, 'dec').end_of_month).map{|h| h.associated_hearings}.flatten).count
January (as of 1/11/2018): 1195
(Hearing.where("hearings.claimant_last_name != 'Nys wcb fake case' AND hearings.status = 'COMPLETE' AND hearings.date_time > ? AND hearings.date_time < ?", Time.zone.local(2018, 'jan').beginning_of_month, Time.zone.local(2018, 'jan').end_of_month).sort.uniq - Hearing.where("hearings.claimant_last_name != 'Nys wcb fake case' AND hearings.status = 'COMPLETE' AND hearings.is_lead_hearing = true AND hearings.date_time > ? AND hearings.date_time < ?", Time.zone.local(2018, 'jan').beginning_of_month, Time.zone.local(2018, 'jan').end_of_month).map{|h| h.associated_hearings}.flatten).count
Hearings with each associated counted as 1 hearing:
Hearing.where("hearings.claimant_last_name != 'Nys wcb fake case' AND hearings.status = 'COMPLETE'").sort.uniq.count
Lead hearings:
Hearing.where("hearings.claimant_last_name != 'Nys wcb fake case' AND hearings.is_lead_hearing = true").sort.uniq.count
Hearing.where(is_lead_hearing: true).count
First run this query to get all 'COMPLETE' hearings:
hearings = (Hearing.where("hearings.claimant_last_name != 'Nys wcb fake case' AND hearings.status = 'COMPLETE'").sort.uniq - Hearing.where("hearings.claimant_last_name != 'Nys wcb fake case' AND hearings.status = 'COMPLETE' AND hearings.is_lead_hearing = true").map{|h| h.associated_hearings}.flatten)
Or this query to get all 'COMPLETE' hearings for a specific month (ex. below is for January 2018):
hearings = (Hearing.where("hearings.claimant_last_name != 'Nys wcb fake case' AND hearings.status = 'COMPLETE' AND hearings.date_time > ? AND hearings.date_time < ?", Time.zone.local(2018, 'jan').beginning_of_month, Time.zone.local(2018, 'jan').end_of_month).sort.uniq) - (Hearing.where("hearings.claimant_last_name != 'Nys wcb fake case' AND hearings.status = 'COMPLETE' AND hearings.is_lead_hearing = true AND hearings.date_time > ? AND hearings.date_time < ?", Time.zone.local(2018, 'jan').beginning_of_month, Time.zone.local(2018, 'jan').end_of_month).map{|h| h.associated_hearings}.flatten)
Then get attendee information:
hearings_attendees_pois = hearings.map do |hearing|
hearing_data = Hearing.current_hearing_data(hearing)
if hearing.has_associated_hearings?
hearing.associated_hearings.each do |associated|
associated.attendees.each do |attendee|
poi_is_unique = hearing_data["attendees"].none?{|attendee_data| attendee_data["poi"] == attendee.poi}
if poi_is_unique
attributes = attendee.attributes
attributes["poi"] = attendee.poi.attributes
hearing_data["attendees"] << attributes
end
end
end
end
hearing_data
end
Total virtual hearings where at least one party attended virtually:*
hearings_attendees_pois.select{|h| h["attendees"].any?{|a| a["recorded_in_cis"] == true && a["attending_method"] == "Virtually"}}.count
- Oct/2017: 70
- Nov/2017: 206
- Dec/2017: 746
- Jan/2018: 591
- TOTAL: 1613 (47.0%)
Total virtual hearings conducted with nobody attending virtually:
hearings_attendees_pois.select{|h| h["attendees"].none?{|a| a["recorded_in_cis"] == true && a["attending_method"] == "Virtually"}}.count
- Oct/2017: 0
- Nov/2017: 312
- Dec/2017: 903
- Jan/2018: 604
- TOTAL: 1819 (53.0%)
Total virtual hearings where all parties attended virtually:*
hearings_attendees_pois.select{|h| h["attendees"].all?{|a| a["recorded_in_cis"] == true && a["attending_method"] == "Virtually"}}.count
- Oct/2017: 20
- Nov/2017: 17
- Dec/2017: 64
- Jan/2018: 74
- TOTAL: 175 (5.1%)
Total virtual hearings where 1 party attended virtually:
hearings_attendees_pois.select{|h| h["attendees"].one?{|a| a["recorded_in_cis"] == true && a["attending_method"] == "Virtually"}}.count
- Oct/2017: 31
- Nov/2017: 154
- Dec/2017: 544
- Jan/2018: 382
- TOTAL: 1111 (32.37%)
Total virtual hearings where 2 parties attended virtually:
final = []; hearings_attendees_pois.select{|h| if (h["attendees"].select{|a| a["recorded_in_cis"] == true && a["attending_method"] == "Virtually"}.count) == 2; final << h; end}; final.count
- Oct/2017: 27
- Nov/2017: 37
- Dec/2017: 146
- Jan/2018: 135
- TOTAL: 345 (10.05%)
Total virtual hearings where 3 parties attended virtually:
final = []; hearings_attendees_pois.select{|h| if (h["attendees"].select{|a| a["recorded_in_cis"] == true && a["attending_method"] == "Virtually"}.count) == 3; final << h; end}; final.count
- Oct/2017: 8
- Nov/2017: 8
- Dec/2017: 38
- Jan/2018: 49
- TOTAL: 103 (3.00%)
Total virtual hearings where more than 3 parties attended virtually:
final = []; hearings_attendees_pois.select{|h| if (h["attendees"].select{|a| a["recorded_in_cis"] == true && a["attending_method"] == "Virtually"}.count) > 3; final << h; end}; final.count
- Oct/2017: 4
- Nov/2017: 7
- Dec/2017: 18
- Jan/2018: 25
- TOTAL: 54 (1.57%)
Total virtual hearings where the claimant attorney attended virtually:
hearings_attendees_pois.select{|h| h["attendees"].any?{|a| a["recorded_in_cis"] == true && a["attending_method"] == "Virtually" && a["hearing_role"] == "Claimant Representative/Attorney"}}.count
- Oct/2017: 37
- Nov/2017: 58
- Dec/2017: 268
- Jan/2018: 225
- TOTAL: 588 (17.13%)
Total virtual hearings where the carrier attorney/rep attended virtually:
hearings_attendees_pois.select{|h| h["attendees"].any?{|a| a["recorded_in_cis"] == true && a["attending_method"] == "Virtually" && a["hearing_role"] == "Carrier Representative/Attorney"}}.count
- Oct/2017: 70
- Nov/2017: 163
- Dec/2017: 557
- Jan/2018: 465
- TOTAL: 1255 (36.57%)
Total virtual hearings where the injured worker (claimant) attended virtually:
hearings_attendees_pois.select{|h| h["attendees"].any?{|a| a["recorded_in_cis"] == true && a["attending_method"] == "Virtually" && a["hearing_role"] == "Claimant"}}.count
- Oct/2017: 7
- Nov/2017: 16
- Dec/2017: 60
- Jan/2018: 51
- TOTAL: 134 (3.9%)
Total virtual hearings where witnesses or other participants attended virtually:*
hearings_attendees_pois.select{|h| h["attendees"].any?{|a| a["recorded_in_cis"] == true && a["attending_method"] == "Virtually" && a["hearing_role"] == "Witness/Other Participant"}}.count
- Oct/2017: 3
- Nov/2017: 7
- Dec/2017: 39
- Jan/2018: 38
- TOTAL: 87 (2.53%)
*This information is additional and not included in Tim's original request