Changes between Version 3 and Version 4 of AdvancedReports

Timestamp:

01/12/23 17:22:50 (2 years ago)

Author:

201194

Comment:

--

AdvancedReports

@@ -1 +1 @@
 = **Напредни извештаи од базата (SQL и складирани процедури)**
 
+=== **Извештаи за клиент според тромесечие, купени производи и потрошена сума**
+{{{#!sql
+SELECT DISTINCT u.korisnicko_ime,
+CASE WHEN tabela.quarter=1 THEN tabela.kupeni_proizvodi ELSE 0 END AS prv_kvartal,
+CASE WHEN tabela.quarter=2 THEN tabela.kupeni_proizvodi ELSE 0 END AS vtor_kvartal,
+CASE WHEN tabela.quarter=3 THEN tabela.kupeni_proizvodi ELSE 0 END AS tret_kvartal,
+CASE WHEN tabela.quarter=4 THEN tabela.kupeni_proizvodi ELSE 0 END AS cetvrt_kvartal,
+
+CASE WHEN tabela.quarter=1 THEN tabela.suma ELSE 0 END AS prv_kvartal_suma,
+CASE WHEN tabela.quarter=2 THEN tabela.suma ELSE 0 END AS vtor_kvartal_suma,
+CASE WHEN tabela.quarter=3 THEN tabela.suma ELSE 0 END AS tret_kvartal_suma,
+CASE WHEN tabela.quarter=4 THEN tabela.suma ELSE 0 END AS cetvrt_kvartal_suma
+
+FROM naracki as n
+ JOIN kupuvaci as k on k.korisnicko_ime=n.korisnicko_ime
+ JOIN korisnici AS u ON k.korisnicko_ime = u.korisnicko_ime
+JOIN parce_e_del_od_naracki as pdm on pdm.id_naracka=n.id_naracka 
+ join parce as p on pdm.id_parce =p.id_parce 
+join proizvodi as pro on p.id_proizvod=pro.id_proizvod
+ JOIN cena AS c ON c.id_proizvod=pro.id_proizvod
+ JOIN (
+
+SELECT DISTINCT c2.korisnicko_ime, extract(quarter FROM n.data_na_naracka) AS quarter, sum(n.suma) AS suma,
+count(n.id_naracka) AS kupeni_proizvodi
+FROM naracki AS n
+JOIN parce_e_del_od_naracki as pdm on pdm.id_naracka=n.id_naracka 
+ join parce as p on pdm.id_parce =p.id_parce 
+ join proizvodi as pro on p.id_proizvod=pro.id_proizvod
+ JOIN cena AS c ON c.id_proizvod=pro.id_proizvod
+JOIN kupuvaci AS c2 ON c2.korisnicko_ime = n.korisnicko_ime
+GROUP BY 1,2
+) AS tabela ON u.korisnicko_ime = tabela.korisnicko_ime
+}}}
 === **Вработен кој извршил достава на најмногу нарачки**
 {{{#!sql
@@ -53 +86 @@
 order by k desc
 }}}
-===
-{{{#!sql
-}}}