Order items
1with
2 order_items as (
3 select
4 date as date
5 , orderNumber as order_id
6 , concat(orderNumber, lineNumber) as order_line_id
7 , i.customer_customerNumber as customer_id
8 , orderType as invoice_type
9 , currency
10 , a.lineNumber as line_id
11 , a.description
12 , a.unit
13 , a.quantity
14 , a.unitNetPrice as unit_net_price
15 , a.discountPercentage as discount_percentage
16 , a.unitCostPrice as unit_cost_price
17 , a.totalNetAmount as total_net_amount
18 , a.marginInBaseCurrency as margin_in_base_currency
19 , a.marginPercentage as margin_percentage
20 , a.departmentalDistribution as departmental_distribution
21 from
22 {{raw.economic.orders}} i
23 left join unnest (lines) as a
24 )
25select
26 c.name
27 , i.*
28from
29 order_items i
30 left join {{raw.economic.customers}} c on i.customer_id = c.customerNumber
31order by
32 date desc+---------------------+----------+----------------+-------------+--------------+----------+--------+-------------------+-------+--------+-------------------+---------------------+-------------------+-------------------------+------------------------+--------------------------+-----------------+
| name | date | order_id | customer_id | invoice_type | currency | line_id | description | unit | quantity | unit_net_price | discount_percentage | unit_cost_price | total_net_amount | margin_in_base_currency | margin_percentage | departmental_distribution |
+---------------------+----------+----------------+-------------+--------------+----------+--------+-------------------+-------+--------+-------------------+---------------------+-------------------+-------------------------+------------------------+--------------------------+-----------------+
| Customer Name 1 | 2022-05-01 | 1001 | 1 | Order | USD | 1 | Product 1 | Piece | 10 | 10.00 | 0.05 | 8.00 | 100.00 | 20.00 | 20.00 | Distribution 1 |
| Customer Name 2 | 2022-05-01 | 1002 | 2 | Order | USD | 1 | Product 2 | Piece | 5 | 20.00 | 0.10 | 15.00 | 100.00 | 25.00 | 25.00 | Distribution 2 |
| Customer Name 3 | 2022-04-30 | 1003 | 3 | Order | USD | 1 | Product 3 | Piece | 2 | 50.00 | 0.15 | 40.00 | 100.00 | 10.00 | 10.00 | Distribution 3 |
+---------------------+----------+----------------+-------------+--------------+----------+--------+-------------------+-------+--------+-------------------+---------------------+-------------------+-------------------------+------------------------+--------------------------+-----------------+The SQL template named "Order items" is designed for integration with the economic system. This SQL model allows you to retrieve detailed information about order items, including the date, order ID, order line ID, customer ID, invoice type, currency, line ID, description, unit, quantity, unit net price, discount percentage, unit cost price, total net amount, margin in base currency, margin percentage, and departmental distribution. By executing this SQL template, you can gain insights into the order items data, such as analyzing sales trends, identifying top customers, monitoring margins, and tracking departmental distribution. The SQL code retrieves the relevant data from the "economic" database tables, specifically the "orders" and "customers" tables. The result is sorted in descending order based on the date. This SQL template can be useful for businesses using the economic system to manage their orders and customers. It provides a comprehensive view of order items, allowing for analysis and decision-making based on various parameters.
