Här samlas de SQL frågor som behövs för att skapa gemensamma uppslagstabeller för SIV

Öppna databas där SIV finns. I detta exempel har Dbeaver används som verktyg men PSQL går lika bra

Schema skfab innehåller SIV databasen. Temporärt schema matchning skapas

create schema matchning;

Skadekategorier

Tabell för att lagra gemensamma kategorier (categories_generic). De finns på svenska , norska och engelska

/* create and update table matchning.categories_generic */
create table matchning.categories_generic
(
id serial,
namn_sv text,
namn_no text,
namn_en text
)
-- drop table matchning.categories_generic

De vanligast förkommande categorizer hämtas in från SIV kategorier. De som förkommer mer än tre gånger. Översättning sker till norska och svenska. Norska orden används också vid konverteringen

INSERT INTO matchning.categories_generic (namn_sv)
select q.name
from
(
select name, count(1) as number
from skfab.damage_categories c
group by 1
order by 2 desc 
) q
where number > 2 and name <> '-odefinierad-'

update matchning.categories_generic set namn_no = 'Uhell', namn_en ='Accident'
where namn_sv = 'Olyckshändelse'
update matchning.categories_generic set namn_no = 'Ansvar', namn_en ='Liability'
where namn_sv = 'Ansvar'
update matchning.categories_generic set namn_no = 'Egendom', namn_en ='Property'
where namn_sv = 'Egendom'
update matchning.categories_generic set namn_no = 'Med overlegg', namn_en ='Intent'
where namn_sv = 'Uppsåt'

--select * from matchning.categories_generic

Tabellen som skapas ser ut så här

Id	namn_sv	namn_no	namn_en
4	Olyckshändelse	Uhell	Accident
1	Ansvar	Ansvar	Liability
3	Egendom	Egendom	Property
2	Uppsåt	Med overlegg	Intent

Nästa steg är att skapa en kors-referens-tabell med hjälp av befintlig kategoritabell (skfab.damage_categories) och sen lägga till en kolumn med referens till den generiska tabellen

/* create and update table matchning.categories_crossref */
create table matchning.categories_crossref
as
(
select damage_category_id, name from skfab.damage_categories dc 
)
alter table matchning.categories_crossref add column generic_category_id integer;

Uppdaterakors-referens-tabell categories_crossref så att all kategorier matchar varandra

--Uppdatera först de kategorier som matchar med de generiska på svenska och norska
update matchning.categories_crossref set generic_category_id = id from categories_generic 
where matchning.categories_crossref.name in (categories_generic.namn_sv, categories_generic.namn_no)
-- Fortsätt sedan att uppdatera de kategorier som inte är matchade
update matchning.categories_crossref set generic_category_id = 3 --egendom
where matchning.categories_crossref.name in ('Byggfel, bristande underhåll', 'Byggfel/bristande underhåll', 'Byggfel/Bristande underhåll', 'Byggnadsfel' )
update matchning.categories_crossref set generic_category_id = 4 --Olyckshändelse
where matchning.categories_crossref.name in ('Olycka','Olycka (tillbud)' )
update matchning.categories_crossref set generic_category_id = 2 --uppsåt
where matchning.categories_crossref.name in ('SD Utemiljö','SK Skadegörelse','Fordonsskador','Oaktsamhet' )
update matchning.categories_crossref set generic_category_id = 1 --ansvar
where matchning.categories_crossref.name in ('.','-odefinierad-','Tillbud','Övrigt','Snöröjning','Väderpåverkan' )

För att testa att allt är matchat så används

select damage_category_id,name, g.namn_sv 
from matchning.categories_crossref
join categories_generic g on generic_category_id = g.id

Skadetyper

Samma procedur används för skadetyper

/* create and update table matchning.categories_generic */
create table matchning.types_generic
(
id serial,
namn_sv text,
namn_no text,
namn_en text
)
-- drop table matchning.types_generic

INSERT INTO matchning.types_generic (namn_sv)
select q.name
from
(
select distinct name, count(1) as number
from skfab.damage_types c
group by 1
order by 2 desc
) q
where number > 3 and name not in 
('-odefinierad-', 'Annet', 'Hærverk','Inventarier odyl','Ruteknusing',
'Tagging','Vann','Vattenskada','Övriga skador','Skadegörelse')

