SHARE
TWEET

Untitled

doranchak Feb 21st, 2020 77 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. exec hwe.drop_view "trial_list"
  2. go
  3. print 'creating view trial_list'
  4. go
  5. create view trial_list AS
  6. select distinct c.id
  7. , c.file_number_ as file_number
  8. , c.name_ as case_name
  9. , case when cd.s$organization_ is null then
  10.         (select case when pn.last_name_ is null then '' else coalesce(pn.last_name_,'')+' '+isnull(pv_sufix.string_value,'')+', '+coalesce(pn.first_name_,'')+' '+coalesce(pn.middle_name_,'') end as defendant_name from
  11.         report.person_ prep join report.person_name_ pn on prep.primary_name_=pn.id
  12.         left join picklist_value pv_sufix on pv_sufix.id=pn.suffix_
  13.         where prep.id=cd.s$person_)
  14.     else
  15.         (select o.name_ from report.organization_ o where o.id=cd.s$organization_)
  16.     end as defendant_name
  17. , cd.id as defendant_id
  18. , coalesce(cd.s$person_ ,cd.s$organization_) as defendant_person_id
  19. , case when pn.last_name_ is null then '' else isnull(pn.last_name_,'')+' '+isnull(pv_sufix.string_value,'')+', '+isnull(pn.first_name_,'')+' '+isnull(pn.middle_name_,'') end as assigned_attorney
  20. , case when opc.s$organization_ is null then
  21.         (select coalesce(pn.last_name_,'')+','+coalesce(pn.first_name_,'')+' '+coalesce(pn.middle_name_,'') from
  22.         report.person_ prep join report.person_name_ pn on prep.primary_name_=pn.id
  23.         where prep.id=opc.s$person_)
  24.     else
  25.         (select o.name_ from report.organization_ o where o.id=opc.s$organization_)
  26.     end as opposing_counsel_name
  27. , cd.otn_ as otn
  28. , p.id as assigned_attorney_id
  29. , c.start_date_ as start_date
  30. , c.end_date_ as end_date
  31. , '' as disposition
  32. , case when copn.last_name_ is null then '' else isnull(copn.last_name_,'')+' '+isnull(copv_sufix.string_value,'')+', '+isnull(copn.first_name_,'')+' '+isnull(copn.middle_name_,'') end as arresting_officer
  33. , hwe.le_get_section_of_max_charge_only_description(cd.id) as violation
  34. , hwe.le_get_section_of_max_charge(cd.id) as grade
  35. , case when pv_cust.string_value='In Custody' then cust_loc.name else pv_cust.string_value end as jail_bail
  36. , c.description_ as offer
  37. , '' as ada_notes
  38. , cc.court_number_ as court_number
  39. , (select top 1 app.start_date from schedule_appearance app where app.reason like '%arr%' and defendant_id=cd.id)
  40.  as ARRAIGNMENT_DATE
  41.  , (select top 1 app.start_date from schedule_appearance app where (app.reason like 'Pretrial%' or app.reason like '%PTC%') and defendant_id=cd.id)
  42.  as PTC_DATE
  43.  , (select top 1 app.start_date from schedule_appearance app where app.reason like '%Call%' and defendant_id=cd.id)
  44.  as CALL_DATE
  45.   , (select top 1 app.start_date from schedule_appearance app where app.reason like 'TRIAL%' and defendant_id=cd.id)
  46.  as TRIAL_MONTH
  47. ,
  48. (
  49. (select top 1 e.start_date_
  50. from report.event_ e
  51. join report.case_to_event_ ce on ce.s$event_=e.id
  52. where e.type_ = (select pv.id from picklist_value pv
  53. where pv.string_value='RULE600' and pv.picklist_id = (select p.id from picklist p where p.name='EventType'))
  54. and coalesce(ce.t$collection_case_,t$traffic_case_,t$delinquency_case_,t$criminal_case_,t$mental_health_matter_,t$forfeiture_case_,t$contempt_case_,t$dependency_case_,t$litigation_matter_,t$general_matter_,t$contract_matter_,t$claim_) = c.id order by e.start_date_ desc
  55. )
  56. ) as EXPIRATION180
  57. ,
  58. (
  59. (select top 1 e.start_date_
  60. from report.event_ e
  61. join report.case_to_event_ ce on ce.s$event_=e.id
  62. where e.type_ = (select pv.id from picklist_value pv
  63. where pv.string_value='RULE600b' and pv.picklist_id = (select p.id from picklist p where p.name='EventType'))
  64. and coalesce(ce.t$collection_case_,t$traffic_case_,t$delinquency_case_,t$criminal_case_,t$mental_health_matter_,t$forfeiture_case_,t$contempt_case_,t$dependency_case_,t$litigation_matter_,t$general_matter_,t$contract_matter_,t$claim_) = c.id  order by e.start_date_ desc
  65. )
  66. ) as EXPIRATION365
  67. from
  68. report.criminal_case_ c
  69. left join report.criminal_defendant_ cd on cd.t$criminal_case_=c.id
  70. left join picklist_value pv_cust on pv_cust.id=cd.custody_status_
  71. left join organization cust_loc on cust_loc.id=cd.custody_location_
  72. left join report.defender_assignment_ da on da.t$criminal_defendant_=cd.id and da.is_primary_=1 and da.assignment_end_date_ is null
  73. left join report.person_ p on da.s$person_=p.id
  74. left join report.person_name_ pn on pn.id=p.primary_name_
  75. left join picklist_value pv_sufix on pv_sufix.id=pn.suffix_
  76. left join report.case_officer_ co on co.t$criminal_case_=c.id and co.is_arresting_ =1
  77. left join report.person_ cop on co.s$person_=cop.id
  78. left join report.person_name_ copn on copn.id=cop.primary_name_
  79. left join picklist_value copv_sufix on copv_sufix.id=copn.suffix_
  80. left join report.opposing_counsel_ opc on opc.t$criminal_case_ is not null and opc.t$criminal_case_ = c.id and opc.id = (select max(id) from
  81. report.opposing_counsel_ r2 where r2.t$criminal_case_ is not null and r2.t$criminal_case_ = c.id and r2.end_assign_date_ is null)
  82. left join report.case_court_ cc on cc.t$criminal_case_ = c.id and cc.is_current_court_=1
  83. go
RAW Paste Data
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
Top