Changes between Version 20 and Version 21 of AdvancedReports

Timestamp:

01/30/22 01:58:13 (3 years ago)

Author:

183060

Comment:

--

AdvancedReports

@@ -5 +5 @@
 
 {{{#!sql
-select d2.doktor_id, c.ime, c.prezime, b.naziv as bolnica, o.naziv as oddel, b.grad, 
-        case when pregledi_quarter.trimesecje = 1 then pregledi_quarter.pregledi else 0 end as quarter_1_pregledi,
-        case when pregledi_quarter.trimesecje = 2 then pregledi_quarter.pregledi else 0 end as quarter_2_pregledi,
-        case when pregledi_quarter.trimesecje = 3 then pregledi_quarter.pregledi else 0 end as quarter_3_pregledi,
-        case when pregledi_quarter.trimesecje = 4 then pregledi_quarter.pregledi else 0 end as quarter_4_pregledi,
+
+select distinct d.doktor_id, c.ime, c.prezime, b.naziv as bolnica, o.naziv as oddel, b.grad,
+        case when pregledi_od_doktor.trimesecje = 1 then pregledi_od_doktor.pregledi else 0 end as quarter_1_pregledi,
+        case when pregledi_od_doktor.trimesecje = 2 then pregledi_od_doktor.pregledi else 0 end as quarter_2_pregledi,
+        case when pregledi_od_doktor.trimesecje = 3 then pregledi_od_doktor.pregledi else 0 end as quarter_3_pregledi,
+        case when pregledi_od_doktor.trimesecje = 4 then pregledi_od_doktor.pregledi else 0 end as quarter_4_pregledi,
         
-        case when zakazani_quarter.trimesecje = 1 then zakazani_quarter.zakazani_termini else 0 end as quarter_1_zakazani_termini,
-        case when zakazani_quarter.trimesecje = 2 then zakazani_quarter.zakazani_termini else 0 end as quarter_2_zakazani_termini,
-        case when zakazani_quarter.trimesecje = 3 then zakazani_quarter.zakazani_termini else 0 end as quarter_3_zakazani_termini,
-        case when zakazani_quarter.trimesecje = 4 then zakazani_quarter.zakazani_termini else 0 end as quarter_4_zakazani_termini,
+        case when upati_od_doktor.trimesecje = 1 then upati_od_doktor.upati else 0 end as quarter_1_upati_od_pregled,
+        case when upati_od_doktor.trimesecje = 2 then upati_od_doktor.upati else 0 end as quarter_2_upati_od_pregled,
+        case when upati_od_doktor.trimesecje = 3 then upati_od_doktor.upati else 0 end as quarter_3_upati_od_pregled,
+        case when upati_od_doktor.trimesecje = 4 then upati_od_doktor.upati else 0 end as quarter_4_upati_od_pregled,
         
-        case when upati_quarter.trimesecje = 1 then upati_quarter.upati_od_pregled else 0 end as quarter_1_upati_od_pregled,
-        case when upati_quarter.trimesecje = 2 then upati_quarter.upati_od_pregled else 0 end as quarter_2_upati_od_pregled,
-        case when upati_quarter.trimesecje = 3 then upati_quarter.upati_od_pregled else 0 end as quarter_3_upati_od_pregled,
-        case when upati_quarter.trimesecje = 4 then upati_quarter.upati_od_pregled else 0 end as quarter_4_upati_od_pregled
-from bolnica b 
-join oddel o on b.bolnica_id = o.bolnica_id 
-join doktor d2 on (o.oddel_id, o.bolnica_id) = (d2.oddel_id, d2.bolnica_id)
-join covek c on d2.doktor_id = c.covek_id 
-left join (
-        select extract (quarter from p.vreme) as trimesecje, count (p.pregled_id) as pregledi,
-                d.doktor_id , o.naziv as oddel, b.naziv as bolnica
-        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)
-        join bolnica b on o.bolnica_id = b.bolnica_id 
-        where extract (year from p.vreme) = extract (year from current_date)
-        group by 1, 3, 4, 5 
-) pregledi_quarter on d2.doktor_id = pregledi_quarter.doktor_id
-left join (
-        select r.doktor_id, count(r.rezervacija_id) zakazani_termini,
-                extract (quarter from t.vreme) as trimesecje
-        from rezervacija r 
-        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, 3
-) zakazani_quarter on d2.doktor_id = zakazani_quarter.doktor_id
-left join (
-        select p.doktor_id , count (u.upat_id) as upati_od_pregled,
-                extract (quarter from p.vreme) as trimesecje
-        from pregled p 
-        join upat u on p.pregled_id = u.pregled_id 
-        where extract (year from p.vreme) = extract (year from current_date)
-        group by 1, 3 
-) upati_quarter on d2.doktor_id = upati_quarter.doktor_id ;
+        case when zakazani_termini_kaj_doktor.trimesecje = 1 then zakazani_termini_kaj_doktor.rezervacii else 0 end as quarter_1_zakazani_termini,
+        case when zakazani_termini_kaj_doktor.trimesecje = 2 then zakazani_termini_kaj_doktor.rezervacii else 0 end as quarter_2_zakazani_termini,
+        case when zakazani_termini_kaj_doktor.trimesecje = 3 then zakazani_termini_kaj_doktor.rezervacii else 0 end as quarter_3_zakazani_termini,
+        case when zakazani_termini_kaj_doktor.trimesecje = 4 then zakazani_termini_kaj_doktor.rezervacii else 0 end as quarter_4_zakazani_termini
+from doktor d 
+left join pregled p on d.doktor_id = p.doktor_id 
+left join termin t on d.doktor_id = t.doktor_id 
+join covek c on d.doktor_id = c.covek_id 
+join oddel o on (d.oddel_id, d.bolnica_id) = (o.oddel_id, o.bolnica_id) 
+join bolnica b on o.bolnica_id = b.bolnica_id 
+left join (
+        select extract (quarter from p2.vreme) as trimesecje, d3.doktor_id, count(p2.pregled_id) as pregledi
+        from doktor d3 
+        left join pregled p2 on d3.doktor_id = p2.doktor_id 
+        where extract (year from p2.vreme) = extract (year from current_date)
+        group by 1, 2
+) pregledi_od_doktor on d.doktor_id = pregledi_od_doktor.doktor_id
+left join (
+        select d3.doktor_id, extract (quarter from p2.vreme) as trimesecje, count(u.upat_id) as upati
+        from doktor d3 
+        left join pregled p2 on d3.doktor_id = p2.doktor_id 
+        left join upat u on p2.pregled_id = u.pregled_id 
+        where extract (year from p2.vreme) = extract (year from current_date)
+        group by 1, 2
+) upati_od_doktor on d.doktor_id = upati_od_doktor.doktor_id
+left join (
+        select t3.doktor_id, extract (quarter from t3.vreme) as trimesecje, count(r.rezervacija_id) as rezervacii
+        from upat u 
+        left join rezervacija r on u.upat_id = r.upat_id 
+        left join termin t3 on (r.termin_id, r.doktor_id) = (t3.termin_id, t3.doktor_id) 
+        where extract (year from t3.vreme) = extract (year from current_date)
+        group by 1, 2
+) zakazani_termini_kaj_doktor on d.doktor_id = zakazani_termini_kaj_doktor.doktor_id ;
+
 }}}