Invoice line items
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+-------------------+----------------+------------+-------------+--------------+---------+----------+---------+-------------------+---------------------+-------------------+-------------------+------------------+---------------------+-------------------+-------------------+-------------------+-------------------+-------------------+
| 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.
