Changes between Version 2 and Version 3 of AdvancedReports

Timestamp:

12/27/22 22:39:48 (3 years ago)

Author:

201084

Comment:

--

AdvancedReports

@@ -100 +100 @@
                 )
 }}}
+
+=== Извештај за сите делови, со нивни категории и производители, и количината која била нарачана од овие делови во секој од последните 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
+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
+left join (
+select p.id_part, p.part_name, cat.category_name, pm.pm_name,
+       count(o.id_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
+                                  and
+                              extract(month from o.order_date) = extract(month from now()) and extract(year from o.order_date) = extract(year from now())
+group by p.id_part, p.part_name, cat.category_name, pm.pm_name
+) as f on p.id_part = f.id_part and pm.pm_name = f.pm_name
+left join (
+select p.id_part, p.part_name, cat.category_name, pm.pm_name,
+       count(o.id_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
+                                  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
+) 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
+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
+                                  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
+) 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
+
+
+
+}}}