Changes between Initial Version and Version 1 of AdvancedReports

Timestamp:

02/20/24 17:10:21 (3 months ago)

Author:

201156

Comment:

--

AdvancedReports

@@ +1 @@
+Идеи за покомплексни извештаи во системот:
+
+**''Месечен извештај за вкупна пордажба по артикл''**
+
+----
+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";
+
+----
+
+