Module Output
SQL:[IDI_Community].[crim_family_sexual_violence].family_sexual_violence_YYYYMM
SAS: libname cmti ODBC dsn=idi_community_srvprd schema=crim_family_sexual_violence; proc print data = cmti.family_sexual_violence_YYYYMM
How to access a code module in the Data Lab:Read here
All workshop recordings : Find here
Purpose
The purpose of this module is to construct spells of people who used or who have been impacted by family violence and sexual violence (FVSV) in New Zealand.
These spells can be used to produce estimates of reported instances of people who have perpetrated and/or have been impacted by FVSV at a given point in time.
The FVSV indictor identifies people that are either victims or offenders of family violence or sexual violence based of recorded interactions with Oranga Tamariki, Ministry of Health, ACC, Police or Ministry of Justice. The final output is a spell dataset with recorded ongoing interactions with the social system, if any, for each individual.
Key Concepts
FVSV is a complex social problem, and often involves multiple government agencies, non-government organisations (NGOs), violence specialists, communities, and whanau.
There are many referral pathways in the social system for victims of FVSV to get support and help from.
Similarly, different screening tools are established across the system to identify at-risk individuals to determine if intervention or referral is required.
The intervention pathway for offenders of FVSV is more straight forward, and often limited to the justice and legal systems, or intervention programmes.
Granularity
The modules are setup to be event format: One row per event, events have a person/entity, date(s), and descriptive information. The variables below will uniquely identify each event in the above table:
Entity id: snz_uid
Period: incident_start_date, incident_end_date
Event information: is_FV_flag, is_SV_flag, offender_flag, victim_flag, classification.
The output dataset allows the analysts and researchers to investigate and explain the different behaviour patterns of victimisation and perpetration of FVSV.
Some behaviour patterns that can be easily identified are:
- People who have experienced FVSV in a single incident or with enduring repeated victimisation.
- People who have used FVSV in a single act or with an ongoing re-offending behaviour.
- People who have both used and experienced FVSV.
The definition also enables the analysts and researchers to answer a range of both descriptive and causal questions. For example:
- How many people have experienced violence and seek support from the system?
- How many incidents of sexual violence and family violence are known to an agency?
- What are their environmental circumstances and social outcomes?
Module Business Rules
1.NZ Police
Data sources:
- Recorded Crime Victims Statistics (RCVS) - July 2014 onwards
- Recorded Crime Offenders Statistics (RCOS) - 2009 onwards
An incident is an occurrence with one or several offences.
The incident start date is the earliest date that the offending reported could have occurred and the incident end date is the latest occurrence end date.
There are two business rules:
- Occurrences with one of the 346 offence codes related to Family Violence and
- Sexual Violence (not all the offences are specific to FVSV)
Occurrences where Victimisations/offender records with intimate or familial relationship between victim and the offender (at the time of the incident), including ex-partners and ex-boyfriends/girlfriends.
2.Ministry of Justice
Data source: Criminal Court Charges - January 1992 onwards
An incident is a charge.
The incident start and end date are the date on which the alleged offence occurred.
There are three business rules:
- Charges with one of the 346 offence codes related to Family Violence and
- Sexual Violence (not all the offences are specific to FVSV)
- Charges with the moj_chg_family_violence_ind = 1
Note that this indicator (which originates from Police and indicates a charge, which could be for any offence type, is family violence-related) is only populated from 1 July 2014 onwards, which impacts on the ability to extract reliable FV information prior to that.
Charges with a Sentencing Act Final Protection Order (FPO) recorded as one of the sentences.
These were introduced in 2010 and represent about 15% of Protection Order applications each year. Application for Protection Order (which is Family Court information) is not available in the IDI.
The incident start and end date are the date on which the alleged offence occurred.
3.Oranga Tamariki
Data sources:
- Contact record events - May 2010 onwards
- Notification events (including Report of Concern and referrals) - September 1991 onwards
- Child Action Plan (CAP)
An incident is a family violence event (Contact record events).
The incident start date is the date that the file was created.
An incident is a Family violence notification (Notification events).
The incident start date is the received date of notification.
An incident is a Family violence referral. (Child Action Plan).
The incident start date is the date the referral was received.
There are three business rules. One for each data source:
- Contact records events:
- Events with a family violence indicator = Y
- The family violence indicator is derived from caller_role and other fields.
- The contact record data available in the IDI is limited to creation date.
- In some cases, a contact record may be created many months after the incident occurred due to processing time lags.
- OT Notifications:
- Notifications with organization notification =‘PFV’ (Police Family Violence) and a closed date
- OT receives FV notifications from Police via SAM/FVIARS tables.
- PFV notifications have a notable downward trend in recent years and should be used with caution as this is not considered to reflect a decline in real world family violence but rather changes in reporting and referral practice that occur earlier in the process before OT would be notified.
- OT Child Action Plan:
- Referrals with a referrer sector =‘Family violence’ and a referral status ‘Accepted’
- OT does not use this data source as a marker for Family violence in their Child Wellbeing Model.
4.ACC
Data source: ACC Claims
An incident is a claim.
The incident start date is the date the accident occurred.
- Sexual Violence (Sensitive claims):
- Claims with acc_cla_sensitive_claim_ind = ‘Y’ AND acc_cla_decision_text = ‘ACCEPT’, ‘DECLINE’, ‘HOLD’, ‘INTERIM ACCEPT’
- Clients with sensitive claims may choose to withdraw their claim before or during the cover assessment process. These withdrawn claims are recorded as declined.
- These claims may still receive support from ACC prior to withdrawing from the ISSC service.
- Therefore, when reporting on sensitive claims, both declined and accepted claims are considered, as the decline represents a decline in the cover approval process opposed to a decline in the claim cover itself.
5.Ministry of Health
Data sources:
- National Minimum Dataset (NMDS) - Public and Private Hospitalisations
- National Non-Admitted Patient Collection (NNPAC) - Outpatient data
An incident is a hospitalisation.
The incident start date for the Public Hospitalisations is the date on which a healthcare user was discharged from a facility (the date the healthcare event ended).
The incident start date for the Private Hospitalisations is the date on which the healthcare event began.
The incident start date for the outpatient data is the date that the triaged patient’s treatment starts by a suitable ED professional.
There are five business rules:
- Diagnosis code related to FVSV:
- Hospitalisation events (Public or Private) with a diagnosis of neglect, abuse, deprivation, assault
- Traumatic brain injury or long bone fractures for child under 2 years of age:
- Hospitalisation events (Public or private) with diagnosis of traumatic brain injury and long bone fractures where the patient is less or equal to 2 years old.
- Traumatic brain injury and long bone fractures are only included for children under 2 as these types of injuries are more often caused by abuse in children under 2 years.
- Event of undetermined intent for child and young people under 14 years of age:
- Hospitalisation events (Public or private) with diagnosis of poisoning, strangulation, falling, drowning where the patient is less or equal to 14 years old.
- The undetermined intent codes are used by clinical coders when it is not possible to distinguish between injuries caused by an accident, self-harm, and assault. They are limited to those aged under 14 years as these events are less likely to be self-inflicted for younger children, though an accident could still be the cause. They cover a range of injuries from poisons, exposure to smoke, falling, and drowning.
- Perpetrator of FVSV:
- Hospitalisation events (Public or private) with a diagnosis of neglect and maltreatment by spouse, by parent, encounter for mental health services for perpetrator of parental/ spouse abuse.
- Outpatient (Non-Admitted Patient):
- Events with one of the three FVSV related health services (COCH0015 - Child Protection, COCH0020 - Child Abuse, SH01004 - Medical Management of Sexual Abuse)
Open Issues/Comments
None
Parameters
The following parameters should be supplied to this module to run it in the database:
- {targetdb}: The SQL database on which the spell datasets are to be created.
- {idicleanversion}: The IDI Clean version that the spell datasets need to be based on.
- {idimetaversion}: The IDI Metadata version that the spell datasets need to be based on.
- {targetschema}: The project schema under the target database into which the spell datasets are to be created.
- {projprefix}: A (short) prefix that enables you to identify the spell dataset easily in the schema, and prevent overwriting any existing datasets that have the same name.
Dependencies
[IDI_Metadata_YYYYMM].[moj].[offence_code]
[IDI_Metadata_YYYYMM].[pol].[rov_code]
[IDI_Clean_YYYYMM].[pol_clean].[nia_links]
[IDI_Clean_YYYYMM].[pol_clean].[pre_count_offenders]
[IDI_Clean_YYYYMM].[pol_clean].[pre_count_victimisations]
[IDI_Clean_YYYYMM].[moj_clean].[charges]
[IDI_Clean_YYYYMM].[cyf_clean].[cyf_contact_record_events]
[IDI_Clean_YYYYMM].[cyf_clean].[cyf_intakes_event]
[IDI_Clean_YYYYMM].[cyf_clean].[cyf_intakes_details]
[IDI_Clean_YYYYMM].[cyf_clean].[cyf_identity_cluster]
[IDI_Clean_YYYYMM].[cap_clean].[referrals]
[IDI_Clean_YYYYMM].[acc_clean].[claims]
[IDI_Clean_YYYYMM].[moh_clean].[pub_fund_hosp_discharges_event]
[IDI_Clean_YYYYMM].[moh_clean].[pub_fund_hosp_discharges_diag]
[IDI_Clean_YYYYMM].[moh_clean].[priv_fund_hosp_discharges_event]
[IDI_Clean_YYYYMM].[moh_clean].[priv_fund_hosp_discharges_diag]
[IDI_Clean_YYYYMM].[moh_clean].[nnpac]
[IDI_Clean_YYYYMM].[data].[personal_detail]
Outputs
{targetdb}.{targetschema}.{projprefix}_family_sexual_violence_{refresh}
Variable Descriptions
| Aspect | Variables | Description |
|---|---|---|
| Entity | snz_uid | Current refresh snz_uid |
| Source | source_data | Sources of the interaction: |
| OT_CONTACT,OT_NOTIFICATION,OT_CAP,ACC,MOH_PUB_HOSP,MOH_PRIV_HOSP, | ||
| MOH_OUTPATIENT,POL_VIC,POL_OFF,MOJ_CHARGES | ||
| Period | incident_start_date | Event start date |
| incident_end_date | Event end date | |
| Event | Event_id | Unique identifier for each event. It is different for each source* |
| is_FV_flag | The incident is family violence related, values = Y, N, NULL | |
| Is_SV_flag | The incident is sexual violence related, values = Y, N, NULL | |
| offender_flag | The individual is an offender of FVSV, values = 1 (Yes), 0 (No) | |
| victim_flag | The individual is a victim of FVSV, values = 1 (Yes), 0 (No) | |
| codification | Codification used by agency to identify the nature of the incident. It is only filled in for MOJ and Police data sources with the offence code and for ACC and MOH data sources with the diagnosis clinical code. | |
| codification_desc | Description of the codification code. The description provides the highest category of the offence or the diagnosis clinical code. | |
| snz_birth_date_proxy | Date of birth of the individual | |
| snz_sex_gender_code | Sex/gender code, values = 1 (Male), 2 (Female), 3 (Transgender), NULL | |
| euro | European ethnicity indicator, values = 1 (Yes), 0 (No) | |
| maori | Maori ethnicity indicator, values = 1 (Yes), 0 (No) | |
| pacific | Pacific Peoples ethnicity indicator, values = 1 (Yes), 0 (No) | |
| asian | Asian ethnicity indicator, values = 1 (Yes), 0 (No) | |
| melaa | Middle Eastern/Latin American/African ethnicity indicator, values = 1 (Yes), 0 (No) | |
| eth_other | Other ethnicities, Value: 1 (Yes), 0 (No) | |
| snz_deceased_date_proxy | Proxy date when the identifier has deceased | |
| age_at_incident | Age of individual when the incident started | |
| Relationship | Relationship between the victim and the offender but only for Police data |
Module Version & Change History
| Date | Version | Comments |
|---|---|---|
| August 2020 | First version of the code developed by Marianna Pekar | |
| September 2024 | Initial version developed by Stella Sim | Version based on specifications from Commissioning document. |
Community of Interest
| Domain | Agency | Person |
|---|---|---|
| Lead SME | Te Puna Aonui | Cecile Dubuisson |
| IDI analysts | SIA | Stella SIM |
| Peer reviewer | Ministry of Justice | Jo Fink |
| Peer reviewer | Ministry of Health | Kristy Udy |
| Peer reviewer | ACC | Anurag Sharma |
| Peer reviewer | Oranga Tamariki | Nick Preval |
Code
/* Establish database for writing views */
:setvar targetdb "{targetdb}"
:setvar targetschema "{targetschema}"
:setvar projprefix "{projprefix}"
:setvar idicleanversion "{idicleanversion}"
:setvar idimetaversion "{idimetaversion}"
/* Assign the target database to which all the components need to be created in. */
use $(targetdb);
/* Offence codes for Family violence Sexual violence */
DROP TABLE IF EXISTS #FVSV_codes;
CREATE TABLE #FVSV_codes (
offence_code VARCHAR(1000)
,offence_description NVARCHAR(MAX)
,is_SV_flag SMALLINT
,is_FV_flag SMALLINT
);
/* Fetch list of offence codes maintained by MoJ */
INSERT INTO #FVSV_codes (
offence_code
,offence_description
,is_SV_flag
,is_FV_flag
)
SELECT
OFFENCE_CODE AS offence_code
,OFFENCE_DESCRIPTION AS offence_description
,IIF(IS_SEXUAL_VIOLENCE_FLG = 'Y', 1, 0) AS is_SV_flag
,IIF(is_specific_family_violence_flg = 'Y', 1, 0) AS is_FV_flag
FROM $(idimetaversion).moj.offence_code
WHERE is_specific_family_violence_flg = 'Y'
OR IS_SEXUAL_VIOLENCE_FLG = 'Y'
/* Manual loading of offence codes of interest for FVSV. */
INSERT INTO #FVSV_codes (
offence_code
, offence_description
, is_SV_flag
, is_FV_flag
)
VALUES
('1475', 'Impedes Breathing/Blood Circulation (Strangulation/Suffocation)',0,1)
,('1531','Assaults Child (Firearm)',0,1) /* not strictly FV, but more than 90% of such offences are FV (Jo Fink)*/
,('1532','Assaults Child (Other Weapon)',0,1) /* not strictly FV, but more than 90% of such offences are FV (Jo Fink)*/
,('1533','Assaults Child (Manually)',0,1) /* not strictly FV, but more than 90% of such offences are FV (Jo Fink)*/
,('1534','Assaults Child (Stabbing/Cutting Weapon)',0,1) /* not strictly FV, but more than 90% of such offences are FV (Jo Fink)*/
,('1539','Other Assault On Child (Under 14 Years)',0,1) /* not strictly FV, but more than 90% of such offences are FV (Jo Fink)*/
,('1541','Male Assaults Female (Firearm)',0,1) /* not strictly FV, but more than 90% of such offences are FV (Jo Fink)*/
,('1542','Male Assaults Female (Other Weapon)',0,1) /* not strictly FV, but more than 90% of such offences are FV (Jo Fink)*/
,('1543','Male Assaults Female (Manually)',0,1) /* not strictly FV, but more than 90% of such offences are FV (Jo Fink)*/
,('1544','Male Assaults Female (Stabbing/Cutting Weapon)',0,1) /* not strictly FV, but more than 90% of such offences are FV (Jo Fink)*/
,('1549','Other Assaults By Male On Female',0,1)/* not strictly FV, but more than 90% of such offences are FV (Jo Fink)*/
,('1771','Fail To Provide Necessities For Child',0,1) /* Child abuse and neglect*/
,('1772','Fail To Provide Necessities For Adult',0,1) /* Elder or disabled family member abuse and neglect*/
,('1774','Ill-treat/Neglect Child Under 16',0,1) /* Child abuse and neglect*/
,('1775','Illtreat/Neglect Child Under 18 Years',0,1)
,('1776','Illtreat/Neglect Vulnerable Adult',0,1) /* Elder or disabled family member abuse and neglect*/
,('1778','Fail To Protect Vulnerable Adult',0,1) /* Elder or disabled family member abuse and neglect*/
,('3712','Ill Treat And Wilful Neglect To Child',0,1)
,('3714','Leaving Child Without Reasonable Supervision',0,1)
,('3716','Child Left In Detrimental Environment',0,1)
,('3888','Fails to Remain for a Police Safety Order',0,1)
,('2132','Abducts For Sex (Female)',1,0) /* Not in ANZSOC Division 03, but can treated as SV*/
/* Not in ANZSOC Division 03, but can treated as SV. These crimes against morality/decency
(section 125/126 Crimes Act) which include indecent exposure ('flushing'). */
,('2213','Indecent Act With Intent To Insult (Male Offender)',1,0)
,('2214','Indecent Act With Intent To Insult (Female Offender)',1,0)
,('2215','Indecent Act (Male Offender)',1,0)
,('2216','Indecent Act (Female Offender)',1,0)
/* Not in ANZSOC Division 03, but can treated as FV and SV */
,('2621','Abduction For Marriage Girl Under 12',1,1)
,('2622','Abduction For Marriage Girl Under 12-16',1,1)
,('2623','Abduction For Marriage Female Over 16',1,1)
/* Not in ANZSOC Division 03, but can treated as SV */
,('2624','Abduction For Sex Girl Under 12',1,0)
,('2625','Abduction For Sex Girl 12-16',1,0)
,('2626','Abduction For Sex Female Over 16',1,0)
,('2627','Abduction For Marriage - Male',1,0)
,('2628','Abduction For Sex - Male',1,0)
/* Not in ANZSOC Division 03, but can treated as FV and SV */
,('2629','Other Abduction For Marriage Or Sex',1,1)
/* Crimes Act, Sec 143/144 */
,('2721','Bestiality',1,0)
,('2722','Indecency With Animal',1,0)
,('2723','Compelling Indecent Act With Animal',1,0)
,('2729','Other Sex With Animals',1,0)
,('2944','Indecent Communication With young Person Under 16',1,0) /*Crimes Act Sec 124A*/
,('2954','Print/Publish Info Promote Child Sex Tour',1,0)
;
/* Drop duplicates */
WITH dupe_markers AS (
SELECT *
,RN = ROW_NUMBER() OVER (
PARTITION BY offence_code, offence_description
ORDER BY is_FV_flag, is_SV_flag
)
FROM #FVSV_codes
)
DELETE FROM dupe_markers
WHERE RN > 1
DROP TABLE IF EXISTS #FVSV_codes_dia;
CREATE TABLE #FVSV_codes_dia (
codification VARCHAR(1000)
,codification_desc NVARCHAR(MAX)
);
INSERT INTO #FVSV_codes_dia (codification, codification_desc)
VALUES
('S06','Intracranial injury')
,('S42','Fracture of shoulder an upper arm')
,('S52','Fracture of forearm')
,('S72','Fracture of femur')
,('S82','Fracture of lower leg, including ankle')
,('T10','Fracture of upper limb, level unspecified')
,('T11','Other injuries of upper limb, level unspecified')
,('T12','Fracture of lower limb, level unspecified')
,('T73','Effects of other deprivation')
,('T74','Maltreatment syndromes')
,('T76','Unspecified effects of external causes')
,('X85','Assaults by drugs, medicaments and biological substances')
,('X86','Assault by corrosive substance')
,('X87','Assault by pesticides')
,('X88','Assault by gases and vapours')
,('X89','Assault by other specified chemicals and noxious substances')
,('X90','Assault by unspecified chemicals and noxious substances')
,('X91','Assault by hanging, strangulation and suffocation')
,('X92','Assault by drowning and subnersion')
,('X93','Assault by handgun discharge')
,('X94','Assault by rifle, shotgun and larger firearm discharge')
,('X95','Assault by other and unspecified firearm discharge')
,('X96','Assault by explosive material')
,('X97','Assault by smoke, fire and flames')
,('X98','Assault by steam, hot vapours and hot objects')
,('X99','Assault by sharp object')
,('Y00','Assault by blunt object')
,('Y01','Assault by pushing from high place')
,('Y02','Assault by pushing or placing victim before moving object')
,('Y03','Assault by crashing of motor vehicle')
,('Y04','Assault by bodily force')
,('Y05','Sexual assault by bodily force')
,('Y06','Neglect and abandonment')
,('Y07','Other maltreatment')
,('Y08','Assault by other specified means')
,('Y09','Assault by unspecified means')
,('Y10','Poisoning by and exposure to nonopioid analgesics, undeternined intent')
,('Y11','Poisoning by and exposure to antiepileptic, sedative-hypnotic, undeternined intent')
,('Y12','Poisoning by and exposure to narcotics and psychodysleptics, undeternined intent')
,('Y13','Poisoning by and exposure to other drugs acting on the autonomic nervous system, undeternined intent')
,('Y14','Poisoning by and exposure to other and unspecified drugs, undeternined intent')
,('Y15','Poisoning by and exposure to alcohol, undeternined intent')
,('Y16','Poisoning by and exposure to organic solvents and halogenated hydrocarbons, undeternined intent')
,('Y17','Poisoning by and exposure to carbon monoxide and other gases and vapours, undeternined intent')
,('Y18','Poisoning by and exposure to pesticides, undeternined intent')
,('Y19','Poisoning by and exposure to other and unspecified chemicals and noxious substances, undeternined intent')
,('Y20','Hanging, strangulation and suffocation, undeternined intent')
,('Y21','Drowning and submersion, undeternined intent')
,('Y22','Handgun discharge, undeternined intent')
,('Y23','Rifle, shotgun and larger firearm discharge, undeternined intent')
,('Y24','Other and unspecified firearm discharge, undeternined intent')
,('Y25','Contact with explosive material, undeternined intent')
,('Y26','Exposure to smoke, fire and flames, undeternined intent')
,('Y27','Contact with steam, hot vapours and hot objects, undeternined intent')
,('Y28','Contact with sharp object, undeternined intent')
,('Y29','Contact with blunt object, undeternined intent')
,('Y30','Falling, jumping or pushed from a high place, undeternined intent')
,('Y31','Falling, lying or running before or into moving object, undeternined intent')
,('Y32','Crashing of motor vehicle, undeternined intent')
,('Y33','Other specified events, undeternined intent')
,('Y34','Unspecified events, undeternined intent')
,('Y87','sequelae of intentional self-harm, assault and events of undetermined intent')
,('Z04','Examination and observation for other reasons')
,('Z61','Problems related to negative life events in childhood')
,('Z62','Other problems related to upbringing')
,('Z63','Other problems related to primary support group, including family circumstances')
,('Z69','Encounter for mental health services for victim and perpetrator of abuse')
,('Z91','Personal history of risk-factors, not elsewhere classified')
;
/* Combined table of FVSV events
DROP TABLE IF EXISTS #tmp_pol_moj;
CREATE TABLE #tmp_pol_moj (
snz_uid INT
,incident_start_date DATE
,incident_end_date DATE
,is_FV_flag SMALLINT
,is_SV_flag SMALLINT
,offender_flag SMALLINT
,victim_flag SMALLINT
,source_data VARCHAR(15)
,codification VARCHAR(1000)
,event_id VARCHAR(20)
,rov CHAR(4)
);
/*Recorded Crime Offenders Statistics (RCOS) ■■■■■■■ onwards*/
/*Pre-count offenders*/
INSERT INTO #tmp_pol_moj (
snz_uid
,incident_start_date
,incident_end_date
,is_FV_flag
,is_SV_flag
,offender_flag
,victim_flag
,source_data
,codification
,event_id
,rov
)
SELECT DISTINCT
a.snz_uid
,a.pol_pro_earliest_occ_start_date AS incident_start_date
,a.pol_pro_latest_poss_occ_date AS incident_end_date
,b.is_FV_flag
,b.is_SV_flag
,1 AS offender_flag
,0 AS victim_flag
,'POL_OFF' AS source_data
,a.pol_pro_offence_code AS codification
,a.snz_pol_occurrence_uid AS event_id
,a.pol_pro_rov_code AS rov
FROM $(idicleanversion).pol_clean.pre_count_offenders AS a
JOIN #FVSV_codes AS b
ON a.pol_pro_offence_code = b.offence_code
WHERE a.■■■■■■■■■■■
AND a.pol_pro_latest_poss_occ_date <= a.pol_pro_proceeding_date
INSERT INTO #tmp_pol_moj (
snz_uid
,incident_start_date
,incident_end_date
,is_FV_flag
,is_SV_flag
,offender_flag
,victim_flag
,source_data
,codification
,event_id
,rov
)
SELECT DISTINCT
snz_uid
,pol_pro_earliest_occ_start_date AS incident_start_date
,pol_pro_latest_poss_occ_date AS incident_end_date
,1 AS is_FV_flag
,0 AS is_SV_flag
,1 AS offender_flag
,0 AS victim_flag
,'POL_OFF' AS source_data
,pol_pro_offence_code AS codification
,snz_pol_occurrence_uid AS event_id
,pol_pro_rov_code AS rov
FROM $(idicleanversion).pol_clean.pre_count_offenders
WHERE pol_pro_offence_code NOT IN (SELECT offence_code from #FVSV_codes)
AND (
LEFT(pol_pro_rov_code, 2) = '11'
OR pol_pro_rov_code in (1210,1220)
)
/*Recorded Crime Victims Statistics (RCVS) -- July 2014 onwards*/
/*Pre-count victimisation*/
INSERT INTO #tmp_pol_moj (
snz_uid
,incident_start_date
,incident_end_date
,is_FV_flag
,is_SV_flag
,offender_flag
,victim_flag
,source_data
,codification
,event_id
,rov
)
SELECT DISTINCT
a.snz_uid
,a.pol_prv_earliest_occ_start_date AS incident_start_date
,a.pol_prv_latest_poss_occ_date AS incident_end_date
,b.is_FV_flag
,b.is_SV_flag
,0 AS offender_flag
,1 AS victim_flag
,'POL_VIC' AS source_data
,a.pol_prv_offence_code AS codification
,a.snz_pol_occurrence_uid AS event_id
,a.pol_prv_rov_code AS rov
FROM $(idicleanversion).pol_clean.pre_count_victimisations AS a
JOIN #FVSV_codes AS b
ON a.pol_prv_offence_code = b.offence_code
WHERE a.■■■■■■■■■■■
AND a.pol_prv_latest_poss_occ_date <= a.pol_prv_reported_date
INSERT INTO #tmp_pol_moj (
snz_uid
,incident_start_date
,incident_end_date
,is_FV_flag
,is_SV_flag
,offender_flag
,victim_flag
,source_data
,codification
,event_id
,rov)
SELECT DISTINCT
snz_uid
,pol_prv_earliest_occ_start_date AS incident_start_date
,pol_prv_latest_poss_occ_date AS incident_end_date
,1 AS is_FV_flag
,0 AS is_SV_flag
,0 AS offender_flag
,1 AS victim_flag
,'POL_VIC' AS source_data
,pol_prv_offence_code AS codification
,snz_pol_occurrence_uid AS event_id
,pol_prv_rov_code AS rov
FROM $(idicleanversion).pol_clean.pre_count_victimisations
WHERE pol_prv_offence_code NOT IN (SELECT offence_code FROM #FVSV_codes)
AND (
LEFT(pol_prv_rov_code, 2) = '11'
OR pol_prv_rov_code in (1210,1220)
)
/*Ministry of Justice, Charges (1992 onwards)*/
INSERT INTO #tmp_pol_moj (
snz_uid
,incident_start_date
,incident_end_date
,is_FV_flag
,is_SV_flag
,offender_flag
,victim_flag
,source_data
,codification
,event_id
,rov
)
SELECT DISTINCT
a.snz_uid
,a.moj_chg_offence_from_date AS incident_start_date
,a.moj_chg_offence_from_date AS incident_end_date
,b.is_FV_flag
,b.is_SV_flag
,1 AS offender_flag
,0 AS victim_flag
,'MOJ_CHARGES' AS data_source
,a.moj_chg_offence_code AS codification
,a.snz_moj_charge_uid AS event_id
,NULL AS rov
FROM $(idicleanversion).moj_clean.charges AS a
JOIN #FVSV_codes AS b
ON a.moj_chg_offence_code = b.offence_code
WHERE a.moj_chg_offence_from_date IS NOT NULL
INSERT INTO #tmp_pol_moj (
snz_uid
,incident_start_date
,incident_end_date
,is_FV_flag
,is_SV_flag
,offender_flag
,victim_flag
,source_data
,codification
,event_id
,rov
)
SELECT DISTINCT
snz_uid
,moj_chg_offence_from_date AS incident_start_date
,moj_chg_offence_from_date AS incident_end_date
,1 AS is_FV_flag
,0 AS is_SV_flag
,1 AS offender_flag
,0 AS victim_flag
,'MOJ_CHARGES' AS source_data
,moj_chg_offence_code AS codification
,snz_moj_charge_uid AS event_id
,NULL as rov
FROM $(idicleanversion).moj_clean.charges
WHERE moj_chg_offence_from_date IS NOT NULL
AND moj_chg_offence_code NOT IN (SELECT offence_code FROM #FVSV_codes)
/* The family violence indicator has only been populated from 2014/2015 onwards*/
AND moj_chg_family_violence_ind = 1
/*Ministry of Justice, Charges (1992 onwards)*/
/*Application for Protection Order is not available, but we can identify offender with Sentencing Act Final */
/*Protection Order recorded as one of the sentences from 2010 onwards (these represent about 15% of Protection Order
applications each year)*/
INSERT INTO #tmp_pol_moj (
snz_uid
,incident_start_date
,incident_end_date
,is_FV_flag
,is_SV_flag
,offender_flag
,victim_flag
,source_data
,codification
,event_id
,rov
)
SELECT DISTINCT
snz_uid
,moj_chg_offence_from_date AS incident_start_date
,moj_chg_offence_from_date AS incident_end_date
,1 AS is_FV_flag
,0 AS is_SV_flag
,1 AS offender_flag
,0 AS victim_flag
,'MOJ_CHARGES' AS source_data
,moj_chg_offence_code AS codification
,snz_moj_charge_uid AS event_id
,NULL AS rov
FROM $(idicleanversion).moj_clean.charges
WHERE 'FPO' IN (
moj_chg_serious_sentence1_code
,moj_chg_serious_sentence2_code
,moj_chg_serious_sentence3_code
,moj_chg_serious_sentence4_code
,moj_chg_serious_sentence5_code
)
AND moj_chg_offence_from_date IS NOT NULL
DROP TABLE IF EXISTS #temp_rov_code;
SELECT
a.snz_uid
,a.incident_start_date
,a.incident_end_date
,a.is_FV_flag
,a.is_SV_flag
,a.offender_flag
,a.victim_flag
,a.source_data
,a.codification
,a.event_id
,b.Relationship
INTO #temp_rov_code
FROM #tmp_pol_moj AS a
LEFT JOIN $(idimetaversion).pol.rov_code AS b
ON a.rov = b.Code
DROP TABLE IF EXISTS #tmp_pol_moj_final;
SELECT
a.snz_uid
,a.incident_start_date
,a.incident_end_date
,a.is_FV_flag
,a.is_SV_flag
,a.offender_flag
,a.victim_flag
,a.source_data
,a.codification
,b.ANZSOC_GROUP_NAME AS codification_desc
,a.event_id
,a.relationship
INTO #tmp_pol_moj_final
FROM #temp_rov_code AS a
LEFT JOIN $(idimetaversion).moj.offence_code AS b
ON a.codification=b.OFFENCE_CODE
DROP TABLE IF EXISTS #tmp_ot_acc;
CREATE TABLE #tmp_ot_acc (
snz_uid INT
,incident_start_date DATE
,incident_end_date DATE
,is_FV_flag SMALLINT
,is_SV_flag SMALLINT
,offender_flag SMALLINT
,victim_flag SMALLINT
,source_data VARCHAR(15)
,codification VARCHAR(1000)
,codification_desc VARCHAR(4000)
,event_id VARCHAR(20)
,relationship VARCHAR(4000)
);
/*OT Contact Record Events*/
INSERT INTO #tmp_ot_acc (
snz_uid
,incident_start_date
,incident_end_date
,is_FV_flag
,is_SV_flag
,offender_flag
,victim_flag
,source_data
,codification
,codification_desc
,event_id
,relationship
)
SELECT DISTINCT
b.snz_uid
,CAST(a.cyf_cli_cr_created_datetime AS DATE) AS incident_start_date
,CAST(a.cyf_cli_cr_created_datetime AS DATE) AS incident_end_date
,1 AS is_FV_flag
,0 AS is_SV_flag
,0 AS offender_flag
,1 AS victim_flag
,'OT_CONTACT' AS source_data
,NULL AS codification
,NULL AS codification_desc
,a.cyf_cli_cr_id_nbr AS event_id
,NULL AS relationship
FROM $(idicleanversion).cyf_clean.cyf_contact_record_events AS a
JOIN $(idicleanversion).cyf_clean.cyf_identity_cluster AS b
ON a.snz_prsn_uid = b.snz_systm_prsn_uid
WHERE a.cyf_cli_family_violence_ind = 'Y'
/*OT Notifications include ROCs and referrals. */
/* Note that family violence notifcations from other sources are not captured using the measure below (they are difficult to distinguish in the data).*/
INSERT INTO #tmp_ot_acc (
snz_uid
,incident_start_date
,incident_end_date
,is_FV_flag
,is_SV_flag
,offender_flag
,victim_flag
,source_data
,codification
,codification_desc
,event_id
,relationship
)
SELECT DISTINCT
a.snz_uid
,a.cyf_ine_event_from_date_wid_date AS incident_start_date
,a.cyf_ine_event_to_date_wid_date AS incident_end_date
,1 AS is_FV_flag
,0 AS is_SV_flag
,0 AS offender_flag
,1 AS victim_flag
,'OT_NOTIFICATION' AS source_data
,NULL AS codification
,NULL AS codification_desc
,a.snz_composite_event_uid AS event_id
,NULL AS relationship
FROM $(idicleanversion).cyf_clean.cyf_intakes_event AS a
INNER JOIN $(idicleanversion).cyf_clean.cyf_intakes_details AS b
ON a.snz_composite_event_uid = b.snz_composite_event_uid
WHERE b.cyf_ind_notifier_role_type_code = 'PFV'
AND a.cyf_ine_event_to_date_wid_date <> '9999-12-31' /*removed end date filler */
/*OT Child Action PLan*/
/* Note Oranga Tamariki does not use this data source as a marker for family violence in their Child Wellbeing Model*/
INSERT INTO #tmp_ot_acc (
snz_uid
,incident_start_date
,incident_end_date
,is_FV_flag
,is_SV_flag
,offender_flag
,victim_flag
,source_data
,codification
,codification_desc
,event_id
,relationship
)
SELECT DISTINCT
snz_uid
,cap_ref_received_date AS incident_start_date
,cap_ref_received_date AS incident_end_date
,1 AS is_FV_flag
,0 AS is_SV_flag
,0 AS offender_flag
,1 AS victim_flag
,'OT_CAP' AS source_data
,NULL AS codification
,NULL AS codification_desc
,snz_cap_referral_uid AS event_id
,NULL AS relationship
FROM $(idicleanversion).cap_clean.referrals
WHERE cap_ref_sector_code = 'FAMILY VIOLENCE'
AND cap_ref_referral_status_code = 'ACCEPTED'
/*ACC claims, 1974 onwards*/
INSERT INTO #tmp_ot_acc (
snz_uid
,incident_start_date
,incident_end_date
,is_FV_flag
,is_SV_flag
,offender_flag
,victim_flag
,source_data
,codification
,codification_desc
,event_id
,relationship
)
SELECT DISTINCT
snz_uid
,acc_cla_accident_date AS incident_start_date
,acc_cla_accident_date AS incident_end_date
,0 AS is_FV_flag
,1 AS is_SV_flag
,0 AS offender_flag
,1 AS victim_flag
,'ACC' AS source_data
,acc_cla_read_code AS codification
,acc_cla_read_code_text AS codification_desc
,snz_acc_claim_uid AS event_id
,NULL AS relationship
FROM $(idicleanversion).acc_clean.claims
/*sexual violence -- sensitive claim*/
WHERE acc_cla_sensitive_claim_ind = 'Y'
AND acc_cla_decision_text IN ('ACCEPT', 'DECLINE' ,'HOLD', 'INTERIM ACCEPT')
/*MOH Outpatient data*/
/*start date is not the incident date*/
INSERT INTO #tmp_ot_acc (
snz_uid
,incident_start_date
,incident_end_date
,is_FV_flag
,is_SV_flag
,offender_flag
,victim_flag
,source_data
,codification
,codification_desc
,event_id
,relationship
)
SELECT DISTINCT
snz_uid
,moh_nnp_service_date AS incident_start_date
,moh_nnp_service_date AS incident_end_date
,IIF(moh_nnp_purchase_unit_code = 'SH01004', 0, 1) AS is_FV_flag
,IIF(moh_nnp_purchase_unit_code = 'SH01004', 1, 0) AS is_SV_flag
,0 AS offender_flag
,1 AS victim_flag
,'MOH_OUTPATIENT' AS source_data
,moh_nnp_purchase_unit_code AS codification
,case when moh_nnp_purchase_unit_code='COCH0015' then 'Child Protection'
when moh_nnp_purchase_unit_code='COCH0020' then 'Child Abuse'
when moh_nnp_purchase_unit_code='SH01004' then 'Medical Management of Sexual Abuse'
end as codification_desc
,snz_moh_evt_uid AS event_id
,NULL AS relationship
FROM $(idicleanversion).moh_clean.nnpac
WHERE moh_nnp_purchase_unit_code IN ('SH01004', 'COCH0020', 'COCH0015')
/*COCH0015 - Child Protection
COCH0020 - Child Abuse
SH01004 - Medical Management of Sexual Abuse*/
DROP TABLE IF EXISTS #tmp_moh;
CREATE TABLE #tmp_moh (
snz_uid INT
, incident_start_date DATE
, incident_end_date DATE
, is_FV_flag SMALLINT
, is_SV_flag SMALLINT
, offender_flag SMALLINT
, victim_flag SMALLINT
, source_data VARCHAR(15)
, codification VARCHAR(1000)
, event_id VARCHAR(20)
, relationship VARCHAR(4000)
);
/*Public Hospitalisation*/
/*Victims of FVSV*/
INSERT INTO #tmp_moh (
snz_uid
,incident_start_date
,incident_end_date
,is_FV_flag
,is_SV_flag
,offender_flag
,victim_flag
,source_data
,codification
,event_id
,relationship
)
SELECT DISTINCT
a.snz_uid
,a.moh_evt_even_date AS incident_start_date
,a.moh_evt_even_date AS incident_end_date
,IIF(LEFT(b.moh_dia_clinical_code,3) = 'Y05' OR LEFT(b.moh_dia_clinical_code,4) IN ('T742', 'T762', 'Z040'), 0, 1) AS is_FV_flag
,IIF(LEFT(b.moh_dia_clinical_code,3) = 'Y05' OR LEFT(b.moh_dia_clinical_code,4) IN ('T742', 'T762', 'Z040'), 1, 0) AS is_SV_flag
,0 AS offender_flag
,1 AS victim_flag
,'MOH_PUB_HOSP' AS source_data
,b.moh_dia_clinical_code AS codification
,a.moh_evt_event_id_nbr AS event_id
,NULL AS relationship
FROM $(idicleanversion).moh_clean.pub_fund_hosp_discharges_event AS a
INNER JOIN $(idicleanversion).moh_clean.pub_fund_hosp_discharges_diag AS b
ON a.moh_evt_event_id_nbr = b.moh_dia_event_id_nbr
WHERE b.moh_dia_clinical_sys_code IN ('11', '12', '13', '14', '15')
/*diagnosis code related to FV*/
AND (
LEFT(b.moh_dia_clinical_code,3) IN ('T73','T74', 'T76','X85', 'X86', 'X87', 'X88', 'X89', 'Y01', 'Y02', 'Y03', 'Y04', 'Y05', 'Y06', 'Y08', 'Y09')
OR LEFT(b.moh_dia_clinical_code,4) IN ('Y060', 'Y061', 'Y871', 'Y872', 'Z614', 'Z615', 'Z616', 'Z630', 'Z918')
OR b.moh_dia_clinical_code IN ('Z69010', 'Z6911')
)
/*Traumatic brain injury or long bone fractures for child under 2 years of age*/
OR (
(
LEFT(b.moh_dia_clinical_code,3) IN ('S06', 'S52', 'S72', 'S82', 'T10', 'T12')
OR LEFT(b.moh_dia_clinical_code,4) IN ('S422','S423','S424','S427','S428')
)
AND DATEDIFF(MONTH, DATEFROMPARTS(a.moh_evt_birth_year_nbr, a.moh_evt_birth_month_nbr, 28), a.moh_evt_even_date) <= 24
)
/*Event of undetermined intent for child and young people under 14 years of age*/
OR (
(
LEFT(b.moh_dia_clinical_code,2) IN ('Y1', 'Y2')
OR LEFT(b.moh_dia_clinical_code,3) IN ('Y31', 'Y32', 'Y33', 'Y34')
OR LEFT(b.moh_dia_clinical_code,4) IN ('Z040','Z045','Z048')
)
AND DATEDIFF(MONTH, DATEFROMPARTS(a.moh_evt_birth_year_nbr, a.moh_evt_birth_month_nbr, 28), a.moh_evt_even_date) <= 168
)
AND a.moh_evt_nz_res_code = 'Y'
AND a.moh_evt_exclu_pu_code = 'INCLU'
/*Private Hospitalisation*/
/*Victims of FVSV*/
INSERT INTO #tmp_moh (
snz_uid
,incident_start_date
,incident_end_date
,is_FV_flag
,is_SV_flag
,offender_flag
,victim_flag
,source_data
,codification
,event_id
,relationship
)
SELECT DISTINCT
a.snz_uid
,a.moh_pri_evt_start_date AS incident_start_date
,a.moh_pri_evt_end_date AS incident_end_date
,IIF(LEFT(b.moh_pri_diag_clinic_code,3) = 'Y05' OR LEFT(b.moh_pri_diag_clinic_code,4) IN ('T742', 'T762', 'Z040'), 0, 1) AS is_FV_flag
,IIF(LEFT(b.moh_pri_diag_clinic_code,3) = 'Y05' OR LEFT(b.moh_pri_diag_clinic_code,4) IN ('T742', 'T762', 'Z040'), 1, 0) AS is_SV_flag
,0 AS offender_flag
,1 AS victim_flag
,'MOH_PRIV_HOSP' AS source_data
, b.moh_pri_diag_clinic_code AS codification
, a.moh_pri_evt_event_id_nbr AS event_id
,NULL as relationship
FROM $(idicleanversion).moh_clean.priv_fund_hosp_discharges_event AS a
INNER JOIN $(idicleanversion).moh_clean.priv_fund_hosp_discharges_diag AS b
ON a.moh_pri_evt_event_id_nbr = b.moh_pri_diag_event_id_nbr
WHERE b.moh_pri_diag_clinic_sys_code IN ('11', '12', '13', '14', '15')
/* diagnosis code related to FVSV*/
AND (
LEFT(b.moh_pri_diag_clinic_code,3) IN ('T73','T74', 'T76','X85', 'X86', 'X87', 'X88', 'X89', 'Y01', 'Y02', 'Y03', 'Y04', 'Y05', 'Y06', 'Y08', 'Y09')
OR LEFT(b.moh_pri_diag_clinic_code,4) IN ('Y060', 'Y061', 'Y871', 'Y872', 'Z614', 'Z615', 'Z616', 'Z630', 'Z918')
OR b.moh_pri_diag_clinic_code IN ('Z69010', 'Z6911')
)
/*Traumatic brain injury or long bone fractures for child under 2 years of age*/
OR (
(
LEFT(b.moh_pri_diag_clinic_code,3) IN ('S06', 'S52', 'S72', 'S82', 'T10', 'T12')
OR LEFT(b.moh_pri_diag_clinic_code,4) IN ('S422','S423','S424','S427','S428')
)
AND DATEDIFF(MONTH, DATEFROMPARTS(a.moh_pri_evt_birth_year_nbr, a.moh_pri_evt_birth_month_nbr, 28), a.moh_pri_evt_start_date) <= 24
)
/*Event of undetermined intent for child and young people under 14 years of age*/
OR (
(
LEFT(b.moh_pri_diag_clinic_code,2) IN ('Y1', 'Y2')
OR LEFT(b.moh_pri_diag_clinic_code,3) IN ('Y31', 'Y32', 'Y33', 'Y34')
OR LEFT(b.moh_pri_diag_clinic_code,4) IN ('Z040','Z045','Z048')
)
AND DATEDIFF(MONTH, DATEFROMPARTS(a.moh_pri_evt_birth_year_nbr, a.moh_pri_evt_birth_month_nbr, 28), a.moh_pri_evt_start_date) <= 168
)
/*Public Hospitalisation*/
/*Perpetrator of FVSV*/
INSERT INTO #tmp_moh (
snz_uid
,incident_start_date
,incident_end_date
,is_FV_flag
,is_SV_flag
,offender_flag
,victim_flag
,source_data
,codification
,event_id
,relationship
)
SELECT DISTINCT
a.snz_uid
,a.moh_evt_even_date AS incident_start_date
,a.moh_evt_even_date AS incident_end_date
,1 AS is_FV_flag
,0 AS is_SV_flag
,1 AS offender_flag
,0 AS victim_flag
,'MOH_PUB_HOSP' AS source_data
,b.moh_dia_clinical_code AS codification
,a.moh_evt_event_id_nbr AS event_id
,NULL as relationship
FROM $(idicleanversion).moh_clean.pub_fund_hosp_discharges_event AS a
LEFT JOIN $(idicleanversion).moh_clean.pub_fund_hosp_discharges_diag AS b
ON a.moh_evt_event_id_nbr = b.moh_dia_event_id_nbr
WHERE b.moh_dia_clinical_sys_code IN ('11', '12', '13', '14', '15')
AND (
LEFT(b.moh_dia_clinical_code,4) IN ('Y070', 'Y071', 'Y074')
OR b.moh_dia_clinical_code IN ('Z69011', 'Z6912')
)
/*Private Hospitalisation*/
/*Perpetrator of FVSV*/
INSERT INTO #tmp_moh (
snz_uid
,incident_start_date
,incident_end_date
,is_FV_flag
,is_SV_flag
,offender_flag
,victim_flag
,source_data
,codification
,event_id
,relationship
)
SELECT DISTINCT
a.snz_uid
,a.moh_pri_evt_start_date AS incident_start_date
,a.moh_pri_evt_end_date AS incident_end_date
,1 AS is_FV_flag
,0 AS is_SV_flag
,1 AS offender_flag
,0 AS victim_flag
,'MOH_PRIV_HOSP' AS source_data
,b.moh_pri_diag_clinic_code AS codification
,a.moh_pri_evt_event_id_nbr AS event_id
,NULL as relationship
FROM $(idicleanversion).moh_clean.priv_fund_hosp_discharges_event AS a
INNER JOIN $(idicleanversion).moh_clean.priv_fund_hosp_discharges_diag AS b
ON a.moh_pri_evt_event_id_nbr = b.moh_pri_diag_event_id_nbr
WHERE b.moh_pri_diag_clinic_sys_code IN ('11', '12', '13', '14', '15')
AND (
LEFT(b.moh_pri_diag_clinic_sys_code,4) IN ('Y070', 'Y071', 'Y074')
OR b.moh_pri_diag_clinic_sys_code IN ('Z69011', 'Z6912')
)
DROP TABLE IF EXISTS #temp_moh_final;
SELECT
a.snz_uid
,a.incident_start_date
,a.incident_end_date
,a.is_FV_flag
,a.is_SV_flag
,a.offender_flag
,a.victim_flag
,a.source_data
,a.codification
,b.codification_desc
,a.event_id
,a.relationship
INTO #temp_moh_final
FROM #tmp_moh AS a
LEFT JOIN #FVSV_codes_dia AS b
ON (LEFT(a.codification,3)) = b.codification
DROP TABLE IF EXISTS #defn_FVSV_all;
SELECT *
INTO #defn_FVSV_all
FROM (
SELECT *
FROM #tmp_pol_moj_final
UNION ALL
SELECT *
FROM #tmp_ot_acc
UNION ALL
SELECT *
FROM #temp_moh_final
) AS a
/*
Retrieve dob, death and ethnicity information from [data].[personal_detail]
Remove non-people ([snz_person_ind] = 1)
*/
/*retrieve dob, death and ethnicity information from [data].[personal_detail]*/
DROP TABLE IF EXISTS #tmp_demo_all;
SELECT
a.*
,b.snz_birth_date_proxy
,b.snz_sex_gender_code
,b.snz_ethnicity_grp1_nbr AS euro
,b.snz_ethnicity_grp2_nbr AS maori
,b.snz_ethnicity_grp3_nbr AS pacific
,b.snz_ethnicity_grp4_nbr AS asian
,b.snz_ethnicity_grp5_nbr AS melaa
,b.snz_ethnicity_grp6_nbr AS eth_other
,CASE
WHEN b.snz_deceased_year_nbr IS NOT NULL THEN CAST(CONCAT(b.snz_deceased_year_nbr,'-',b.snz_deceased_month_nbr,'-28') AS DATE)
ELSE NULL
END AS snz_deceased_date_proxy
,DATEDIFF(YEAR, b.snz_birth_date_proxy, a.incident_start_date) as age_at_incident
INTO #tmp_demo_all
FROM #defn_FVSV_all AS a
LEFT JOIN $(idicleanversion).data.personal_detail AS b
ON a.snz_uid = b.snz_uid
WHERE b.snz_person_ind = 1
ORDER BY a.snz_uid, a.incident_start_date
/* Remove absurd records */
/*records with missing dob*/
/*records with dob greater than first incidence date*/
DELETE
FROM #tmp_demo_all
WHERE EXISTS (
SELECT 1
WHERE (
incident_start_date < snz_birth_date_proxy
OR snz_birth_date_proxy IS NULL
)
)
/*records with deceased date less than any incidence dates*/
DELETE
FROM #tmp_demo_all
WHERE EXISTS (
SELECT 1
WHERE snz_deceased_date_proxy < incident_start_date )
/*records with court charges happened before age of 14 */
DELETE
FROM #tmp_demo_all
WHERE EXISTS (
SELECT 1
WHERE DATEDIFF(MONTH, snz_birth_date_proxy, incident_start_date) <= (14 * 12)
AND source_data = 'MOJ_CHARGES'
)
/*records of those born before 1920 (data patchy)*/
DELETE
FROM #tmp_demo_all
WHERE EXISTS (
SELECT 1
WHERE YEAR(snz_birth_date_proxy) < 1920
)
DROP TABLE IF EXISTS [$(targetdb)].[$(targetschema)].[$(projprefix)_family_sexual_violence];
CREATE TABLE [$(targetdb)].[$(targetschema)].[$(projprefix)_family_sexual_violence] (
snz_uid INT
,incident_start_date DATE
,incident_end_date DATE
,is_FV_flag SMALLINT
,is_SV_flag SMALLINT
,offender_flag SMALLINT
,victim_flag SMALLINT
,source_data VARCHAR(15)
,codification VARCHAR(1000)
,codification_desc NVARCHAR(MAX)
,event_id VARCHAR(20)
,relationship VARCHAR(4000)
,snz_birth_date_proxy DATE
,snz_sex_gender_code CHAR(1)
,euro SMALLINT
,maori SMALLINT
,pacific SMALLINT
,asian SMALLINT
,melaa SMALLINT
,eth_other SMALLINT
,age_at_incident INT
);
INSERT INTO [$(targetdb)].[$(targetschema)].[$(projprefix)_family_sexual_violence]
SELECT
snz_uid
,incident_start_date
,incident_end_date
,is_FV_flag
,is_SV_flag
,offender_flag
,victim_flag
,source_data
,codification
,codification_desc
,event_id
,relationship
,snz_birth_date_proxy
,snz_sex_gender_code
,euro
,maori
,pacific
,asian
,melaa
,eth_other
,age_at_incident
FROM #tmp_demo_all;
ALTER TABLE [$(targetdb)].[$(targetschema)].[$(projprefix)_family_sexual_violence]
REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = PAGE);
DROP TABLE IF EXISTS #defn_FVSV_all
DROP TABLE IF EXISTS #tmp_demo_all
DROP TABLE IF EXISTS #tmp_pol_moj_final
DROP TABLE IF EXISTS #tmp_ot_acc
DROP TABLE IF EXISTS #temp_moh_final