select a.acdyear_number as agencyID, c.sri as locSystem,
  case
      when substr(c.sri,1,1)='I'
      then 'I'
      when substr(c.sri,1,1)='U'
      then 'US'
      when substr(c.sri,1,1)='S'
      then 'SR'
      when substr(c.sri,1,1)='L'
      then 'L'
  end as routeType,
  c.county as County,  c.mp_accident as locOffset, c.acdtime as accidentTime,
  case
      when c.severity='1'
      then 'K'
      when c.severity ='2'
      then 'A'
      when c.severity ='3'
      then 'B'
      when  c.severity ='4'
      then 'C'
      when  c.propdamageonly ='X'
      then 'P'
      when c.severity = '5'
      then 'X'
  end as accidentSeverity1,
   (c.totalfatals + c.pedfatals) as numberOfFatalities, 
   (c.totalinjury + c.pedinjury) as numberOfInjuries, 
    b.unit_nbr as Vehicle_Number, b.unit_type as Vehicle_Type, 
    b.driver_condition, b.vehicle_action, b.vehicle_type, 
    b.operator_dob, b. arithmetic_operator_dob, 
    c.acddate as Day_of_Calender_Year, c.acdyear as Year,  
    c.severity, c.numvehicles, c.locfirstevent, c.surfacecondition, 
    c.lighting, c.weather, c.totalfatals, c.totalinjury, c.pedfatals, c.pedinjury
  
	FROM acdi_persons_loc a
  inner join acdu_vehicles_loc b
	ON (a.acdyear_number = b.acdyear_number
     and a.occupiedcode = b.unit_nbr)
  inner join acdl_crash_loc c
  on (c.acdyear_number = b.acdyear_number)
     where a.positioncode=1 
 ;
 
 