Weld logo
economic logo
Economic

Invoice line items

Retrieves invoice line items from the economic integration. It combines data from the "booked_invoices" table and the "products" and "customers" tables to provide information such as the customer name, invoice details, product details, and order by date in descending order.
1with
2    invoice_items as (
3        select
4            date as date
5          , concat(invoiceNumber, lineNumber) as invoice_line_id
6          , invoiceNumber as invoice_id
7          , i.customer_customerNumber as customer_id
8          , invoiceType as invoice_type
9          , currency
10          , a.quantity as quantity
11          , a.lineNumber as line_id
12          , a.unitNetPrice as unit_net_price
13          , a.discountPercentage as discount_percentage
14          , a.unitCostPrice as unit_cost_price
15          , a.vatRate as vat_rate
16          , a.vatamount as vat_amount
17          , a.totalNetAmount as net_amount_excl_tax
18          , (a.totalNetAmount + a.vatamount) as net_amount_incl_tax
19          , json_value(a.product, '$.productNumber') as product_id
20        from
21            {{raw.economic.booked_invoices}} i
22            left join unnest (lines) as a
23    )
24select
25    c.name
26  , i.*
27from
28    invoice_items i
29    left join {{raw.economic.products}} p on i.product_id = p.productNumber
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       | invoice_id | customer_id | invoice_type | currency | quantity | line_id | unit_net_price    | discount_percentage | unit_cost_price   | vat_rate          | vat_amount       | net_amount_excl_tax | net_amount_incl_tax | product_id        | product_name      | customer_name     |
+-------------------+----------------+------------+-------------+--------------+---------+----------+---------+-------------------+---------------------+-------------------+-------------------+------------------+---------------------+-------------------+-------------------+-------------------+-------------------+
| John Doe          | 2022-01-31     | 1001       | 12345       | Sale         | USD     | 2        | 1       | 10.00             | 0.05                | 8.00              | 0.20              | 4.00             | 20.00               | 24.00             | 123456            | Product A         | John's Company    |
| Jane Smith        | 2022-01-30     | 1002       | 54321       | Sale         | USD     | 1        | 1       | 15.00             | 0.10                | 12.00             | 0.20              | 3.00             | 15.00               | 18.00             | 654321            | Product B         | Jane's Business   |
| Mark Johnson      | 2022-01-29     | 1003       | 98765       | Sale         | USD     | 3        | 1       | 8.00              | 0.00                | 6.50              | 0.20              | 2.60             | 24.00               | 26.60             | 987654            | Product C         | Mark's Store      |
+-------------------+----------------+------------+-------------+--------------+---------+----------+---------+-------------------+---------------------+-------------------+-------------------+------------------+---------------------+-------------------+-------------------+-------------------+-------------------+

The SQL template "Invoice line items" is designed to retrieve detailed information about invoice line items from the Economic integration. This SQL model allows you to extract data such as the date, invoice line ID, invoice ID, customer ID, invoice type, currency, quantity, line ID, unit net price, discount percentage, unit cost price, VAT rate, VAT amount, net amount excluding tax, net amount including tax, product ID, customer name, and more. By executing this SQL template, you can gain insights into the line items of invoices, including the products purchased, customer details, pricing information, and invoice specifics. This can be useful for various purposes, such as analyzing sales trends, calculating revenue, identifying popular products, understanding customer preferences, and generating financial reports. The SQL code retrieves the necessary data from the "booked_invoices" table and joins it with the "products" and "customers" tables from the Economic integration. The resulting output is sorted in descending order based on the date of the invoice.

Ready to start modeling your own economic data?

Get started building your data warehouse with economic and connect all your apps and databases.

economic logo
Weld logo
Sign up for free
© 2024 Weld. All rights reserved.