Changes between Version 15 and Version 16 of AdvancedReports

Timestamp:

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

Author:

183060

Comment:

--

AdvancedReports

@@ -90 +90 @@
         where extract (year from p.vreme) = extract (year from current_date) 
         group by 1, 2
-) upati_quarter on (o.oddel_id, o.bolnica_id) = (upati_quarter.oddel_id, upati_quarter.bolnica_id)
+) upati_quarter on (o.oddel_id, o.bolnica_id) = (upati_quarter.oddel_id, upati_quarter.bolnica_id) ;
+}}}
+
+
+
+== Тренд на препишани лекови по тримесечја за тековната година
+
+{{{#!sql
+select distinct l.ime_lek,
+        case when recepta_quarter.trimesecje = 1 then recepti_za_lekovi.recepti_za_lek else 0 end as quarter_1_recepti_za_lek,
+        case when recepta_quarter.trimesecje = 2 then recepti_za_lekovi.recepti_za_lek else 0 end as quarter_2_recepti_za_lek,
+        case when recepta_quarter.trimesecje = 3 then recepti_za_lekovi.recepti_za_lek else 0 end as quarter_3_recepti_za_lek,
+        case when recepta_quarter.trimesecje = 4 then recepti_za_lekovi.recepti_za_lek else 0 end as quarter_4_recepti_za_lek
+from recepta r
+join lekovi l on r.lek_id = l.lek_id
+join (
+        select r.recepta_id, extract (quarter from r.datum_izdavanje) as trimesecje
+        from recepta r 
+) recepta_quarter on r.recepta_id = recepta_quarter.recepta_id
+join (
+        select r.lek_id, count(r.recepta_id) as recepti_za_lek
+        from recepta r
+        where extract (year from r.datum_izdavanje) = extract (year from current_date)
+        group by 1
+) recepti_za_lekovi on r.lek_id = recepti_za_lekovi.lek_id
+order by 2 desc, 3 desc, 4 desc, 5 desc ;
 }}}