wiki:AdvancedReports

Version 1 (modified by 181129, 21 months ago) ( diff )

--

Напредни извештаи од базата (SQL и складирани процедури)


Промет во последната година


SELECT
  SUM(CASE WHEN TipDok.TipID = 10 THEN -Stavka.Cena_Vkupno
  WHEN TipDok.TipID = 20 THEN Stavka.Cena_Vkupno END) as Promet
FROM
  Stavka
JOIN TipDok ON Stavka.TipID = TipDok.TipID
WHERE
  Datum >= date_trunc('year', current_date) - INTERVAL '1 year'
  AND Datum < date_trunc('year', current_date)

Најтргован продукт во последната година


SELECT Artikal_Ime, SUM(Kolicina)
  AS Vkupno_Trgovan
FROM
  LagerList
JOIN Artikal ON LagerList.Artikal_ID = Artikal.Artikal_ID
JOIN Stavka ON LagerList.StavkaID = Stavka.StavkaID
WHERE
  Datum >= date_trunc('year', current_date) - INTERVAL '1 year'
  AND Datum < date_trunc('year', current_date)
GROUP BY Artikal_Ime
ORDER BY Vkupno_Trgovan DESC
LIMIT 1;

Клиенти подредени по најпрофитабилни во последниот квартал


SELECT 
  Klient.KlientIme,
  SUM(CASE WHEN Stavka.TipID = 10 THEN -Stavka.Cena_Vkupno
  WHEN Stavka.TipID = 20 THEN Stavka.Cena_Vkupno END) AS Vkupen_Promet
FROM
  Klient
JOIN Stavka ON Klient.KlientID = Stavka.KlientID
JOIN TipDok ON Stavka.TipID = TipDok.TipID
WHERE
  Stavka.Datum >= date_trunc('quarter', current_date) - INTERVAL '3 months'
  AND Stavka.Datum < date_trunc('quarter', current_date)
GROUP BY Klient.KlientIme
ORDER BY Vkupen_Promet DESC
Note: See TracWiki for help on using the wiki.