Changes between Version 17 and Version 18 of AdvancedReports

Timestamp:

01/26/22 22:36:29 (3 years ago)

Author:

183060

Comment:

--

AdvancedReports

@@ -54 +54 @@
 
 
-== Број на пациенти кои биле на преглед во даден оддел и број на упати кои биле издадени за во оддел после извршен преглед, за секое тримесечје, за тековната година
-
-{{{#!sql
-select distinct o.naziv as oddel, b.naziv as bolnica,
-        case when pregled_quarter.trimesecje = 1 then pacienti_quarter.pacienti else 0 end as quarter_1_pacienti,
-        case when pregled_quarter.trimesecje = 2 then pacienti_quarter.pacienti else 0 end as quarter_2_pacienti,
-        case when pregled_quarter.trimesecje = 3 then pacienti_quarter.pacienti else 0 end as quarter_3_pacienti,
-        case when pregled_quarter.trimesecje = 4 then pacienti_quarter.pacienti else 0 end as quarter_4_pacienti,
+== Број на пациенти кои биле на преглед во даден оддел и број на упати кои биле/се издадени за во оддел, за секое тримесечје, за тековната година
+
+{{{#!sql
+select o.naziv as oddel, b.naziv as bolnica,
+        case when pacienti_quarter.trimesecje = 1 then pacienti_quarter.pacienti else 0 end as quarter_1_pacienti,
+        case when pacienti_quarter.trimesecje = 2 then pacienti_quarter.pacienti else 0 end as quarter_2_pacienti,
+        case when pacienti_quarter.trimesecje = 3 then pacienti_quarter.pacienti else 0 end as quarter_3_pacienti,
+        case when pacienti_quarter.trimesecje = 4 then pacienti_quarter.pacienti else 0 end as quarter_4_pacienti,
         
-        case when pregled_quarter.trimesecje = 1 then upati_quarter.upati else 0 end as quarter_1_upati,
-        case when pregled_quarter.trimesecje = 2 then upati_quarter.upati else 0 end as quarter_2_upati,
-        case when pregled_quarter.trimesecje = 3 then upati_quarter.upati else 0 end as quarter_3_upati,
-        case when pregled_quarter.trimesecje = 4 then upati_quarter.upati else 0 end as quarter_4_upati
+        case when upati_quarter.trimesecje = 1 then upati_quarter.upati else 0 end as quarter_1_upati,
+        case when upati_quarter.trimesecje = 2 then upati_quarter.upati else 0 end as quarter_2_upati,
+        case when upati_quarter.trimesecje = 3 then upati_quarter.upati else 0 end as quarter_3_upati,
+        case when upati_quarter.trimesecje = 4 then upati_quarter.upati else 0 end as quarter_4_upati
 from oddel o 
 join bolnica b on o.bolnica_id = b.bolnica_id 
-join (
-        select o.oddel_id, o.bolnica_id, extract (quarter from p.vreme) as trimesecje
-        from pregled p
-        join doktor d on p.doktor_id = d.doktor_id 
-        join oddel o on (d.oddel_id, d.bolnica_id) = (o.oddel_id, o.bolnica_id)
-) pregled_quarter on (o.oddel_id, o.bolnica_id) = (pregled_quarter.oddel_id, pregled_quarter.bolnica_id)
-left join (
-        select o.oddel_id, o.bolnica_id, count (p.covek_pacient_id) as pacienti
+left join (
+        select o.oddel_id, o.bolnica_id, count (p.covek_pacient_id) as pacienti,
+                extract (quarter from p.vreme) as trimesecje
         from pregled p 
         join doktor d on p.doktor_id = d.doktor_id 
@@ -82 +77 @@
         join bolnica b on o.bolnica_id = b.bolnica_id 
         where extract (year from p.vreme) = extract (year from current_date)
-        group by 1, 2
+        group by 1, 2, 4
 ) pacienti_quarter on (o.oddel_id, o.bolnica_id) = (pacienti_quarter.oddel_id, pacienti_quarter.bolnica_id)     
 left join (
-        select u.oddel_id, u.bolnica_id, count(u.upat_id) as upati
+        select u.oddel_id, u.bolnica_id, count(u.upat_id) as upati,
+                extract (quarter from t.vreme) as trimesecje
         from upat u 
-        join pregled p on u.pregled_id = p.pregled_id 
-        where extract (year from p.vreme) = extract (year from current_date) 
-        group by 1, 2
+        join rezervacija r on u.upat_id = r.upat_id 
+        join termin t on (r.termin_id, r.doktor_id) = (t.termin_id, t.doktor_id)
+        where extract (year from t.vreme) = extract (year from current_date) 
+        group by 1, 2, 4
 ) upati_quarter on (o.oddel_id, o.bolnica_id) = (upati_quarter.oddel_id, upati_quarter.bolnica_id) ;
 }}}