Weld logo
economic
Economic

Order items

Retrieves order items from the "economic" integration. It combines data from the "orders" table and the "customers" table, joining them based on the customer ID. The result is sorted in descending order by date.
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
Example of output from model:
+---------------------+----------+----------------+-------------+--------------+----------+--------+-------------------+-------+--------+-------------------+---------------------+-------------------+-------------------------+------------------------+--------------------------+-----------------+
|        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.

Ready to start modeling your own economic data?

Get started building your data warehouse with economic and 100+ more apps and databases available.

economic
Weld logo
Sign up for free
Weld logo

Tired of scattered data? Sync data to your data warehouse with Weld in minutes with our powerful ETL, SQL Transformations, Reverse ETL and AI Assistant, Ed - connected to 100+ apps, files and databases.

Backed by leading investors
Frontline logoCherry logoInnnovation Fund logo
Twitter LogoLinkedIn Logo
GDPR logoSOC2
© 2024 Weld. All rights reserved.