From 44d58471f29e2edb842565e93ebebd0d38da7f1c Mon Sep 17 00:00:00 2001 From: Maxime Bourget Date: Tue, 4 Jun 2013 17:08:45 +0100 Subject: [PATCH] Add create order summary view file, (missing) --- sql/create_order_summary_view.sql | 19 +++++++++++++++++++ 1 file changed, 19 insertions(+) create mode 100644 sql/create_order_summary_view.sql diff --git a/sql/create_order_summary_view.sql b/sql/create_order_summary_view.sql new file mode 100644 index 0000000..96d66d5 --- /dev/null +++ b/sql/create_order_summary_view.sql @@ -0,0 +1,19 @@ +CREATE VIEW 0_order_summary_view AS +SELECT debtor_no, debtor_ref, branch_ref, stk_code +, min(delivery_date) delivery_date +, sum(sod.quantity - qty_sent) as quantity +, sum((sod.quantity - qty_sent)*unit_price*(1-discount_percent/100)) as amount +, min(required_date) required_date +, group_concat(distinct comments separator ';') as order_comment +, group_concat(distinct comment separator ';') as detail_comment +, max(quantity_before) as quantity_before +FROM 0_sales_order_details sod +JOIN 0_sales_orders so ON (so.order_no = sod.order_no + AND so.trans_type = sod.trans_type + AND so.trans_type = 30 +) +NATURAL JOIN 0_debtors_master +NATURAL JOIN 0_cust_branch +JOIN 0_denorm_order_details_queue d ON (d.id = sod.id) +WHERE sod.quantity > qty_sent +GROUP BY debtor_no, branch_code, stk_code -- 2.30.2