API tools faq
paste
Advertisement
doranchak

Untitled

doranchak
Mar 18th, 2020
224
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 38.85 KB | None | 0 0
raw download clone embed print report
  1. execution count: 1
  2. avg time per execution: 73s
  3. query: update hwe.entity_attribute_value set _origin='report', updated_by=1,string_value=(select bond_description_ from #TempUpdate) where entity_attribute_id=-925640 and entity_id=20348714
  4.  
  5.  
  6. execution count: 2
  7. avg time per execution: 38s
  8. query: update hwe.entity_attribute_value set _origin='report', updated_by=1,string_value=(select bond_description_ from #TempUpdate) where entity_attribute_id=-925640 and entity_id=20348714
  9.  
  10.  
  11. execution count: 5
  12. avg time per execution: 32s
  13. query: update report.criminal_case_ set start_date_=@P0, new_leaf_date_=@P1, disposition_date_=@P2, filing_date_=@P3, primary_reference_id_=@P4, is_open_=@P5, name_=@P6, _origin=@P7, is_gang_case_=@P8, prosecutor_=@P9, conflict_review_date_=@P10, file_number_=@P11, description_=@P12, end_date_=@P13, cpcms_end_date_=@P14, created_time=@P15, updated_time=@P16, updated_by=@P17, created_by=@P18, court_numbers=@P19, is_vertical_case_=@P20, court_order_amount_=@P21, bond_amount_=@P22, bond_date_=@P23, bond_judge_=@P24, bond_court_=@P25, bond_count_=@P26, is_upgrade_case_=@P27, is_sealed_=@P28, is_superior_court_=@P29, bond_description_=@P30, is_bond_cash_=@P31, is_bond_posted_=@P32, is_bond_ptr_=@P33, is_bond10_=@P34, is_media_=@P35, speedy_trial_date_filed_=@P36, case_type_=@P37, weight_=@P38, criminal_status_=@P39, cpcms_criminal_status_=@P40, bond_=@P41, disposition_=@P42, phase_reason_=@P43, filing_category_=@P44, outcome_reason_=@P45, filing_cause_=@P46, outcome_=@P47, goal_=@P48, filing_type_=@P49, sub_type_=@P50, rb_reason_=@P51, phase_=@P52, goal_measure_=@P53, cp_judge_=@P54 where id=@P55
  14.  
  15.  
  16. execution count: 1
  17. avg time per execution: 29s
  18. query: SELECT TOP 10 SUBSTRING(qt.TEXT, (qs.statement_start_offset/2)+1, ((CASE qs.statement_end_offset WHEN -1 THEN DATALENGTH(qt.TEXT) ELSE qs.statement_end_offset END - qs.statement_start_offset)/2)+1), qs.execution_count, qs.total_logical_reads, qs.last_logical_reads, qs.total_logical_writes, qs.last_logical_writes, qs.total_worker_time, qs.last_worker_time, qs.total_elapsed_time/1000000 total_elapsed_time_in_S, qs.last_elapsed_time/1000000 last_elapsed_time_in_S, qs.last_execution_time, qp.query_plan FROM sys.dm_exec_query_stats qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp --ORDER BY qs.total_logical_reads DESC -- logical reads -- ORDER BY qs.total_logical_writes DESC -- logical writes ORDER BY qs.total_worker_time/execution_count DESC
  19.  
  20.  
  21. execution count: 1
  22. avg time per execution: 29s
  23. query: WITH query AS (select ROW_NUMBER() OVER (order by chargedao1_.source_ , chargedao1_.paragraph_) as __hibernate_row_nr__, casedefend0_.id as id782_0_, chargedao1_.id as id836_1_, trafficdef2_.id as id1139_2_, contemptde3_.id as id868_3_, criminalde4_.id as id882_4_, dependency5_.id as id910_5_, dependency6_.id as id912_6_, juvenileof7_.id as id1002_7_, casedefend0_.t$criminal_defendant_ as t2_782_0_, casedefend0_.t$contempt_defendant_ as t3_782_0_, casedefend0_.t$traffic_defendant_ as t4_782_0_, casedefend0_.t$dependency_case_ as t5_782_0_, casedefend0_.t$dependency_client_ as t6_782_0_, casedefend0_.t$juvenile_offender_ as t7_782_0_, casedefend0_.s$charge_ as s8_782_0_, casedefend0_.is_primary_ as is9_782_0_, casedefend0_.name_ as name10_782_0_, casedefend0_.created_time as created11_782_0_, casedefend0_.updated_time as updated12_782_0_, casedefend0_.updated_by as updated13_782_0_, casedefend0_._origin as column14_782_0_, casedefend0_.created_by as created15_782_0_, chargedao1_.disposition_ as disposit2_836_1_, chargedao1_.reporting_status_type_ as reporting3_836_1_, chargedao1_.law_code_ as law4_836_1_, chargedao1_.plea_ as plea5_836_1_, chargedao1_.weapon_ as weapon6_836_1_, chargedao1_.dismissal_type_ as dismissal7_836_1_, chargedao1_.drug_conviction_ as drug8_836_1_, chargedao1_.disposed_severity_ as disposed9_836_1_, chargedao1_.severity_ as severity10_836_1_, chargedao1_.category_ as category11_836_1_, chargedao1_.drug_sentence_ as drug12_836_1_, chargedao1_.reporting_status_ as reporting13_836_1_, chargedao1_.drug_offense_conviction_ as drug14_836_1_, chargedao1_.demand_type_ as demand15_836_1_, chargedao1_.conviction_weapon_ as conviction16_836_1_, chargedao1_.reporting_agency_ as reporting17_836_1_, chargedao1_.status_ as status18_836_1_, chargedao1_.type_ as type19_836_1_, chargedao1_.violation_start_date_ as violation20_836_1_, chargedao1_.comments_ as comments21_836_1_, chargedao1_.disposition_date_ as disposi22_836_1_, chargedao1_.date_filed_ as date23_836_1_, chargedao1_.time_filed_ as time24_836_1_, chargedao1_.paragraph_ as paragraph25_836_1_, chargedao1_.law_ as law26_836_1_, chargedao1_.violation_end_date_ as violation27_836_1_, chargedao1_.section_ as section28_836_1_, chargedao1_.degree_ as degree29_836_1_, chargedao1_.source_ as source30_836_1_, chargedao1_.created_time as created31_836_1_, chargedao1_.updated_time as updated32_836_1_, chargedao1_.updated_by as updated33_836_1_, chargedao1_._origin as column34_836_1_, chargedao1_.created_by as created35_836_1_, chargedao1_.offense_date_ as offense36_836_1_, chargedao1_.is_reduced_felony_ as is37_836_1_, chargedao1_.demand_date_ as demand38_836_1_, chargedao1_.agency_report_number_ as agency39_836_1_, chargedao1_.arresting_officer_ as arresting40_836_1_, chargedao1_.offense_time_ as offense41_836_1_, chargedao1_.demand_action_ as demand42_836_1_, chargedao1_.is_reporting_charge_ as is43_836_1_, chargedao1_.is_conviction_ as is44_836_1_, chargedao1_.offense_location_ as offense45_836_1_, chargedao1_.conviction_date_ as conviction46_836_1_, chargedao1_.number_ as number47_836_1_, chargedao1_.officer_comment_ as officer48_836_1_, chargedao1_.plea_date_ as plea49_836_1_, chargedao1_.officer_badge_number_ as officer50_836_1_, chargedao1_.drug_ as drug51_836_1_, trafficdef2_.s$person_ as s2_1139_2_, trafficdef2_.t$traffic_case_ as t3_1139_2_, trafficdef2_.is_primary_ as is4_1139_2_, trafficdef2_.name_ as name5_1139_2_, trafficdef2_.created_time as created6_1139_2_, trafficdef2_.updated_time as updated7_1139_2_, trafficdef2_.updated_by as updated8_1139_2_, trafficdef2_._origin as column9_1139_2_, trafficdef2_.created_by as created10_1139_2_, contemptde3_.role_ as role2_868_3_, contemptde3_.s$person_ as s3_868_3_, contemptde3_.t$contempt_case_ as t4_868_3_, contemptde3_.is_primary_ as is5_868_3_, contemptde3_.name_ as name6_868_3_, contemptde3_.created_time as created7_868_3_, contemptde3_.updated_time as updated8_868_3_, contemptde3_.updated_by as updated9_868_3_, contemptde3_._origin as column10_868_3_, contemptde3_.created_by as created11_868_3_, contemptde3_.defendant_status_ as defendant12_868_3_, criminalde4_.t$trial_case_ as t2_882_4_, criminalde4_.s$juvenile_person_ as s3_882_4_, criminalde4_.indicted_ as indicted4_882_4_, criminalde4_.custody_status_ as custody5_882_4_, criminalde4_.s$person_ as s6_882_4_, criminalde4_.s$organization_ as s7_882_4_, criminalde4_.t$criminal_case_ as t8_882_4_, criminalde4_.certification_ as certific9_882_4_, criminalde4_.bond_secured_by_ as bond10_882_4_, criminalde4_.interpreter_language_ as interpr11_882_4_, criminalde4_.approval_status_reason_ as approval12_882_4_, criminalde4_.defense_attorney_type1_ as defense13_882_4_, criminalde4_.bond_amount_ as bond14_882_4_, criminalde4_.defense_attorney_type2_ as defense15_882_4_, criminalde4_.defense_attorney2_ as defense16_882_4_, criminalde4_.arrest_date_ as arrest17_882_4_, criminalde4_.date_defender_appointed_ as date18_882_4_, criminalde4_.prop36_comment_ as prop19_882_4_, criminalde4_.processed_date_ as processed20_882_4_, criminalde4_.is_interpreter_needed_ as is21_882_4_, criminalde4_.name_ as name22_882_4_, criminalde4_.defense_attorney1_ as defense23_882_4_, criminalde4_.otn_ as otn24_882_4_, criminalde4_.is_primary_ as is25_882_4_, criminalde4_.bail_set_amount_ as bail26_882_4_, criminalde4_.is_prop36_eligible_ as is27_882_4_, criminalde4_.conflict_review_date_ as conflict28_882_4_, criminalde4_.probation_officer_phone_ as probation29_882_4_, criminalde4_.defendant_number_ as defendant30_882_4_, criminalde4_.bond_number_ as bond31_882_4_, criminalde4_.is_defender_appointed_ as is32_882_4_, criminalde4_.custody_date_ as custody33_882_4_, criminalde4_.custody_end_date_ as custody34_882_4_, criminalde4_.probation_officer_ as probation35_882_4_, criminalde4_.is_not_pd_defendant_ as is36_882_4_, criminalde4_.created_time as created37_882_4_, criminalde4_.updated_time as updated38_882_4_, criminalde4_.defender_names as defender39_882_4_, criminalde4_.custody_location_ as custody40_882_4_, criminalde4_.certification_date_ as certifi41_882_4_, criminalde4_.updated_by as updated42_882_4_, criminalde4_._origin as column43_882_4_, criminalde4_.created_by as created44_882_4_, criminalde4_.approval_status_ as approval45_882_4_, criminalde4_.approval_status_date_ as approval46_882_4_, criminalde4_.is_approval_override_ as is47_882_4_, criminalde4_.approved_denied_ as approved48_882_4_, criminalde4_.approval_override_reason_ as approval49_882_4_, criminalde4_.is_jail_request_ as is50_882_4_, dependency5_.prior_intervention_residence_ as prior2_910_5_, dependency5_.filing_cause_ as filing3_910_5_, dependency5_.disposition_ as disposit4_910_5_, dependency5_.filing_type_ as filing5_910_5_, dependency5_.filing_category_ as filing6_910_5_, dependency5_.case_type_ as case7_910_5_, dependency5_.detention_type_ as detention8_910_5_, dependency5_.weight_ as weight9_910_5_, dependency5_.dependency_status_ as dependency10_910_5_, dependency5_.outcome_ as outcome11_910_5_, dependency5_.start_date_ as start12_910_5_, dependency5_.family_number_ as family13_910_5_, dependency5_.disposition_date_ as disposi14_910_5_, dependency5_.filing_date_ as filing15_910_5_, dependency5_.file_number_ as file16_910_5_, dependency5_.primary_reference_id_ as primary17_910_5_, dependency5_.is_open_ as is18_910_5_, dependency5_.phase_ as phase19_910_5_, dependency5_.end_date_ as end20_910_5_, dependency5_.name_ as name21_910_5_, dependency5_.created_time as created22_910_5_, dependency5_.updated_time as updated23_910_5_, dependency5_.updated_by as updated24_910_5_, dependency5_.created_by as created25_910_5_, dependency5_.court_numbers as court26_910_5_, dependency5_.is_pre_adjudication_ as is27_910_5_, dependency5_.is_detained_ as is28_910_5_, dependency5_.is_sealed_ as is29_910_5_, dependency5_.dcfs_investigator_ as dcfs30_910_5_, dependency5_.description_ as descrip31_910_5_, dependency5_.is_media_ as is32_910_5_, dependency5_._origin as column33_910_5_, dependency6_.s$juvenile_person_ as s2_912_6_, dependency6_.relation_to_child_ as relation3_912_6_, dependency6_.legal_client_relationship_ as legal4_912_6_, dependency6_.s$person_ as s5_912_6_, dependency6_.t$dependency_case_ as t6_912_6_, dependency6_.s$family_ as s7_912_6_, dependency6_.is_primary_ as is8_912_6_, dependency6_.name_ as name9_912_6_, dependency6_.created_time as created10_912_6_, dependency6_.updated_time as updated11_912_6_, dependency6_.updated_by as updated12_912_6_, dependency6_._origin as column13_912_6_, dependency6_.created_by as created14_912_6_, juvenileof7_.release_condition_ as release2_1002_7_, juvenileof7_.custodial_status_ as custodial3_1002_7_, juvenileof7_.custody_status_ as custody4_1002_7_, juvenileof7_.t$delinquency_case_ as t5_1002_7_, juvenileof7_.s$person_ as s6_1002_7_, juvenileof7_.approval_status_ as approval7_1002_7_, juvenileof7_.interpreter_language_ as interpre8_1002_7_, juvenileof7_.otn_ as otn9_1002_7_, juvenileof7_.is_primary_ as is10_1002_7_, juvenileof7_.release_condition_note_ as release11_1002_7_, juvenileof7_.custodial_status_note_ as custodial12_1002_7_, juvenileof7_.arrest_date_ as arrest13_1002_7_, juvenileof7_.custody_date_ as custody14_1002_7_, juvenileof7_.processed_date_ as processed15_1002_7_, juvenileof7_.name_ as name16_1002_7_, juvenileof7_.is_not_pd_defendant_ as is17_1002_7_, juvenileof7_.release_date_ as release18_1002_7_, juvenileof7_.created_time as created19_1002_7_, juvenileof7_.updated_time as updated20_1002_7_, juvenileof7_.custody_location_ as custody21_1002_7_, juvenileof7_.approval_status_date_ as approval22_1002_7_, juvenileof7_.updated_by as updated23_1002_7_, juvenileof7_._origin as column24_1002_7_, juvenileof7_.created_by as created25_1002_7_ from report.case_defendant_to_charge_ casedefend0_ left outer join report.charge_ chargedao1_ on casedefend0_.s$charge_=chargedao1_.id left outer join report.traffic_defendant_ trafficdef2_ on casedefend0_.t$traffic_defendant_=trafficdef2_.id left outer join report.contempt_defendant_ contemptde3_ on casedefend0_.t$contempt_defendant_=contemptde3_.id left outer join report.criminal_defendant_ criminalde4_ on casedefend0_.t$criminal_defendant_=criminalde4_.id left outer join report.dependency_case_ dependency5_ on casedefend0_.t$dependency_case_=dependency5_.id left outer join report.dependency_client_ dependency6_ on casedefend0_.t$dependency_client_=dependency6_.id left outer join report.juvenile_offender_ juvenileof7_ on casedefend0_.t$juvenile_offender_=juvenileof7_.id ) SELECT * FROM query WHERE __hibernate_row_nr__ BETWEEN @P0 AND @P1
  24.  
  25.  
  26. execution count: 1
  27. avg time per execution: 16s
  28. query: select distinct a.* into hwe.appearance_overdue_mt1 from hwe_lehigh.report.appearance_ a left join hwe_lehigh.report.appearance_to_appearance_reas$ a2ar on a2ar.s$appearance_ = a.id left join hwe_lehigh.report.appearance_reason_ ar on ar.id = a2ar.t$appearance_reason_ where a.start_date_ < hwe_lehigh.hwe.csv_to_sorted_day(getdate()) and ar.status_ is null
  29.  
  30.  
  31. execution count: 1
  32. avg time per execution: 6s
  33. query: select tbl.* into hwe.attorney_caseload_open_mt1 from (select df.id as id , 'CriminalDefendant' as model_object_type_name , -889644 as model_object_type_id , hwe_lehigh.hwe.csv_sortable_name_2(defender_name.last_name_, defender_name.first_name_, defender_name.middle_name_, defender_name.suffix_pv) as defender_fullname , dd.id as defender_assignment_id , dd.s$person_ as person_id , -33056 as p_model_object_type_id , 'DefenderAssignment' as p_model_object_type_name , defendant.primary_reference_id_ as primary_reference_id , c.id as case_id , c.file_number_ as file_number , hwe_lehigh.hwe.csv_to_sorted_day_from_daytime(dd.assignment_start_date_) as assignment_start_date , hwe_lehigh.hwe.csv_to_sorted_day_from_daytime(dd.assignment_end_date_) as assignment_end_date , dd.assignment_end_date_ as assignment_end_date_raw , hwe_lehigh.hwe.csv_sortable_name_2(defendant_name.last_name_, defendant_name.first_name_, defendant_name.middle_name_, defendant_name.suffix_pv) as defendant_fullname , isnull(investigation_status.status,'Not requested') as investigation , df.custody_status_pv as custody_status , defendant.custody_location_ as custody_location , c.court_numbers as casecourt_numbers , pvphase.string_value as phase , next_appearance.appearance_id as appearance_id , hwe_lehigh.hwe.csv_to_day(next_appearance.start_date_) as appearance_start_date , hwe_lehigh.hwe.csv_to_time(next_appearance.start_time_) as appearance_start_time , next_appearance.start_time_ as appearance_start_time_raw , appearance_location_pv.string_value as appearance_location , dmc.section as section_of_max_charge , hwe_lehigh.hwe.csv_to_sorted_day(next_appearance.start_date_) as sortable_appearance_start_date , next_appearance.start_date_ as appearance_start_date_raw , pvcasetype.string_value as case_type , casestaff.s$person_ as staff_person_id , casestaff.id as case_staff_id , df.defendant_number_ as defendant_number /*HWE:8662*/ , dd.type_pv as role , null as facility /*HWE-9929*/ , null as active_defenders --neutered for now due to performance issues at Allegheny /*HWE:8662*/ from hwe_lehigh.hwe.v_defender_assignment_ dd join hwe_lehigh.hwe.v_criminal_defendant_ df on dd.t$criminal_defendant_ = df.id join hwe_lehigh.report.person_ defendant on defendant.id = df.s$person_ join hwe_lehigh.hwe.v_person_name_ defendant_name on defendant_name.id = defendant.primary_name_ join hwe_lehigh.report.criminal_case_ c on c.id = df.t$criminal_case_ join hwe_lehigh.report.person_ defender on defender.id = dd.s$person_ join hwe_lehigh.hwe.v_person_name_ defender_name on defender_name.id = defender.primary_name_ left join hwe_lehigh.report.criminal_staff_assignment_ casestaff on casestaff.t$criminal_case_=c.id left join hwe_lehigh.hwe.picklist_value pvcasetype on pvcasetype.id=c.filing_type_ left join hwe_lehigh.hwe.picklist_value pvphase on pvphase.id=c.phase_ left join hwe.all_appearance_next_mt1 next_appearance on next_appearance.defendant_id = df.id left join hwe.investigation_status_mt1 investigation_status on investigation_status.defendant_id = df.id left join hwe.defendant_max_charge_mt1 dmc on dmc.defendant_id = df.id left join hwe_lehigh.hwe.picklist_value appearance_location_pv on appearance_location_pv.id = next_appearance.location_ where c.is_open_ = 1 union all select c.id as id , 'DelinquencyCase' as model_object_type_name , -112261 as model_object_type_id , hwe_lehigh.hwe.csv_sortable_name_2(defender_name.last_name_, defender_name.first_name_, defender_name.middle_name_, defender_name.suffix_pv) as defender_fullname , dd.id as defender_assignment_id , dd.s$person_ as person_id , -889604 as p_model_object_type_id , 'DelinquencyStaffAssignment' as p_model_object_type_name , defendant.primary_reference_id_ as primary_reference_id , c.id as case_id , c.file_number_ as file_number , hwe_lehigh.hwe.csv_to_sorted_day_from_daytime(dd.assignment_start_date_) as assignment_start_date , hwe_lehigh.hwe.csv_to_sorted_day_from_daytime(dd.assignment_end_date_) as assignment_end_date , dd.assignment_end_date_ as assignment_end_date_raw , hwe_lehigh.hwe.csv_sortable_name_2(defendant_name.last_name_, defendant_name.first_name_, defendant_name.middle_name_, defendant_name.suffix_pv) as defendant_fullname , isnull(investigation_status.status,'Not requested') as investigation , null as custody_status , defendant.custody_location_ as custody_location --, c.court_numbers as casecourt_numbers , c.primary_reference_id_ as casecourt_numbers/*HWE-9399*/ , pvphase.string_value as phase , next_appearance.appearance_id as appearance_id , hwe_lehigh.hwe.csv_to_day(next_appearance.start_date_) as appearance_start_date , hwe_lehigh.hwe.csv_to_time(next_appearance.start_time_) as appearance_start_time , next_appearance.start_time_ as appearance_start_time_raw , appearance_location_pv.string_value as appearance_location , dmc.section as section_of_max_charge , hwe_lehigh.hwe.csv_to_sorted_day(next_appearance.start_date_) as sortable_appearance_start_date , next_appearance.start_date_ as appearance_start_date_raw , pvcasetype.string_value as case_type , casestaff.s$person_ as staff_person_id , casestaff.id as case_staff_id , null as defendant_number /*HWE:8662*/ , role_pv.string_value as role , null as facility /*HWE-9929*/ , null as active_defenders --neutered for now due to performance issues at Allegheny /*HWE:8662*/ from hwe_lehigh.report.juvenile_offender_ df join hwe_lehigh.report.delinquency_case_ c on c.id = df.t$delinquency_case_ join hwe_lehigh.report.delinquency_staff_assignment_ dd on dd.t$delinquency_case_ = c.id join hwe_lehigh.report.juvenile_person_ defendant on defendant.id = df.s$juvenile_person_ join hwe_lehigh.hwe.v_person_name_ defendant_name on defendant_name.id = defendant.primary_name_ join hwe_lehigh.report.person_ defender on defender.id = dd.s$person_ join hwe_lehigh.hwe.v_person_name_ defender_name on defender_name.id = defender.primary_name_ left join hwe_lehigh.report.delinquency_staff_assignment_ casestaff on casestaff.t$delinquency_case_=c.id left join hwe_lehigh.hwe.picklist_value pvcasetype on pvcasetype.id=c.filing_type_ left join hwe_lehigh.hwe.picklist_value pvphase on pvphase.id=c.phase_ left join hwe.all_appearance_next_mt1 next_appearance on next_appearance.defendant_id = df.id left join hwe.investigation_status_mt1 investigation_status on investigation_status.defendant_id = df.id left join hwe.defendant_max_charge_mt1 dmc on dmc.defendant_id = df.id left join hwe_lehigh.hwe.picklist_value appearance_location_pv on appearance_location_pv.id = next_appearance.location_ left join hwe_lehigh.hwe.picklist_value role_pv on role_pv.id=dd.role_ where c.is_open_ = 1 and (dd.assignment_end_date_ is null or dd.assignment_end_date_ = '') -- adding for juvenile_offenders with source = person instead of juvenile_person (see HWE-10445) union all select c.id as id , 'DelinquencyCase' as model_object_type_name , -112261 as model_object_type_id , hwe_lehigh.hwe.csv_sortable_name_2(defender_name.last_name_, defender_name.first_name_, defender_name.middle_name_, defender_name.suffix_pv) as defender_fullname , dd.id as defender_assignment_id , dd.s$person_ as person_id , -889604 as p_model_object_type_id , 'DelinquencyStaffAssignment' as p_model_object_type_name , defendant.primary_reference_id_ as primary_reference_id , c.id as case_id , c.file_number_ as file_number , hwe_lehigh.hwe.csv_to_sorted_day_from_daytime(dd.assignment_start_date_) as assignment_start_date , hwe_lehigh.hwe.csv_to_sorted_day_from_daytime(dd.assignment_end_date_) as assignment_end_date , dd.assignment_end_date_ as assignment_end_date_raw , hwe_lehigh.hwe.csv_sortable_name_2(defendant_name.last_name_, defendant_name.first_name_, defendant_name.middle_name_, defendant_name.suffix_pv) as defendant_fullname , isnull(investigation_status.status,'Not requested') as investigation , null as custody_status , defendant.custody_location_ as custody_location --, c.court_numbers as casecourt_numbers , c.primary_reference_id_ as casecourt_numbers/*HWE-9399*/ , pvphase.string_value as phase , next_appearance.appearance_id as appearance_id , hwe_lehigh.hwe.csv_to_day(next_appearance.start_date_) as appearance_start_date , hwe_lehigh.hwe.csv_to_time(next_appearance.start_time_) as appearance_start_time , next_appearance.start_time_ as appearance_start_time_raw , appearance_location_pv.string_value as appearance_location , dmc.section as section_of_max_charge , hwe_lehigh.hwe.csv_to_sorted_day(next_appearance.start_date_) as sortable_appearance_start_date , next_appearance.start_date_ as appearance_start_date_raw , pvcasetype.string_value as case_type , casestaff.s$person_ as staff_person_id , casestaff.id as case_staff_id , null as defendant_number /*HWE:8662*/ , role_pv.string_value as role , null as facility /*HWE-9929*/ , null as active_defenders --neutered for now due to performance issues at Allegheny /*HWE:8662*/ from hwe_lehigh.report.juvenile_offender_ df join hwe_lehigh.report.delinquency_case_ c on c.id = df.t$delinquency_case_ join hwe_lehigh.report.delinquency_staff_assignment_ dd on dd.t$delinquency_case_ = c.id join hwe_lehigh.report.person_ defendant on defendant.id = df.s$person_ join hwe_lehigh.hwe.v_person_name_ defendant_name on defendant_name.id = defendant.primary_name_ join hwe_lehigh.report.person_ defender on defender.id = dd.s$person_ join hwe_lehigh.hwe.v_person_name_ defender_name on defender_name.id = defender.primary_name_ left join hwe_lehigh.report.delinquency_staff_assignment_ casestaff on casestaff.t$delinquency_case_=c.id left join hwe_lehigh.hwe.picklist_value pvcasetype on pvcasetype.id=c.filing_type_ left join hwe_lehigh.hwe.picklist_value pvphase on pvphase.id=c.phase_ left join hwe.all_appearance_next_mt1 next_appearance on next_appearance.defendant_id = df.id left join hwe.investigation_status_mt1 investigation_status on investigation_status.defendant_id = df.id left join hwe.defendant_max_charge_mt1 dmc on dmc.defendant_id = df.id left join hwe_lehigh.hwe.picklist_value appearance_location_pv on appearance_location_pv.id = next_appearance.location_ left join hwe_lehigh.hwe.picklist_value role_pv on role_pv.id=dd.role_ where c.is_open_ = 1 and (dd.assignment_end_date_ is null or dd.assignment_end_date_ = '') union all select c.id as id , 'DependencyCase' as model_object_type_name , -112261 as model_object_type_id , hwe_lehigh.hwe.csv_sortable_name_2(defender_name.last_name_, defender_name.first_name_, defender_name.middle_name_, defender_name.suffix_pv) as defender_fullname , dd.id as defender_assignment_id , dd.s$person_ as person_id , -903276 as p_model_object_type_id , 'CaseStaffAssignment' as p_model_object_type_name , defendant.primary_reference_id_ as primary_reference_id , c.id as case_id , c.file_number_ as file_number , hwe_lehigh.hwe.csv_to_sorted_day_from_daytime(dd.assignment_start_date_) as assignment_start_date , hwe_lehigh.hwe.csv_to_sorted_day_from_daytime(dd.assignment_end_date_) as assignment_end_date , dd.assignment_end_date_ as assignment_end_date_raw , hwe_lehigh.hwe.csv_sortable_name_2(defendant_name.last_name_, defendant_name.first_name_, defendant_name.middle_name_, defendant_name.suffix_pv) as defendant_fullname , isnull(investigation_status.status,'Not requested') as investigation , null as custody_status , defendant.custody_location_ as custody_location , c.court_numbers as casecourt_numbers , pvphase.string_value as phase , next_appearance.appearance_id as appearance_id , hwe_lehigh.hwe.csv_to_day(next_appearance.start_date_) as appearance_start_date , hwe_lehigh.hwe.csv_to_time(next_appearance.start_time_) as appearance_start_time , next_appearance.start_time_ as appearance_start_time_raw , appearance_location_pv.string_value as appearance_location , dmc.section as section_of_max_charge , hwe_lehigh.hwe.csv_to_sorted_day(next_appearance.start_date_) as sortable_appearance_start_date , next_appearance.start_date_ as appearance_start_date_raw , pvcasetype.string_value as case_type , casestaff.s$person_ as staff_person_id , casestaff.id as case_staff_id , null as defendant_number /*HWE:8662*/ , role_pv.string_value as role , null as facility /*HWE-9929*/ , null as active_defenders --neutered for now due to performance issues at Allegheny /*HWE:8662*/ from hwe_lehigh.report.dependency_client_ df join hwe_lehigh.report.dependency_case_ c on c.id = df.t$dependency_case_ join hwe_lehigh.report.case_staff_assignment_ dd on dd.t$dependency_case_ = c.id join hwe_lehigh.report.person_ defendant on defendant.id = df.s$person_ join hwe_lehigh.hwe.v_person_name_ defendant_name on defendant_name.id = defendant.primary_name_ join hwe_lehigh.report.person_ defender on defender.id = dd.s$person_ join hwe_lehigh.hwe.v_person_name_ defender_name on defender_name.id = defender.primary_name_ left join hwe_lehigh.report.case_staff_assignment_ casestaff on casestaff.t$dependency_case_=c.id left join hwe_lehigh.hwe.picklist_value pvcasetype on pvcasetype.id=c.filing_type_ left join hwe_lehigh.hwe.picklist_value pvphase on pvphase.id=c.phase_ left join hwe.all_appearance_next_mt1 next_appearance on next_appearance.defendant_id = df.id left join hwe.investigation_status_mt1 investigation_status on investigation_status.defendant_id = df.id left join hwe.defendant_max_charge_mt1 dmc on dmc.defendant_id = df.id left join hwe_lehigh.hwe.picklist_value appearance_location_pv on appearance_location_pv.id = next_appearance.location_ left join hwe_lehigh.hwe.picklist_value role_pv on role_pv.id=dd.role_ where c.is_open_ = 1 and (dd.assignment_end_date_ is null or dd.assignment_end_date_ = '') union all select c.id as id , 'MentalHealthMatter' as model_object_type_name , -902462 as model_object_type_id , hwe_lehigh.hwe.csv_sortable_name_2(defender_name.last_name_, defender_name.first_name_, defender_name.middle_name_, defender_name.suffix_pv) as defender_fullname , dd.id as defender_assignment_id , dd.s$person_ as person_id , -903276 as p_model_object_type_id , 'CaseStaffAssignment' as p_model_object_type_name , defendant.primary_reference_id_ as primary_reference_id , c.id as case_id , c.file_number_ as file_number , hwe_lehigh.hwe.csv_to_sorted_day_from_daytime(dd.assignment_start_date_) as assignment_start_date , hwe_lehigh.hwe.csv_to_sorted_day_from_daytime(dd.assignment_end_date_) as assignment_end_date , dd.assignment_end_date_ as assignment_end_date_raw , hwe_lehigh.hwe.csv_sortable_name_2(defendant_name.last_name_, defendant_name.first_name_, defendant_name.middle_name_, defendant_name.suffix_pv) as defendant_fullname , isnull(investigation_status.status,'Not requested') as investigation , null as custody_status , defendant.custody_location_ as custody_location , c.primary_reference_id_ as casecourt_numbers/*HWE-9079*/ , pvphase.string_value as phase , next_appearance.appearance_id as appearance_id , hwe_lehigh.hwe.csv_to_day(next_appearance.start_date_) as appearance_start_date , hwe_lehigh.hwe.csv_to_time(next_appearance.start_time_) as appearance_start_time , next_appearance.start_time_ as appearance_start_time_raw , appearance_location_pv.string_value as appearance_location , dmc.section as section_of_max_charge , hwe_lehigh.hwe.csv_to_sorted_day(next_appearance.start_date_) as sortable_appearance_start_date , next_appearance.start_date_ as appearance_start_date_raw , pvcasetype.string_value as case_type , casestaff.s$person_ as staff_person_id , casestaff.id as case_staff_id , null as defendant_number /*HWE:8662*/ , role_pv.string_value as role , facil.name_ as facility /*HWE-9929*/ , null as active_defenders --neutered for now due to performance issues at Allegheny /*HWE:8662*/ from hwe_lehigh.report.mental_health_patient_ df join hwe_lehigh.report.mental_health_matter_ c on c.id = df.t$mental_health_matter_ join hwe_lehigh.report.case_staff_assignment_ dd on dd.t$mental_health_matter_ = c.id join hwe_lehigh.report.person_ defendant on defendant.id = df.s$person_ join hwe_lehigh.hwe.v_person_name_ defendant_name on defendant_name.id = defendant.primary_name_ join hwe_lehigh.report.person_ defender on defender.id = dd.s$person_ join hwe_lehigh.hwe.v_person_name_ defender_name on defender_name.id = defender.primary_name_ left join hwe_lehigh.report.case_staff_assignment_ casestaff on casestaff.t$mental_health_matter_=c.id left join hwe_lehigh.hwe.picklist_value pvcasetype on pvcasetype.id=c.filing_type_ left join hwe_lehigh.hwe.picklist_value pvphase on pvphase.id=c.phase_ left join hwe_lehigh.report.mental_health_to_facility_ mhta on mhta.t$mental_health_matter_ = c.id left join hwe_lehigh.report.health_care_facility_ facil on facil.id = mhta.s$health_care_facility_ left join hwe.all_appearance_next_mt1 next_appearance on next_appearance.defendant_id = df.id left join hwe.investigation_status_mt1 investigation_status on investigation_status.defendant_id = df.id left join hwe.defendant_max_charge_mt1 dmc on dmc.defendant_id = df.id left join hwe_lehigh.hwe.picklist_value appearance_location_pv on appearance_location_pv.id = next_appearance.location_ left join hwe_lehigh.hwe.picklist_value role_pv on role_pv.id=dd.role_ where c.is_open_ = 1 and (dd.assignment_end_date_ is null or dd.assignment_end_date_ = '') union all select c.id as id , 'ContemptCase' as model_object_type_name , -902819 as model_object_type_id , hwe_lehigh.hwe.csv_sortable_name_2(defender_name.last_name_, defender_name.first_name_, defender_name.middle_name_, defender_name.suffix_pv) as defender_fullname , dd.id as defender_assignment_id , dd.s$person_ as person_id , -903276 as p_model_object_type_id , 'CaseStaffAssignment' as p_model_object_type_name , defendant.primary_reference_id_ as primary_reference_id , c.id as case_id , c.file_number_ as file_number , hwe_lehigh.hwe.csv_to_sorted_day_from_daytime(dd.assignment_start_date_) as assignment_start_date , hwe_lehigh.hwe.csv_to_sorted_day_from_daytime(dd.assignment_end_date_) as assignment_end_date , dd.assignment_end_date_ as assignment_end_date_raw , hwe_lehigh.hwe.csv_sortable_name_2(defendant_name.last_name_, defendant_name.first_name_, defendant_name.middle_name_, defendant_name.suffix_pv) as defendant_fullname , isnull(investigation_status.status,'Not requested') as investigation , null as custody_status , defendant.custody_location_ as custody_location , c.primary_reference_id_ as casecourt_numbers/*HWE-9079*/ , pvphase.string_value as phase , next_appearance.appearance_id as appearance_id , hwe_lehigh.hwe.csv_to_day(next_appearance.start_date_) as appearance_start_date , hwe_lehigh.hwe.csv_to_time(next_appearance.start_time_) as appearance_start_time , next_appearance.start_time_ as appearance_start_time_raw , appearance_location_pv.string_value as appearance_location , dmc.section as section_of_max_charge , hwe_lehigh.hwe.csv_to_sorted_day(next_appearance.start_date_) as sortable_appearance_start_date , next_appearance.start_date_ as appearance_start_date_raw , pvcasetype.string_value as case_type , casestaff.s$person_ as staff_person_id , casestaff.id as case_staff_id , null as defendant_number /*HWE:8662*/ , role_pv.string_value as role , null as facility /*HWE-9929*/ , null as active_defenders --neutered for now due to performance issues at Allegheny /*HWE:8662*/ from hwe_lehigh.report.contempt_defendant_ df join hwe_lehigh.report.contempt_case_ c on c.id = df.t$contempt_case_ join hwe_lehigh.report.case_staff_assignment_ dd on dd.t$contempt_case_ = c.id join hwe_lehigh.report.person_ defendant on defendant.id = isnull(df.s$person_, df.s$juvenile_person_) join hwe_lehigh.hwe.v_person_name_ defendant_name on defendant_name.id = defendant.primary_name_ join hwe_lehigh.report.person_ defender on defender.id = dd.s$person_ join hwe_lehigh.hwe.v_person_name_ defender_name on defender_name.id = defender.primary_name_ left join hwe_lehigh.report.case_staff_assignment_ casestaff on casestaff.t$contempt_case_=c.id left join hwe_lehigh.hwe.picklist_value pvcasetype on pvcasetype.id=c.filing_type_ left join hwe_lehigh.hwe.picklist_value pvphase on pvphase.id=c.phase_ left join hwe.all_appearance_next_mt1 next_appearance on next_appearance.defendant_id = df.id left join hwe.investigation_status_mt1 investigation_status on investigation_status.defendant_id = df.id left join hwe.defendant_max_charge_mt1 dmc on dmc.defendant_id = df.id left join hwe_lehigh.hwe.picklist_value appearance_location_pv on appearance_location_pv.id = next_appearance.location_ left join hwe_lehigh.hwe.picklist_value role_pv on role_pv.id=dd.role_ where c.is_open_ = 1 and (dd.assignment_end_date_ is null or dd.assignment_end_date_ = '') union all select c.id as id , 'TrafficCase' as model_object_type_name , -902771 as model_object_type_id , hwe_lehigh.hwe.csv_sortable_name_2(defender_name.last_name_, defender_name.first_name_, defender_name.middle_name_, defender_name.suffix_pv) as defender_fullname , dd.id as defender_assignment_id , dd.s$person_ as person_id , -903276 as p_model_object_type_id , 'CaseStaffAssignment' as p_model_object_type_name , defendant.primary_reference_id_ as primary_reference_id , c.id as case_id , c.file_number_ as file_number , hwe_lehigh.hwe.csv_to_sorted_day_from_daytime(dd.assignment_start_date_) as assignment_start_date , hwe_lehigh.hwe.csv_to_sorted_day_from_daytime(dd.assignment_end_date_) as assignment_end_date , dd.assignment_end_date_ as assignment_end_date_raw , hwe_lehigh.hwe.csv_sortable_name_2(defendant_name.last_name_, defendant_name.first_name_, defendant_name.middle_name_, defendant_name.suffix_pv) as defendant_fullname , isnull(investigation_status.status,'Not requested') as investigation , null as custody_status , defendant.custody_location_ as custody_location , c.primary_reference_id_ as casecourt_numbers/*HWE-9079*/ , pvphase.string_value as phase , next_appearance.appearance_id as appearance_id , hwe_lehigh.hwe.csv_to_day(next_appearance.start_date_) as appearance_start_date , hwe_lehigh.hwe.csv_to_time(next_appearance.start_time_) as appearance_start_time , next_appearance.start_time_ as appearance_start_time_raw , appearance_location_pv.string_value as appearance_location , dmc.section as section_of_max_charge , hwe_lehigh.hwe.csv_to_sorted_day(next_appearance.start_date_) as sortable_appearance_start_date , next_appearance.start_date_ as appearance_start_date_raw , pvcasetype.string_value as case_type , casestaff.s$person_ as staff_person_id , casestaff.id as case_staff_id , null as defendant_number /*HWE:8662*/ , role_pv.string_value as role , null as facility /*HWE-9929*/ , null as active_defenders --neutered for now due to performance issues at Allegheny /*HWE:8662*/ from hwe_lehigh.report.traffic_defendant_ df join hwe_lehigh.report.traffic_case_ c on c.id = df.t$traffic_case_ join hwe_lehigh.report.case_staff_assignment_ dd on dd.t$traffic_case_ = c.id join hwe_lehigh.report.person_ defendant on defendant.id = isnull(df.s$person_, df.s$juvenile_person_) join hwe_lehigh.hwe.v_person_name_ defendant_name on defendant_name.id = defendant.primary_name_ join hwe_lehigh.report.person_ defender on defender.id = dd.s$person_ join hwe_lehigh.hwe.v_person_name_ defender_name on defender_name.id = defender.primary_name_ left join hwe_lehigh.report.case_staff_assignment_ casestaff on casestaff.t$traffic_case_=c.id left join hwe_lehigh.hwe.picklist_value pvcasetype on pvcasetype.id=c.filing_type_ left join hwe_lehigh.hwe.picklist_value pvphase on pvphase.id=c.phase_ left join hwe.all_appearance_next_mt1 next_appearance on next_appearance.defendant_id = df.id left join hwe.investigation_status_mt1 investigation_status on investigation_status.defendant_id = df.id left join hwe.defendant_max_charge_mt1 dmc on dmc.defendant_id = df.id left join hwe_lehigh.hwe.picklist_value appearance_location_pv on appearance_location_pv.id = next_appearance.location_ left join hwe_lehigh.hwe.picklist_value role_pv on role_pv.id=dd.role_ where c.is_open_ = 1 and (dd.assignment_end_date_ is null or dd.assignment_end_date_ = '') ) tbl
  34.  
  35.  
  36. execution count: 1
  37. avg time per execution: 5s
  38. query: select count(*) as col_0_0_ from hwe.my_appearances myappearan0_ where (((myappearan0_.person_id=@P0 )and(myappearan0_.person_id is not null )or(myappearan0_.person_org_id=@P1 )and(myappearan0_.person_org_id is not null )))
  39.  
  40.  
  41. execution count: 3
  42. avg time per execution: 5s
  43. query: select * into hwe.prosecutors_mt1 from hwe_lehigh.hwe.minimal_person where type in ('Prosecutor','Defender','Law Enforcement')
  44.  
  45.  
  46. execution count: 1
  47. avg time per execution: 4s
  48. query: select * into hwe.appearance_future_mt1 from hwe_lehigh.report.appearance_ where start_date_ >= hwe_lehigh.hwe.csv_to_sorted_day(getdate())
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement
Public Pastes
Advertisement
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy.  OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!