Идеи за покомплексни извештаи во системот:

Месечен извештај за вкупна пордажба по артикл

---

SELECT

EXTRACT(YEAR FROM N."Datum") AS "Godina", EXTRACT(MONTH FROM N."Datum") AS "Mesec", A."Naziv" AS "Artikl", SUM(A."Cena" * NA."Kolicina") AS "VkupnoProdazba"

FROM

"Naracka" N INNER JOIN "Naracka_Artikl" NA ON N."NarackaID" = NA."NarackaID" INNER JOIN "Artikl" A ON NA."ArtiklID" = A."ArtiklID"

WHERE

EXTRACT(MONTH FROM N."Datum") = @MesecParam AND EXTRACT(YEAR FROM N."Datum") = @GodinaParam

GROUP BY

EXTRACT(YEAR FROM N."Datum"), EXTRACT(MONTH FROM N."Datum"), A."Naziv"

ORDER BY

EXTRACT(YEAR FROM N."Datum"), EXTRACT(MONTH FROM N."Datum"), A."Naziv";

---

Годишен извештај за вкупна продажба кон клиент

---

SELECT

EXTRACT(YEAR FROM N."Datum") AS "Godina", NF."FirmaID", F."Naziv" AS "Firma", SUM(A."Cena" * NA."Kolicina") AS "VkupnoProdazba"

FROM

"Naracka" N INNER JOIN "Naracka_Artikl" NA ON N."NarackaID" = NA."NarackaID" INNER JOIN "Artikl" A ON NA."ArtiklID" = A."ArtiklID" INNER JOIN "Naracka_Firma" NF ON N."NarackaID" = NF."NarackaID" INNER JOIN "Firma" F ON NF."FirmaID" = F."FirmaID"

WHERE

EXTRACT(YEAR FROM N."Datum") = @GodinaParam

GROUP BY

EXTRACT(YEAR FROM N."Datum"), NF."FirmaID", F."Naziv"

ORDER BY

EXTRACT(YEAR FROM N."Datum"), NF."FirmaID";

---

Годишен извештај за средно време на завршување на нарачка по фирма

---

SELECT

NF."FirmaID", F."Naziv" AS "Firma", AVG(EXTRACT(EPOCH FROM NI."DatumEnd" - NI."DatumStart")) / 86400 AS "SrednoVremeZavrshuvanje"

FROM

"Naracka_IzrabotkaPlan" NI INNER JOIN "Naracka_Firma" NF ON NI."NarackaID" = NF."NarackaID" INNER JOIN "Firma" F ON NF."FirmaID" = F."FirmaID"

WHERE

EXTRACT(YEAR FROM NI."DatumStart") = 2024

GROUP BY

NF."FirmaID", F."Naziv"

ORDER BY

NF."FirmaID";

---