Changes between Initial Version and Version 1 of AdvancedReports

Timestamp:

01/15/24 22:15:37 (11 months ago)

Author:

212012

Comment:

--

AdvancedReports

@@ +1 @@
+= Напредни извештаи од базата (SQL и складирани процедури) =
+
+=== Извештај за сите решени случаеви од минатата година кој што
+ги водат полициските станици во секорот за внатрешни работи во Скопје. Дополнително за секој решен случај да се пресмета бројот на дадени изјави  ===
+{{{#!sql
+select cc.c_id as c_id,cc.c_name as c_name,
+cc.opening_date as opening_date,sc.date_of_closing as date_of_closing,
+sc.final_document as final_document,
+(select count(*) from statements  where c_id=cc.c_id) as broj_na_izjavi
+from
+sector_of_interal_affairs sia
+join police_station ps on sia.s_id=ps.s_id
+join crime_case cc on ps.p_id=cc.p_id
+join solved_case sc on cc.c_id=sc.c_id
+where
+sia.city ~ '(Skopje|Скопје)'
+}}}
+
+=== Извештај за секоја полициска станица, колку вработени полицајци има, колку од нив се вработени во последните 2 години и колку од нив се со македонска националност ===
+{{{#!sql
+select ps.p_id, ps.p_address,
+        count(po.pe_id) as vkupno_policajci,
+        (select count(*)
+                        from people p
+                        join policeman pol on p.pe_id=pol.pe_id
+                        where
+                        p.nationality='Македонец' and pol.p_id=ps.p_id) as Makedonci_nachalnici_i_policajci,
+        (select count(*) from policeman pol
+        where p_date_of_employment between now()-interval '2 years' and now()
+        and pol.p_id=ps.p_id ) as Vraboteni_vo_interval_2_god
+from police_station ps
+join policeman po on po.p_id = ps.p_id
+group by ps.p_id;
+}}}
+
+=== Извештај за секоја сектор за внатрешни работи, колку вработени има во сите полициски станици секторот кои што имаат ранг поголем од 2 и се помлади од 30 години ===
+{{{#!sql
+select sia.s_id as sector_id, sia.city as sector_city,
+ps.p_id as police_station_id, ps.p_address as police_station_address,
+(select count(*) from 
+ people pe 
+ join policeman pl on pe.pe_id=pl.pe_id
+ where rank>2 and pl.p_id=ps.p_id) as broj_na_vraboteni_so_rang_pogolem_od_2,
+ (select count(*) from 
+    crime_case cc
+    join solved_case  sc on cc.c_id=sc.c_id
+    where
+    cc.p_id=ps.p_id
+ ) as broj_na_resheni_slucai
+from sector_of_interal_affairs sia
+join police_station ps on sia.s_id=ps.s_id
+join policeman p on ps.p_id=p.p_id
+}}}
+
+=== Извештај за секоја полициска станица, колку случаи биле отворени со тип на криминал ‘Семејно насилство’ и колку жртви биле жени од ромско потекло  ===
+{{{#!sql
+select ps.p_id, ps.p_address, 
+        (
+        select count(cs.c_id) 
+        from police_station ps
+        join crime_case cs on cs.p_id = ps.p_id
+        join statements st on st.c_id = cs.c_id
+        join victim vic on vic.pe_id = st.victim_pe_id
+        join people pe on pe.pe_id = vic.pe_id
+        join case_belongs_to_type_of_crime cbtt on cbtt.c_id = cs.c_id
+        join type_of_crime toc on toc.t_id = cbtt.t_id
+        where pe.gender = 'Ж' and toc.t_name = 'Семејно насилство' 
+        and pe.nationality = 'Ромка'
+        ) as vk_sluchaevi_zheni_zhrtvi
+from police_station ps
+group by ps.p_id;
+}}}
+
+=== Извештај за секоја полициска станица кој полицаец има земено најмногу изјави во последната година ===
+{{{#!sql
+with stanici_policajci_izjavi
+         as (select ps.p_id, ps.p_address, pm.pe_id, pm.badge_no, count(s.c_id) br_izjavi
+             from police_station ps
+                      left join policeman pm on ps.p_id = pm.p_id
+                      left join statements s on pm.pe_id = s.pe_id
+             group by ps.p_id, ps.p_address, pm.pe_id, pm.badge_no),
+     stanici_max_izjavi
+         as (select psi.p_id, psi.p_address, max(psi.br_izjavi) max_izjavi
+             from stanici_policajci_izjavi psi
+             group by psi.p_id, psi.p_address)
+
+select psi.p_id, psi.p_address, psi.pe_id, psi.badge_no, psi.br_izjavi
+from stanici_policajci_izjavi psi
+         join stanici_max_izjavi smi
+              on (psi.p_id, psi.p_address, psi.br_izjavi) = (smi.p_id, smi.p_address, smi.max_izjavi)
+}}}
+
+=== Извештај за секој тип на криминал која полициска станица има отворено најмногу случаеви со тој тип на криминал ===
+{{{#!sql
+SELECT 
+    toc.t_id AS type_id,
+    toc.t_name AS type_name,
+    ps.p_id AS station_id,
+    ps.p_address ,
+    COUNT(cs.c_id) AS case_count
+FROM 
+    type_of_crime toc
+JOIN case_belongs_to_type_of_crime cbtt ON toc.t_id = cbtt.t_id
+JOIN crime_case cs ON cbtt.c_id = cs.c_id
+JOIN police_station ps ON cs.p_id = ps.p_id
+GROUP BY 
+    toc.t_id, toc.t_name, ps.p_id, ps.p_address 
+HAVING 
+    COUNT(cs.c_id) = (
+        SELECT 
+            MAX(case_count)
+        FROM (
+            SELECT 
+                toc_sub.t_id AS type_id,
+                COUNT(cs_sub.c_id) AS case_count
+            FROM 
+                type_of_crime toc_sub
+            JOIN 
+                case_belongs_to_type_of_crime cbtt_sub ON toc_sub.t_id = cbtt_sub.t_id
+            JOIN 
+                crime_case cs_sub ON cbtt_sub.c_id = cs_sub.c_id
+            GROUP BY 
+                toc_sub.t_id
+        ) max_cases
+        WHERE max_cases.type_id = toc.t_id
+    )
+ORDER BY 
+    toc.t_id, case_count DESC;
+}}}