Data Lake - View or Table for the Offense's vehicle association

Good afternoon, 

If you are referring to vehicle status like victim vehicle, stolen, recovered etc...I found it under vw_property_statuses.status

Not the field, I’m looking for, but this picture will probably explain best and thanks for the quick response.  I’ll explain why I need the Offense to Vehicle link.  If there’s only 1 offense and 1 vehicle, no issue.  However, when there are let’s say 2 offenses, NIBRS 240 for stolen vehicle and NIBRS 290 for Vandalism, I’m trying to only target the 240 stolen vehicles.  However, for NIBRS 240 and per NIBRS rules, you can also use Destroyed Damage and Burned and not necessarily just Stolen and Recovered in the status field that you mentioned, which I am using in this custom report.  In this scenario, let’s say both vehicles are Destroyed Damage.  One is for the NIBRS 240 and the other for the NIBRS 290.  If I isolate based on the Offense/Vehicle link section as seen in the 2nd section below, I can isolate the true NIBRS 240 and not show both as NIBRS 240.  Separately, I have a different issue that I’ll need to isolate.  I would like Attempted NIBRS 240 to also show on my report.  Unfortunately, the M43 coding does not require a Offense/Vehicle link on attempted NIBRS 240, so I’ll need to figure that out also.

This is the same table, but isolated to only charges that are NIBRS 240.  Hopefully I’m understanding what you’re needing.

SELECT
	ip.owner_id AS [report_id],
	iv.item_id AS [item_profile_id],
	ps.property_status_attr_id,
	ps.offense_id AS [offense_id]
FROM
	RMS.item_vehicles iv
	JOIN RMS.item_profiles ip ON ip.id = iv.item_id
	JOIN RMS.property_statuses ps ON ps.item_profile_id = iv.item_id
	JOIN RMS.offenses o ON o.id = ps.offense_id
	JOIN RMS.offense_codes oc ON oc.id = o.offense_code_id
	JOIN REF.nibrs_offense_codes noc ON noc.id = oc.nibrs_offense_code_id
WHERE 
	ps.offense_id IS NOT NULL
	AND noc.code = '240'

Here’s what I’m working with so far and just need to isolated the “LINK VEHICLES” input by users:
 

/*----------------------To speed query, no filter on status or dates yet only NIBRS code---------------------*/
IF OBJECT_ID('tempdb..#TempStolen01') IS NOT NULL DROP TABLE #TempStolen01

SELECT *
INTO #TempStolen01
FROM (
SELECT 
   O.reporting_event_number
   ,O.offense_location_subdivision_1
   ,E.event_start_date
   ,O.offense_start_date
   ,O.offense_end_date
   ,O.was_completed
   ,IIF(O.offense_start_date IS NULL,'Unknown','') AS OffStartDateUnk
 
   ,IIF(O.offense_location_street_number<> '' OR O.offense_location_street_number IS NOT NULL,O.offense_location_street_number+' '+O.offense_location_street_name,O.offense_location_street_name) AS Location
         --,O.offense_location_street_number
         --,O.offense_location_street_name
   ,O.offense_id
   ,O.report_id
   ,O.record_number
   ,O.nibrs_code
FROM 
   analytics_rms.vw_report_offenses AS O
   LEFT OUTER JOIN analytics_rms.vw_report_event_infos AS E ON O.report_id=E.report_id

WHERE
   nibrs_code = '240'
   --AND E.event_start_date >= '2024-01-01'
   --AND S.Status IN ('Stolen', 'Recovered','Burned','Destroyed Damaged')  --(S.Status = 'Stolen' OR S.Status = 'Recovered')

   ) AS AAA;

/*----------------------Add vehicle data only to speed up query--------------------*/
IF OBJECT_ID('tempdb..#TempStolen02') IS NOT NULL DROP TABLE #TempStolen02
SELECT *
INTO #TempStolen02
FROM (
SELECT
  T.*
   ,V.item_id AS V_item_id
   ,V.year_of_manufacture
   ,Upper(V.make) AS make
   ,Upper(V.model) AS model
   ,Upper(V.body_style) AS body_style
   ,IIF(V.secondary_color IS NOT NULL OR Not(V.secondary_color = ''),Upper(V.primary_color)+'/'+Upper(secondary_color),Upper(V.primary_color)) AS VehColor 
   ,Upper(V.license_plate) AS license_plate
   ,V.registration_state_abbreviation
       ,V.vin_number
FROM
#TempStolen01 AS T
      LEFT OUTER JOIN analytics_rms.vw_property_vehicles AS V ON V.report_id=T.report_id

   ) AS BBB;

