Changes between Version 6 and Version 7 of AdvancedReports

Timestamp:

12/28/22 14:57:01 (3 years ago)

Author:

201084

Comment:

--

AdvancedReports

@@ -169 +169 @@
 group by p.id_part, p.part_name, cat.category_name, pm.pm_name
 }}}
+
+=== Извештај за производителите на авто делови, колку делови од тој производител се продаваат за секоја четвртина од годината ===
+{{{#!sql
+select pm.id_part_manufacturer ,pm.pm_name , 
+coalesce(fq.count_first_quarter,0) as first_quarter, coalesce(sq.count_second_quarter,0) as second_quarter,
+coalesce(tq.count_third_quarter,0) as third_quarter, coalesce(frq.count_fourth_quarter,0) as fourth_quarter
+from project.part_manufacturer pm 
+join project.part p on p.id_part_manufacturer = pm.id_part_manufacturer 
+join project.order_contains_part ocp on ocp.id_part = p.id_part 
+join project.order_table ot on ot.id_order = ocp.id_order
+left join (
+select pm.id_part_manufacturer ,pm.pm_name , count(ocp.id_part) as count_first_quarter
+from project.part_manufacturer pm 
+join project.part p on p.id_part_manufacturer = pm.id_part_manufacturer 
+join project.order_contains_part ocp on ocp.id_part = p.id_part 
+join project.order_table ot on ot.id_order = ocp.id_order and ot.order_date 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') 
+group by pm.id_part_manufacturer 
+) as fq on pm.id_part_manufacturer = fq.id_part_manufacturer and pm.pm_name = fq.pm_name
+left join (
+select pm.id_part_manufacturer ,pm.pm_name , count(ocp.id_part) as count_second_quarter
+from project.part_manufacturer pm 
+join project.part p on p.id_part_manufacturer = pm.id_part_manufacturer 
+join project.order_contains_part ocp on ocp.id_part = p.id_part 
+join project.order_table ot on ot.id_order = ocp.id_order and ot.order_date 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') 
+group by pm.id_part_manufacturer 
+) as sq on pm.id_part_manufacturer = sq.id_part_manufacturer and pm.pm_name = sq.pm_name
+left join (
+select pm.id_part_manufacturer ,pm.pm_name , count(ocp.id_part) as count_third_quarter
+from project.part_manufacturer pm 
+join project.part p on p.id_part_manufacturer = pm.id_part_manufacturer 
+join project.order_contains_part ocp on ocp.id_part = p.id_part 
+join project.order_table ot on ot.id_order = ocp.id_order and ot.order_date 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') 
+group by pm.id_part_manufacturer 
+) as tq on pm.id_part_manufacturer = tq.id_part_manufacturer and pm.pm_name = tq.pm_name
+left join (
+select pm.id_part_manufacturer ,pm.pm_name , count(ocp.id_part) as count_fourth_quarter
+from project.part_manufacturer pm 
+join project.part p on p.id_part_manufacturer = pm.id_part_manufacturer 
+join project.order_contains_part ocp on ocp.id_part = p.id_part 
+join project.order_table ot on ot.id_order = ocp.id_order and ot.order_date 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') 
+group by pm.id_part_manufacturer 
+) as frq on pm.id_part_manufacturer = frq.id_part_manufacturer and pm.pm_name = frq.pm_name
+group by pm.id_part_manufacturer, fq.count_first_quarter, sq.count_second_quarter, tq.count_third_quarter, frq.count_fourth_quarter
+
+}}}