Changes between Version 3 and Version 4 of AdvancedReports

Timestamp:

12/28/22 12:19:25 (2 years ago)

Author:

201084

Comment:

--

AdvancedReports

@@ -103 +103 @@
 === Извештај за сите делови, со нивни категории и производители, и количината која била нарачана од овие делови во секој од последните 3 месеци. ===
 {{{#!sql
-select p.id_part, p.part_name, cat.category_name, pm.pm_name, f.narachani_vo_ovoj_mesec as narachani_ovoj_mesec, s.narachani_vo_prethoden_mesec as narachani_prethoden_mesec, t.narachani_vo_predprethoden_mesec as narachani_predprethoden_mesec
+select p.id_part, p.part_name, cat.category_name, pm.pm_name, 
+coalesce (f.narachani_vo_ovoj_mesec,0) as narachani_ovoj_mesec, 
+coalesce (s.narachani_vo_prethoden_mesec,0) as narachani_prethoden_mesec,
+coalesce (t.narachani_vo_predprethoden_mesec,0) as narachani_predprethoden_mesec
 from project.part as p
 join project.part_manufacturer as pm on p.id_part_manufacturer = pm.id_part_manufacturer
 join project.part_is_from_category as pifc on p.id_part = pifc.id_part
 join project.category as cat on pifc.id_category = cat.id_category
-left join project.order_contains_part as ocp on p.id_part = ocp.id_part
-left join project.order_table as o on ocp.id_order = o.id_order
+join project.order_contains_part as ocp on p.id_part = ocp.id_part
+join project.order_table as o on ocp.id_order = o.id_order
 left join (
 select p.id_part, p.part_name, cat.category_name, pm.pm_name,
-       count(o.id_order) as narachani_vo_ovoj_mesec
+       sum(ocp.quantity_order) as narachani_vo_ovoj_mesec
 from project.part as p
 join project.part_manufacturer as pm on p.id_part_manufacturer = pm.id_part_manufacturer
 join project.part_is_from_category as pifc on p.id_part = pifc.id_part
 join project.category as cat on pifc.id_category = cat.id_category
-left join project.order_contains_part as ocp on p.id_part = ocp.id_part
-left join project.order_table as o on ocp.id_order = o.id_order
+join project.order_contains_part as ocp on p.id_part = ocp.id_part
+join project.order_table as o on ocp.id_order = o.id_order
                                   and
                               extract(month from o.order_date) = extract(month from now()) and extract(year from o.order_date) = extract(year from now())
@@ -125 +128 @@
 left join (
 select p.id_part, p.part_name, cat.category_name, pm.pm_name,
-       count(o.id_order) as narachani_vo_prethoden_mesec
+       sum(ocp.quantity_order)  as narachani_vo_prethoden_mesec
 from project.part as p
 join project.part_manufacturer as pm on p.id_part_manufacturer = pm.id_part_manufacturer
 join project.part_is_from_category as pifc on p.id_part = pifc.id_part
 join project.category as cat on pifc.id_category = cat.id_category
-left join project.order_contains_part as ocp on p.id_part = ocp.id_part
-left join project.order_table as o on ocp.id_order = o.id_order
+join project.order_contains_part as ocp on p.id_part = ocp.id_part
+join project.order_table as o on ocp.id_order = o.id_order
                                   and
                               extract(month from o.order_date) = extract(month from now()-interval'1 month') and extract(year from o.order_date) = extract(year from now()-interval'1 month')
-group by p.id_part, p.part_name, cat.category_name, pm.pm_name
+group by p.id_part, p.part_name, cat.category_name, pm.pm_name 
 ) as s on p.id_part = s.id_part and pm.pm_name = s.pm_name
 left join (
 select p.id_part, p.part_name, cat.category_name, pm.pm_name,
-       count(o.id_order) as narachani_vo_predprethoden_mesec
+       sum(ocp.quantity_order)  as narachani_vo_predprethoden_mesec
 from project.part as p
 join project.part_manufacturer as pm on p.id_part_manufacturer = pm.id_part_manufacturer
 join project.part_is_from_category as pifc on p.id_part = pifc.id_part
 join project.category as cat on pifc.id_category = cat.id_category
-left join project.order_contains_part as ocp on p.id_part = ocp.id_part
-left join project.order_table as o on ocp.id_order = o.id_order
+join project.order_contains_part as ocp on p.id_part = ocp.id_part
+join project.order_table as o on ocp.id_order = o.id_order
                                   and
                               extract(month from o.order_date) = extract(month from now()-interval'2 month') and extract(year from o.order_date) = extract(year from now()-interval'2 month')
-group by p.id_part, p.part_name, cat.category_name, pm.pm_name
+group by p.id_part, p.part_name, cat.category_name, pm.pm_name 
 ) as t on p.id_part = t.id_part and pm.pm_name = t.pm_name
 group by p.id_part, p.part_name, cat.category_name, pm.pm_name, f.narachani_vo_ovoj_mesec, s.narachani_vo_prethoden_mesec, t.narachani_vo_predprethoden_mesec
 
-
-
 }}}