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

If you want the vehicle to offense link table, this generates that.  Note that the vehicle could be linked to multiple statuses. Each status has a link to it’s own offenses.  So you need to know the status you are looking for as well.  Also a vehicle could be linked to multiple offenses. So you can have situations like this, where you will need additional code to determine what offense you are looking for.

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
WHERE ps.offense_id IS NOT NULL

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.

Kevin M.  Thanks for your response.  I’m taking off now and will look tomorrow.

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'

Thanks for trying Kevin.  I don’t think that it isolates the exact field that I’m looking for.  I’m trying to isolate the “LINK VEHICLE(S)” field as seen below in this test case.

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

If you use the item_profile_id, you can get the rest of the information about the vehicle in the RMS.item_vehicles and supporting tables.

SELECT
--	ip.owner_id AS [report_id],
	r.reporting_event_number AS report,
--	iv.item_id AS [item_profile_id],
	iv.tag AS license_plate,
	makes.make_name AS make,
	models.model_name AS model,
	iv.year_of_manufacture AS year,
--	ps.property_status_attr_id,
	a_pstatus.display_value AS property_status,
--	ps.offense_id AS [offense_id],
	oc.display_name AS offense_code,
	noc.code AS nibrs_code
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
	JOIN RMS.reports r ON r.id = ip.owner_id
	LEFT JOIN RMS.vehicle_makes makes ON makes.id = iv.vehicle_make_id
	LEFT JOIN RMS.vehicle_models models ON models.id = iv.vehicle_model_id
	LEFT JOIN RMS.attributes a_pstatus ON a_pstatus.id = ps.property_status_attr_id
WHERE 
	ps.offense_id IS NOT NULL
	AND noc.code = '240'

I think I get it now. Thanks!

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.

Trying to parse the make and model from a text field (description) would be nearly impossible unless it always in the same format (because the officers ALWAYS put things in the same format /s), or you had some sort of AI processing that field.  I’m not sure where else it would be if not the make/model fields.  Where else have you seen it and I can dig around?

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. 
 

Kevin, I just needed to make a few mods to ur code and solved the MAKE issue and can apply the same to the MODEL issue.  As seen in this pic, using make alone has some null values, but adding an additional table, RMS.attributes make_attr, and using, COALESCE(makes.make_name, CONCAT(make_attr.display_value, ': ' + ip.item_make)) AS Make2 , shows the Make2 correctly now when the user enters the Make incorrectly or even when our interfaces brings in Make data incorrectly.

SELECT
--    ip.owner_id AS [report_id],
    r.reporting_event_number AS report,
--    iv.item_id AS [item_profile_id],
--    iv.tag AS license_plate,
    makes.make_name AS make,
    COALESCE(makes.make_name, CONCAT(make_attr.display_value, ': ' + ip.item_make)) AS Make2,
    models.model_name AS model,
    iv.year_of_manufacture AS year,
--    ps.property_status_attr_id,
    a_pstatus.display_value AS property_status,
--    ps.offense_id AS [offense_id],
    oc.display_name AS offense_code,
    noc.code AS nibrs_code
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
    JOIN RMS.reports r ON r.id = ip.owner_id
    LEFT JOIN RMS.attributes make_attr ON iv.vehicle_make_attr_id = make_attr.id
    LEFT JOIN RMS.vehicle_makes makes ON makes.id = iv.vehicle_make_id
    LEFT JOIN RMS.vehicle_models models ON models.id = iv.vehicle_model_id
    LEFT JOIN RMS.attributes a_pstatus ON a_pstatus.id = ps.property_status_attr_id
WHERE 
    ps.offense_id IS NOT NULL
    AND noc.code = '240'

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.