Changes between Version 3 and Version 4 of AdvancedReports

Timestamp:

12/28/23 16:12:24 (6 months ago)

Author:

090810

Comment:

--

AdvancedReports

@@ -74 +74 @@
 join covek c on d.id = c.id;
 }}}
+==Вкупна количина на тип на медикамент за секој дијабетичар што ја поседува==
+{{{
+#!sql
+
+select
+    c.id,
+    c.ime,
+    c.prezime,
+    c.pol,
+    c.datum_na_ragjanje,
+    m.tip_medikament,
+    m.ime as medikament_ime,
+    m.proizvoditel as medikament_proizvoditel,
+    ti.ime as tip_insulin,
+    sum(kolicina) as vkupna_kolicina
+from
+    dijabeticar d
+join
+    covek c on d.id = c.id
+join 
+    tip_dijabetes td on d.tip_dijabetes_id = td.id
+join
+    inventar i on d.id = i.dijabeticar_id
+join
+    medikament m on i.medikament_id = m.id
+left join
+    insulin ins on m.id = ins.id
+left join
+    tip_insulin ti on ins.tip_insulin_id = ti.id
+group by
+    c.id,
+    c.ime,
+    c.prezime,
+    c.pol,
+    c.datum_na_ragjanje,
+    m.tip_medikament,
+    m.ime,
+    m.proizvoditel,
+    ti.ime;
+}}}
+==Просечно внесени калории за секој дијабетичар во последните 3 месеци==
+{{{
+#!sql
+
+select
+    d.id as dijabeticar_id,
+    c.ime as dijabeticar_ime,
+    c.prezime as dijabeticar_prezime,
+    AVG(h.kalorii) as prosecni_kalorii_posledni_3_meseci
+from
+    dijabeticar d
+join
+    covek c on d.id = c.id
+join
+    zapis z on d.id = z.dijabeticar_id
+join
+    zapis_hrana zh on z.dijabeticar_id = zh.dijabeticar_id and z."data" = zh."data"
+join
+    obrok o on zh.dijabeticar_id = o.dijabeticar_id and zh."data" = o."data"
+join
+    hrana h on o.hrana_id = h.id
+where
+    z."data" >= date_trunc('month', current_date - interval '3 months') 
+group by
+    d.id, 
+    c.ime, 
+    c.prezime;
+}}}
+