update matchning.types_generic set namn_no = 'Ansvar', namn_en ='Liability'
where namn_sv = 'Ansvar';
update matchning.types_generic set namn_no = 'Avløp', namn_en ='Drainage'
where namn_sv = 'Avlopp';
update matchning.types_generic set namn_no = 'Brann', namn_en ='Fire'
where namn_sv = 'Brand';
update matchning.types_generic set namn_no = 'Eiendom', namn_en ='Property'
where namn_sv = 'Egendom';
update matchning.types_generic set namn_no = 'Glassknuser', namn_en ='Glass breaker'
where namn_sv = 'Glaskross';
update matchning.types_generic set namn_no = 'Innbrudd', namn_en ='Burglary'
where namn_sv = 'Inbrott';
update matchning.types_generic set namn_no = 'Doodle', namn_en ='Doodle'
where namn_sv = 'Klotter';
update matchning.types_generic set namn_no = 'Maskin', namn_en ='Machine'
where namn_sv = 'Maskin';
update matchning.types_generic set namn_no = 'Skade', namn_en ='Damage'
where namn_sv = 'Skada';
update matchning.types_generic set namn_no = 'Tyveri', namn_en ='Theft'
where namn_sv = 'Stöld';
update matchning.types_generic set namn_no = 'Vann', namn_en ='Water'
where namn_sv = 'Vatten';
update matchning.types_generic set namn_no = 'Annen', namn_en ='Other'
where namn_sv = 'Övrigt';
update matchning.types_generic set namn_no = 'Personlig', namn_en ='Personal', namn_sv='Person'
where id = 8 ;
update matchning.types_generic set namn_no = 'Forbrytelse', namn_en ='Crime', namn_sv='Brottslihet'
where id = 5 ;

Tabellen ser ut så här

id	namn_sv	namn_no	namn_en
1	Övrigt	Annen	Other
2	Brand	Brann	Fire
3	Vatten	Vann	Water
4	Glaskross	Glassknuser	Glass breaker
5	Brottslihet	Forbrytelse	Crime
6	Skada	Skade	Damage
7	Maskin	Maskin	Machine
8	Person	Personlig	Personal
9	Ansvar	Ansvar	Liability
10	Klotter	Doodle	Doodle
11	Egendom	Eiendom	Property
12	Avlopp	Avløp	Drainage

Skapa en kors-referens-tabell med hjälp av befintlig typ-tabell

Uppdatera de vanligaste och komplettera sen uppdateringen med de udda

/* create and update table matchning.categories_types
 * drop table matchning.types_crossref */
create table matchning.types_crossref
as
(
select damage_type_id, name from skfab.damage_types dc 
);
alter table matchning.types_crossref add column generic_type_id integer;
--Uppdatera de vanligaste typerna
update matchning.types_crossref  set generic_type_id = id from types_generic  
where matchning.types_crossref.name in (types_generic.namn_sv, types_generic.namn_no);
--Uppdarera de udda typerna
update matchning.types_crossref set generic_type_id = 1
where matchning.types_crossref.name in ('.','Övriga skador','Annet','Annan ansvarsskada','-odefinierad-','Annan','Storm','Tillbud' );
update matchning.types_crossref set generic_type_id = 2
where matchning.types_crossref.name in ('Brandtillbud');
update matchning.types_crossref set generic_type_id = 3 --Vatten
where matchning.types_crossref.name in ('Vattenskada','Fukt/mögel','Frysskada','Översvämning','Vattenskada (i egna fastigheter)' );
update matchning.types_crossref set generic_type_id = 4 --Glaskross
where matchning.types_crossref.name in ('Glas','Ruteknusing' );
update matchning.types_crossref set generic_type_id = 5 --brott
where matchning.types_crossref.name in ('Rån','Inbrottsskada','Ofredande','Hot','Inbrottsskada','Sabotage','Stöld','Innbrudd','Tyveri','Inbrott' );
update matchning.types_crossref set generic_type_id = 6 --Skada
where matchning.types_crossref.name in ('Hærverk','Skadegörelse','Annan skadegörelse','Sakskada' );
update matchning.types_crossref set generic_type_id = 7 --Maskin
where matchning.types_crossref.name in ('Motor','Fordon','Privata fordone','Maskiner','Tunga fordon','Maskinskada','Privata fordon','Fordonsskada',
'Kommunens tjänstebilar','Motor','Transports fordon','Transport','Plogskada','Vägskada','Plogskada','Transports fordon' );
update matchning.types_crossref set generic_type_id = 8 --Person
where matchning.types_crossref.name in ('Tandskada','Kroppskada','Patient','Nackskada','Person','Halkolycka','Personskada','Patientskada','Sårskada','Fallskada','Personskade','Sekundär drunkning','Olyckshändelse','Stroke' );
update matchning.types_crossref set generic_type_id = 9 --Ansvar
where matchning.types_crossref.name in ('Myndighetsskada','Förmögenhetsskada','Diskriminering','KÖV','Oaktsamhet','Produkt','Kärl',
'Skadeståndsansvar','Påkörning','Slarv','Okänd','Generellt ansvar','Explosion','Allrisk','IT-incident' );
update matchning.types_crossref set generic_type_id = 10 --klotter
where matchning.types_crossref.name in ('Tagging');
update matchning.types_crossref set generic_type_id = 11 --egendom
where matchning.types_crossref.name in ('Inventarier odyl','Mekanisk/elektrisk','Mögel/Fukt','Gatu-/Parkbelysning','Bristande underhåll',
'Byggfel','Entreprenad','Mögel, fukt','Skadad egendom','Snölast','Miljö/byggherreansvar','El','Praktik','LOU-skada','Mögel/fukt');
update matchning.types_crossref set generic_type_id = 12 --Avlopp / miljö
where matchning.types_crossref.name in ('VA-skada','Miljöskada','Nedebördsskada','Olja','Naturskade','VA-skada','VA','Miljöutredning');