/*----------------------Label Status only, search next---------------------*/
IF OBJECT_ID('tempdb..#TempStolen03') IS NOT NULL DROP TABLE #TempStolen03
SELECT *
INTO #TempStolen03
FROM (
SELECT
   T2.*
   ,S.status AS Vstatus
FROM
   #TempStolen02 AS T2
   LEFT OUTER JOIN analytics_rms.vw_property_statuses AS S ON S.item_id=T2.V_item_id
   ) AS CCC;

/*---------------------- Filter Status and Date now---------------------*/
SELECT
T3.*
   ,IIF(T3.Vstatus='Stolen', 'STN', 
       IIF(T3.Vstatus='Recovered', 'RCV',
       IIF(T3.Vstatus='Destroyed Damaged', 'DAM',
       IIF(T3.Vstatus='Burned', 'BURN',
                 T3.Vstatus)))) AS VehStatusShort
FROM
#TempStolen03 AS T3
WHERE
T3.event_start_date >= '2024-01-01'
--T3.event_start_date >= DATEADD(day,(@DayNumber*-1), GETDATE())
AND T3.Vstatus IN ('Stolen', 'Recovered','Destroyed Damaged','Burned')

Order By T3.record_number DESC

IF OBJECT_ID('tempdb..#TempStolen01') IS NOT NULL DROP TABLE #TempStolen01
IF OBJECT_ID('tempdb..#TempStolen02') IS NOT NULL DROP TABLE #TempStolen02
IF OBJECT_ID('tempdb..#TempStolen03') IS NOT NULL DROP TABLE #TempStolen03

I’m weak with working with M43 Tables since I don’t work with M43 data that often.  As an example, it’s hard to isolate the Make and Model since it might be in several different places, which includes the iv.description when other places are null.

Hello all, 

I couldn’t get the SSO to the guardian hub to work for me yesterday to provide a response. 

It looks like you received good information though @ajminton .

One thing that I would add, which you alluded to earlier, is that the “status” of the vehicle can change over time and the vehicle may be associated to multiple offenses (as you stated). 

I am not sure what the intent of your query is, but if it is to determine the current status of stolen vehicles (i.e., recovered, outstanding, etc.), then there is a bit more nuance (at least in my department). 

If your department is anything like mine, isolating vehicle statuses to NIBRS 240 codes may prevent you from identifying the current or most recent status of the vehicle if that vehicle has been involved in other offenses subsequent to the initial 240. My agency often stumbles upon NIBRS errors that are corrected well after the fact when a vehicle is initially stolen under a 240 offense code on one report and subsequently the recovery is documented under a NIBRS 280 (stolen property offenses) offense code on another report without an OMS to update the status on the original 240 offense code. In WA State, we have a “possession of stolen motor vehicle” statute that maps to NIBRS 280 and is documented when the vehicle is recovered and in possession of the suspect. 

In these instances, albeit incorrectly entered, the status of the vehicle on the 240 may show as stolen when the 280 shows it as recovered.

What I would do is query a full list of all vehicles associated to 240 offense codes and then join that back to a subquery/temp table providing the most recent status of each vehicle (keying on the vehicle id). This too is simplistic and doesn’t account for instances where the vehicle is stolen and recovered multiple times, but that nuance can be worked out if necessary. 
 

Jaycee, thanks for responding and that’s a very interesting angle.  I’ll take a guess that it’s also happening here and will research further.  Curious, do you have any reports that may catch the user errors?  If it’s an issue here, I will probably create a pre-report that our NIBRS person can run before their NIBRS run.  I was curious and checked our NIBRS 280’s.  One report came back with a vehicle listed as a Stolen status and then updated to Recovered status, which was done differently than the other 124 280 NIBRS cases that we have to date.