Changes between Version 16 and Version 17 of Напредни извештаи од базата

Timestamp:

02/13/22 14:11:35 (3 years ago)

Author:

186102

Comment:

--

Напредни извештаи од базата

@@ +1 @@
+== Вкупен приџод по продавница во изминатите 3 месеци==
+{{{#!sql
+select sum(t1.Anhoch+t2.Anhoch) as Anhoch, sum(t1.Setec + t2.Setec) as Setec, sum(t1.Neptun+t2.Neptun) as Neptun
+from
+(
+select
+sum(case when pr.store_name = 'Anhoch' then cp.quantity * pr.price else 0 end) as Anhoch,
+sum(case when pr.store_name = 'Setec' then cp.quantity * pr.price else 0 end) as Setec,
+sum(case when pr.store_name = 'Neptun' then cp.quantity * pr.price else 0 end) as Neptun
+from project.order as o
+join project.checkout as ch on o.order_id = ch.order_id
+join project.cart as cr on cr.cart_id = ch.cart_id
+join project.contains_product as cp on cp.cart_id = cr.cart_id
+join project.product as pr on pr.prod_id = cp.prod_id
+join project.store as str on pr.store_name = str.store_name
+join project.delivery as dlv on dlv.delivery_id = o.delivery_id
+where now() - interval '3 months' <= dlv."date"
+) as t1,
+(
+select
+sum(case when pr.store_name = 'Anhoch' then ccp.num_products_config * pr.price else 0 end) as Anhoch,
+sum(case when pr.store_name = 'Setec' then ccp.num_products_config * pr.price else 0 end) as Setec,
+sum(case when pr.store_name = 'Neptun' then ccp.num_products_config * pr.price else 0 end) as Neptun
+from project.order as o
+join project.orders_configuration as oc on o.order_id = oc.order_id
+join project."Configuration" as cfg on oc.config_id = cfg.config_id
+join project.config_contains_product as ccp on cfg.config_id = ccp.config_id
+join project.product as pr on pr.prod_id = ccp.prod_id
+join project.store as str on pr.store_name = str.store_name
+join project.delivery as dlv on dlv.delivery_id = o.delivery_id
+where now() - interval '3 months' <= dlv."date"
+) as t2
+}}}
+
 == Просечна цена на продуктот во месец од изминатите 12 месеци ==
 {{{#!sql