select damage_type_id,name, g.namn_sv 
from matchning.types_crossref
join types_generic g on generic_type_id = g.id

`Uppdatera SKFAB schemat`

Tabellerna skfab.damage_categories och skfab.damage_types uppdateras båda med en ny kolumn generic. Värden från den tillfälliga tabellerna matchning.categories_crossref och matchning.types_crossref uppdaras till skfab.damage_categories och skfab.damage_types

Redigerings- och sökformulär måste också uppdateras

-- Uppdatera skfab.damage_categories från den tillfälliga matchning.categories_crossref
alter table skfab.damage_categories add column generic integer; 
-- alter table skfab.damage_categories drop column generic; 
update skfab.damage_categories set generic=generic_category_id from  matchning.categories_crossref
where  skfab.damage_categories.damage_category_id = matchning.categories_crossref.damage_category_id;
-- Uppdatera skfab.damage_categories från den tillfälliga matchning.categories_crossref
alter table skfab.damage_types add column generic integer;
-- alter table skfab.damage_types drop column generic;
update skfab.damage_types set generic=generic_type_id from  matchning.types_crossref
where  skfab.damage_types.damage_type_id = matchning.types_crossref.damage_type_id

Generiska tabeller flyttas till skfab

-- flytta de generiska tabellern till schema skfab
create table skfab.categories_generic as table matchning.categories_generic;
create table skfab.types_generic as table matchning.types_generic;
-- drop table skfab.categories_generic
-- drop table skfab.types_generic

Testning

Nu testas att skriva ut skador med generiska kategorier och typer

--testa att relatione till generiska kategorier och typer finns
--Använd norska då kunen nummer är 100108
select d.customer_link,damage_id, 
damage_date,
category_link, 
dc."name", 
case when d.customer_link = 100108 then cg.namn_no else cg.namn_sv end ,
d.type_link ,
dt."name" ,
case when d.customer_link = 100108 then tg.namn_no else tg.namn_sv end ,
description
from skfab.damages d
join skfab.damage_categories dc on dc.damage_category_id = d.category_link 
join skfab.categories_generic cg on cg.id = dc.generic 
join skfab.damage_types dt on dt.damage_type_id = d.type_link
join skfab.types_generic tg on tg.id = dt.generic

Grupper på kategorier och typer med antal och total skadekostnad

--Gruppera på generiska
--Generiska kategorier
select cg.namn_sv, count(*) as antal, sum(d.cost_def) as kostnad
from skfab.damages d
join skfab.damage_categories dc on dc.damage_category_id = d.category_link 
join skfab.categories_generic cg on cg.id = dc.generic 
group by 1
--Generiska typer
select tg.namn_sv, count(*) as antal, sum(d.cost_def) as kostnad
from skfab.damages d
join skfab.damage_types dt on dt.damage_type_id = d.type_link
join skfab.types_generic tg on tg.id = dt.generic 
group by 1

Stäng och ta bort tillfälligt schema

Med cascade tas både schema och dess tabeller bort

drop schema matchning cascade;

Lägga till gemensamma uppslagstabeller för SIV

Skadekategorier

Skadetyper

Uppdatera SKFAB schemat

Testning

Stäng och ta bort tillfälligt schema

`Uppdatera SKFAB schemat`