Changes between Version 12 and Version 13 of AdvancedReports

Timestamp:

01/25/22 18:19:47 (3 years ago)

Author:

183060

Comment:

--

AdvancedReports

@@ -1 +1 @@
 = Напредни извештаи од базата (SQL и складирани процедури)
+
+
+== Број на извршени прегледи од секој доктор, број на издадени упати од секој доктор и број на закажани термини кај секој доктор, за секое тримесечје, за тековната година
+
+{{{#!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,
+        
+        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_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 trimesecje, 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 ;
+}}}
+
+
 
 == Пациент на кој му се извршени највеќе прегледи