Changes between Version 8 and Version 9 of AdvancedReports

Timestamp:

01/25/23 14:22:15 (2 years ago)

Author:

201081

Comment:

--

AdvancedReports

@@ -89 +89 @@
 }}}
 
-=== **Извештај за сите компании и од која компанија биле купени најмалку производи, попустот, просек на цената на производите, купувачи со број нарачани парчиња од компанијата**
+=== **Извештај за компаниите,број на купени производи од секоја компанија според тромесечие **
 {{{#!sql
-select komp.ime as ime_kompanija, p.ime_proizvod as tip_proizvod , kolicina as dostapen_vo_kolicina , kat.ime as ime_kategorija,
-avg(iznos) as prosek_cena, 
 
-count((pedon.id_parce,pedon.id_naracka)) as broj_naracki,
-count((n.korisnicko_ime,n.id_naracka)) as kupuvac_broj_naracani,
-count((pdvm.id_magacin,pdvm.id_parce)) as dostapno_vo_magacin
-from proizvodi p 
+select k.ime ,
+coalesce(prv.zbir_1,0) as prv_kavrtal, coalesce(vtor.zbir_2,0) as vtor_kvartal,
+coalesce(tret.zbir_3,0) as tret_kvartal, coalesce(cetvrt.zbir_4,0) as cetvrt_kvartal
+from kompanija k 
+join proizvodi p on p.ime = k.ime
+join parce as par on par.id_proizvod =p.id_proizvod 
+join parce_e_del_od_naracki pedon on pedon.id_parce =par.id_parce 
+join naracki as n on n.id_naracka =pedon.id_parce 
+left join (
+select k.ime ,p.ime_proizvod , p.kolicina  as zbir_1
+from kompanija k
+join proizvodi p on p.ime = k.ime
+join parce as par on par.id_proizvod =p.id_proizvod 
+join parce_e_del_od_naracki pedon on pedon.id_parce =par.id_parce 
+join naracki as n on n.id_naracka =pedon.id_parce  and n.data_na_naracka between to_date(concat('01-01-',extract(year from now())),'dd-mm-yyyy') and to_date(concat('31-03-',extract(year from now())),'dd-mm-yyyy') 
+) as prv on  k.ime = prv.ime
+left join (
+select k.ime ,p.ime_proizvod , p.kolicina  as zbir_2
+from kompanija k
+join proizvodi p on p.ime = k.ime
+join parce as par on par.id_proizvod =p.id_proizvod 
+join parce_e_del_od_naracki pedon on pedon.id_parce =par.id_parce 
+join naracki as n on n.id_naracka =pedon.id_parce  and n.data_na_naracka between to_date(concat('01-04-',extract(year from now())),'dd-mm-yyyy') and to_date(concat('30-06-',extract(year from now())),'dd-mm-yyyy') 
+) as vtor on k.ime = vtor.ime
+left join (
+select k.ime ,p.ime_proizvod , p.kolicina  as zbir_3
+from kompanija k
+join proizvodi p on p.ime = k.ime
+join parce as par on par.id_proizvod =p.id_proizvod 
+join parce_e_del_od_naracki pedon on pedon.id_parce =par.id_parce 
+join naracki as n on n.id_naracka =pedon.id_parce  and  n.data_na_naracka between to_date(concat('01-07-',extract(year from now())),'dd-mm-yyyy') and to_date(concat('30-09-',extract(year from now())),'dd-mm-yyyy')
+) as tret on k.ime=tret.ime
+left join (
+select k.ime ,p.ime_proizvod, p.kolicina  as zbir_4
+from kompanija k
+join proizvodi p on p.ime = k.ime
+join parce as par on par.id_proizvod =p.id_proizvod 
+join parce_e_del_od_naracki pedon on pedon.id_parce =par.id_parce 
+join naracki as n on n.id_naracka =pedon.id_parce  and  n.data_na_naracka between to_date(concat('01-10-',extract(year from now())),'dd-mm-yyyy') and to_date(concat('31-12-',extract(year from now())),'dd-mm-yyyy')
+ 
+) as cetvrt on k.ime=cetvrt.ime
+group by k.ime, prv.zbir_1, vtor.zbir_2, tret.zbir_3, cetvrt.zbir_4
 
-left join kompanija komp on p.ime = komp.ime
-left join kategorii kat on p.id_proizvod = kat.id_proizvod
-left join parce par on p.id_proizvod = par.id_proizvod 
-left join parce_e_del_od_naracki pedon on par.id_parce = pedon.id_parce
-left join komentari k on p.id_proizvod=k.id_proizvod 
-left join kupuvaci k2 on k.korisnicko_ime = k2.korisnicko_ime 
-left join naracki n on n.korisnicko_ime = k2.korisnicko_ime 
-left join parce_dostapno_vo_magacini pdvm on par.id_parce = pdvm.id_parce 
-left join magacini m on pdvm.id_magacin=m.id_magacin 
-left join cena c on p.id_proizvod=c.id_proizvod and c.popust > 1
 
-group by 1,2,3,4
-order by kolicina
+
+
+
 }}}