SQL Analytics - Guide to navigate the Kleer database

A comprehensive guide to navigate the Kleer database

This page is in English, matching the source material — no Swedish translation exists yet.

Overview

Kleer’s database consists of two databases:

• report_external_v1
• report_external_beta

Beta exists to allow for more rapid development of new features that are generally implemented in beta first. Kleer tries to never release changes that are not backwards compatible, but in beta Kleer reserves the possibility to make changes in the future.

SQL-Analytics is company specific, meaning that only data from the company connected to your username and token is accessible. Company ID can be retrieved from every table to ease merging of different data sources.

All SQL in this document is written using report_external_v1 as the primary database.

Scope — groups and modules

The database can be divided into some of the same modules found in Kleer. This guide is grouped similarly:

• Common
• Time records
• Transactions (accounting)
• Client invoices
• Supplier invoices
• Bank
• Other

Each group starts with a table listing related database tables, followed by a table of all SQL examples within that group.

Common

Tables that have no direct connection to a specific module, or that are used across many modules.

Available dimension entries in company

SELECT
    d.name 'Dimension',
    de.name 'Dimension Entry'
FROM
    dimension d
    JOIN
    dimension_entry de ON de.dimension_id = d.id

Dimensions (categories)

Dimensions are (if enabled) a way of categorizing the bookkeeping in order to create a strategy for following up results. Common examples are “Region”, “Business Unit” or “Cost center”. A dimension entry is a specific entry within a dimension (a sub-dimension) — if the dimension is “Business Unit”, a typical entry could be “Sales”, “Consulting”, “Development” etc. Both dimensions and dimension entries are unique to each company and can be added and managed freely.

Transactions can be “tagged” by dimension entries interchangeably. A transaction can be tagged with zero, one, or many entries, and every entry can be distributed by a percentage, as long as the percentage does not exceed 100% within the same dimension.

Depending on company settings, some dimensions are standard — examples are “Projekt” (when time records are enabled) and “Användare” (when enabled in company settings).

Example: a revenue transaction of 100 SEK can be tagged like this:

• Dimension: Region, Entry: “Stockholm”, Distribution: 100%
• Dimension: Business Unit, Entry: “Sales”, Distribution: 50%
• Dimension: Business Unit, Entry: “Consulting”, Distribution: 50%

Effectively, “Stockholm” is tagged with a revenue of 100 SEK, “Sales” with 50 SEK, and “Consulting” with 50 SEK.

Month and day

These tables show static time-entries you can use to extract data in a linear date fashion — useful when you want to sum or display data from a predefined time grouping, e.g. calculate revenue per month, or number of invoices sent per month. They also help when gaps might exist in the date-dimension of a primary table.

SELECT
    EXTRACT(YEAR_MONTH FROM d.date) 'Month',
    COUNT(DISTINCT d.date) 'Number of days in month'
FROM
    date d
WHERE
    EXTRACT(YEAR FROM d.date) = '2020'
GROUP BY EXTRACT(YEAR_MONTH FROM d.date)

SELECT
    m.year_month_val 'Month',
    m.start_date 'Start of month',
    m.end_date 'End of month'
FROM
    month m
WHERE
    m.year = '2020'

Time records

Event

Conceptually an event is a “time report”: a specific user reporting a specific amount of time (hours), on a specific date, on a specific activity, (sometimes) on a specific project.

Activities and projects

Activities can be either reported time or reported absence, and can be invoiceable or not depending on the activity’s settings.

When the time records module is enabled, time can be reported on a project. Projects can be internal (no client) or external (connected to a client), and ongoing or (if enabled) fixed price — this governs how revenue is booked and time invoiced.

Projects can also have dimension entries assigned to them, found in project_dimension — not to be confused with a project’s own unique dimension entry under the standard “Projekt” dimension.

SELECT
    p.label 'Project',
    pde.name 'Project specific dimension',
    pd.dimension_name 'Assigned dimension',
    pd.dimension_entry_name 'Assigned dimension entry'
FROM
    report_external_beta.project p
    JOIN
    report_external_beta.project_dimension pd ON pd.project_id = p.id
    JOIN
    dimension_entry pde ON pde.id = p.dimension_entry_id

Project budget

Projects can have budgeted values, available in client_project_budget and client_project_budget_activity.

SELECT
    p.label 'Project',
    c.name 'Client name',
    a.name 'Activity',
    u.name 'User',
    pba.hours 'Hours',
    pba.price / 100 'Price',
    pba.internal_cost_accounting / 100 'Internal cost',
    pba.start_date 'Period start',
    pba.start_date 'Period end'
FROM
    report_external_beta.project p
    JOIN
    client c ON c.id = p.client_id
    JOIN
    report_external_beta.client_project_budget pb ON pb.client_project_id = p.id
    JOIN
    report_external_beta.client_project_budget_activity pba ON
        pba.client_project_budget_id = pb.id
    JOIN
    report_external_beta.activity a ON a.id = pba.activity_id
    JOIN
    user u ON u.id = pba.user_id

SELECT
    p.label 'Project',
    c.name 'Client name',
    pb.cost_amount / 100 'Total of other costs',
    pb.cost_description 'Description of other costs',
    pb.reinvoiced_cost_amount / 100 'Of which onward invoiced costs'
FROM
    report_external_beta.project p
    JOIN
    client c ON c.id = p.client_id
    JOIN
    report_external_beta.client_project_budget pb ON pb.client_project_id = p.id

Scheduled time

Actual scheduled time can be found in payroll_user_scheduled_time, which shows the amount of scheduled time per user, per day.

SELECT
    m.year_month_val 'Month',
    u.name 'User',
    IFNULL((SELECT
                SUM(st.scheduled_actual_hours)
            FROM
                report_external_beta.payroll_user_scheduled_time st
            WHERE
                st.user_id = u.id
                AND EXTRACT(YEAR_MONTH FROM st.date) = m.year_month_val),
            0) 'Scheduled time'
FROM
    month m
    JOIN
    user u
WHERE
    m.year = '2020'

Extract different types of events

Events are created when users report time. Because there are many types of events, nested select queries are useful for calculating aggregated sums of specific event types.

SELECT
    EXTRACT(YEAR_MONTH FROM e.date) AS 'Month',
    u.name AS 'User',
    SUM(e.hours) AS 'Reported time',
    IFNULL((SELECT
                SUM(st.scheduled_actual_hours)
            FROM
                report_external_beta.payroll_user_scheduled_time st
            WHERE
                st.user_id = e.user_id
                AND EXTRACT(YEAR_MONTH FROM st.date) = EXTRACT(YEAR_MONTH FROM e.date)),
            0) AS 'Scheduled time',
    IFNULL((SELECT
                SUM(hours)
            FROM
                report_external_beta.event e1
            WHERE
                e1.invoiceable = 1
                AND EXTRACT(YEAR_MONTH FROM e1.date) = EXTRACT(YEAR_MONTH FROM e.date)
                AND e1.user_id = e.user_id
                AND e1.certified = 1),
            0) AS 'Invoiceable time',
    IFNULL((SELECT
                SUM(hours)
            FROM
                report_external_beta.event e1
            WHERE
                EXTRACT(YEAR_MONTH FROM e1.date) = EXTRACT(YEAR_MONTH FROM e.date)
                AND e1.user_id = e.user_id
                AND e1.absence = 1),
            0) AS 'Absence',
    IFNULL((SELECT
                Round(SUM(e1.invoiced_hours*e1.invoiced_price/100),2)
            FROM
                report_external_beta.event e1
            WHERE
                e1.invoiceable = 1
                AND EXTRACT(YEAR_MONTH FROM e1.date) = EXTRACT(YEAR_MONTH FROM e.date)
                AND e1.user_id = e.user_id
                AND e1.certified = 1),
            0) AS 'Invoiceable amount'
FROM
    report_external_beta.event e
    JOIN
    user u ON u.id = e.user_id
WHERE
    EXTRACT(YEAR_MONTH FROM e.date) = '201910'
GROUP BY user_id , EXTRACT(YEAR_MONTH FROM e.date)
ORDER BY EXTRACT(YEAR_MONTH FROM e.date)

Expected event price vs invoiced amount

The general workflow for creating client invoices from time records is:

Time is reported → Time is certified → Basis for invoice is edited → Invoice is created → Invoice is certified

When editing the basis for invoice, time and hourly price are finalized and can overwrite project settings — so the actual invoiced amount and time don’t necessarily match what was expected from project pricing settings.

Example: Project 1, Activity 1 (150 SEK/h). A user reports 8 hours on Activity 1. Basis for invoice is edited to 10 hours at 200 SEK/h.

Expected price can be set at three levels — Activity, Project activity, or Project activity member (most granular). Regardless of strategy, expected_event_price (joined by user, activity, and project) always displays the price actually visible in the project.

SELECT
    p.label 'Project',
    u.name 'Member',
    e.date 'Date',
    e.activity_name 'Aktivitet',
    e.hours 'Reported hours',
    e.invoiced_hours 'Invoiced hours',
    e.invoiced_price / 100 'Invoiced price',
    e.invoiced_hours * e.invoiced_price 'Invoiced amount',
    ep.price / 100 'Expected invoice price',
    ep.price / 100 * e.hours 'Expected invoiced amount'
FROM
    report_external_beta.event e
    JOIN
    user u ON e.user_id = u.id
    JOIN
    report_external_beta.project p ON p.id = e.project_id
    JOIN
    client c ON c.id = p.client_id
    JOIN
    report_external_beta.expected_event_price ep ON ep.user_id = u.id
        AND ep.client_project_id = p.id
        AND ep.activity_id = e.activity_id
WHERE
    e.date >= '2019-01-01'
    AND e.date <= NOW()
ORDER BY e.date

Internal cost

Users can be assigned internal cost per hour in time report settings, either per activity or the same across all activities.

SELECT
    e.date 'Date',
    u.name 'User',
    e.hours 'Hours',
    e.activity_name 'Activity',
    IFNULL((SELECT
                ic.amount
            FROM
                report_external_beta.time_report_internal_hourly_cost ic
            WHERE
                ic.user_id = u.id
                AND ic.start_date <= e.date
                AND ic.activity_id = e.activity_id
                OR ic.activity_id IS NULL
                AND ic.user_id = u.id
                AND ic.start_date <= e.date
            order by ic.start_date desc
            limit 1),
            0) AS 'Internal cost/h of event'
FROM
    report_external_beta.event e
    JOIN
    user u ON u.id = e.user_id

See also Result of client project (including internal cost) for more uses of internal cost.

Transactions (accounting)

Transactions and vouchers are central to the Kleer database. The structure can look complex at first glance but makes more sense once you understand the basic model.

Vouchers

A voucher has a number and a series. The series signifies how the voucher was created:

• A — manual vouchers created by an accountant
• B — from client invoices being certified
• C — special payments of invoices (not commonly used)
• D — from supplier invoices being certified
• F — generally relates to accruals
• T — booking of in/out bank account transactions
• N — “revenues” (own income ledger integrations)
• K — expenses
• L — payrolls

Vouchers are commonly referred to as series + number, e.g. “B2” — in SQL a concat of voucher_serie and nr. Because voucher numbers reset each accounting year, this abbreviation isn’t unique on its own; accounting year (start + end date) is usually included too.

SELECT
    CONCAT(ay.start_date, ' - ', ay.end_date) 'Accounting year',
    CONCAT(v.voucher_serie, v.nr) 'Voucher',
    v.description 'Voucher description'
FROM
    voucher v
    JOIN
    accounting_year ay ON ay.id = v.accounting_year_id
ORDER BY CONCAT(ay.start_date, ' - ', ay.end_date) , CONCAT(v.voucher_serie, v.nr)

Account and accounting year

The account table displays both result and balance accounts. Accounts may have ingoing values from the previous accounting year, and are unique per accounting year.

Transactions

A transaction is a row on a voucher — “a booking on an account”. Amounts are always noted in accounting currency (transaction.accounting_amount), and since all monetary amounts are in “cents”, divide by 100 to get decimal form.

Example: accounting currency SEK, accounting_amount = 150000 → 1 500 SEK.

Debit bookings have positive amounts, credit bookings have negative amounts.

Transaction reference

Transactions often originate from a specific Kleer module and entity. transaction_reference connects the accounting to whatever entity generated it — e.g. a revenue transaction to a client or an invoice.

SELECT
    CONCAT(v.voucher_serie, v.nr) 'Voucher',
    v.date 'Date',
    v.description 'Description',
    t.account_nr 'Account',
    ROUND(t.accounting_amount / 100, 2) 'Accounting amount',
    IFNULL(ci.nr, '') 'Client invoice nr',
    IFNULL(c.name, '') 'Client name',
    IFNULL(si.reference_nr, '') 'Supplier invoice nr',
    IFNULL(s.name, '') 'Supplier'
FROM
    voucher v
    JOIN
    transaction t ON t.voucher_id = v.id
    JOIN
    transaction_reference tr ON tr.transaction_id = t.id
    LEFT JOIN
    client_invoice ci ON ci.id = tr.client_invoice_id
    LEFT JOIN
    client c ON c.id = tr.client_id
    LEFT JOIN
    supplier_invoice si ON si.id = tr.supplier_invoice_id
    LEFT JOIN
    supplier s ON s.id = tr.supplier_id

Transaction dimension group

Currently only result accounts can be “tagged” with dimension entries. When an entity (like a client invoice row) is assigned one or many dimensions, its transactions get a connected transaction dimension group.

The group has its own accounting amount, used to extract the amount tagged on each dimension entry within it. Groups are generated to account for dimension distribution — when a percentage of a dimension entry is tagged on the transaction, a group is created for every combination of possible distributions, so extraction stays simple.

A transaction dimension group can have one or many transaction_dimension_group_entry rows — one per dimension entry in the group. Because entries are unique, take care with grouping when summing: a left join across group and group entry gives non-tagged transactions null values, and every entry becomes its own row.

SELECT
    CONCAT(v.voucher_serie, v.nr) 'Voucher',
    v.description 'Voucher description',
    v.date 'Date',
    t.account_nr 'Account',
    t.accounting_amount / 100 'Transaction amount',
    tdg.accounting_amount / 100 'Transaction group amount',
    d.name 'Dimenson',
    de.name 'Dimension entry'
FROM
    voucher v
    JOIN
    transaction t ON t.voucher_id = v.id
    LEFT JOIN
    transaction_dimension_group tdg ON tdg.transaction_id = t.id
    LEFT JOIN
    transaction_dimension_group_entry tdge ON
        tdge.transaction_dimension_group_id = tdg.id
    LEFT JOIN
    dimension_entry de ON de.id = tdge.dimension_entry_id
    LEFT JOIN
    dimension d ON d.id = de.dimension_id

Extract amounts based on dimensions and dimension entries

Example 1 — even distribution. A transaction of 1000 SEK is tagged Cost center C1 (100%) and Region South (100%). This creates one dimension group of 1000 SEK, since there’s only one way to combine 100% with 100%.

Example 2 — uneven distribution. A transaction of 1000 SEK is tagged Cost center C1 (50%). Two dimension groups of 500 SEK each are created (1000 * 50%), which can be summed since they share the same dimension — the sum of all groups always equals the full transaction amount.

Example 3 — combination of distributions. A transaction of 1000 SEK is tagged Cost center C1 (50%), Region North (20%), Region South (80%). Four groups are created (400, 100, 400, 100 SEK). To find how much is tagged with Region South, sum all groups containing that dimension entry: 400 + 400 = 800 SEK.

Example 4 — filtering to avoid duplication. If you join transaction_dimension_group with transaction_dimension_group_entry without filtering, amounts get duplicated per entry — a 1000 SEK transaction tagged with two dimensions would sum to 2000 SEK, which is wrong. A grouping or filter is required; only 1000 SEK is really booked on the transaction.

SELECT
    CONCAT(v.voucher_serie, v.nr) 'Voucher',
    v.description 'Voucher description',
    v.date 'Date',
    t.account_nr 'Account',
    t.accounting_amount / 100 'Transaction amount',
    tdg.accounting_amount / 100 'Transaction group amount',
    IFNULL((SELECT
                de1.name
            FROM
                transaction t1
                JOIN
                transaction_dimension_group tdg1 ON tdg1.transaction_id = t1.id
                JOIN
                transaction_dimension_group_entry tdge1 ON
                    tdge1.transaction_dimension_group_id = tdg1.id
                JOIN
                dimension_entry de1 ON de1.id = tdge1.dimension_entry_id
                LEFT JOIN
                dimension d1 ON d1.id = de1.dimension_id
            WHERE
                tdg1.id = tdg.id
                AND d1.name = 'Användare'),
            '') AS 'Användare',
    IFNULL((SELECT
                de1.name
            FROM
                transaction t1
                JOIN
                transaction_dimension_group tdg1 ON tdg1.transaction_id = t1.id
                JOIN
                transaction_dimension_group_entry tdge1 ON
                    tdge1.transaction_dimension_group_id = tdg1.id
                JOIN
                dimension_entry de1 ON de1.id = tdge1.dimension_entry_id
                LEFT JOIN
                dimension d1 ON d1.id = de1.dimension_id
            WHERE
                tdg1.id = tdg.id
                AND d1.name = 'Affärsområde'),
            '') AS 'Affärsområde',
    IFNULL((SELECT
                de1.name
            FROM
                transaction t1
                JOIN
                transaction_dimension_group tdg1 ON tdg1.transaction_id = t1.id
                JOIN
                transaction_dimension_group_entry tdge1 ON
                    tdge1.transaction_dimension_group_id = tdg1.id
                JOIN
                dimension_entry de1 ON de1.id = tdge1.dimension_entry_id
                LEFT JOIN
                dimension d1 ON d1.id = de1.dimension_id
            WHERE
                tdg1.id = tdg.id AND d1.name = 'Projekt'),
            '') AS 'Projekt'
FROM
    voucher v
    JOIN
    transaction t ON t.voucher_id = v.id
    LEFT JOIN
    transaction_dimension_group tdg ON tdg.transaction_id = t.id
    LEFT JOIN
    transaction_dimension_group_entry tdge ON tdge.transaction_dimension_group_id = tdg.id
    JOIN
    account a ON a.id = t.account_id
GROUP BY t.id, tdg.id

This groups the query on transaction, one row per transaction on an account, and lays out amounts per predefined dimension as separate columns — a good default shape for exporting readable accounting data.

Balance accounts and exports

Outgoing balance for a given date on a balance account equals ingoing balance + transactions in period.

Example: account 1510 has an in-balance of 10 500 (2020). Sum of transactions in January 2020 is 5 000. Outgoing balance in January = 15 500.

SELECT
    x.accountingYear 'Accounting year',
    x.month 'Month',
    x.account 'Account',
    x.accountType 'Account type',
    x.accountDescription 'Description',
    FORMAT(x.ingoing, 2, 'sv_SE') 'IB',
    FORMAT(x.period, 2, 'sv_SE') 'Period',
    FORMAT(x.ingoing + x.period, 2, 'sv_SE') 'UB'
FROM
    (SELECT
        CONCAT(ay.start_date, ' - ', ay.end_date) AS accountingYear,
        m.year_month_val AS month,
        IF(left(a.nr,1) BETWEEN 3 AND 8, 'Result', 'Balance') AS accountType,
        a.nr AS account,
        a.description AS accountDescription,
        IFNULL((SELECT
                    a1.in_balance / 100
                FROM
                    account a1
                WHERE
                    a1.accounting_year_id = ay.id
                    AND a1.nr = a.nr), 0) AS ingoing,
        IFNULL((SELECT
                    IF(left(t.account_nr,1) BETWEEN 3 AND 8, SUM(- t.accounting_amount
                        / 100), SUM(t.accounting_amount / 100))
                FROM
                    transaction t
                WHERE
                    t.date >= ay.start_date
                    AND t.date <= m.end_date
                    AND t.account_nr = a.nr), 0) AS period
    FROM
        month m
        JOIN accounting_year ay
        JOIN account a ON a.accounting_year_id = ay.id
    WHERE
        NOW() BETWEEN ay.start_date AND ay.end_date
        AND m.year_month_val = EXTRACT(YEAR_MONTH FROM NOW())) AS x
WHERE
x.period != 0

Combining accounting and time records

Sometimes you need to combine modules — e.g. looking at the result of a client project, which is essentially revenue and costs on the project’s dimension entry, plus internal costs for events (activities) reported on that project.

Example: a project with one activity and one user, at 200 SEK/h internal cost.

• Project income: 125 000 SEK
• Project cost: 25 000 SEK
• Hours reported: 160 h
• Internal cost: 32 000 SEK (160 * 200)
• Project result: 68 000 SEK (125 000 − 25 000 − 32 000)

Internal cost per user is found in time_report_internal_hourly_cost (Beta) — see also Internal cost.

SELECT
    temp.month 'Month',
    temp.project 'Project',
    temp.revenue 'Revenue',
    temp.cost 'Cost',
    (SELECT
        m.year_month_val AS month,
        p.label AS project,
        IFNULL((SELECT
                    ROUND(SUM(- tdg.accounting_amount / 100), 2)
                FROM
                    transaction t
                    JOIN transaction_dimension_group tdg ON tdg.transaction_id = t.id
                    JOIN transaction_dimension_group_entry tdge ON
                        tdge.transaction_dimension_group_id = tdg.id
                    JOIN dimension_entry de1 ON de1.id = tdge.dimension_entry_id
                WHERE
                    de1.id = de.id
                    AND EXTRACT(YEAR_MONTH FROM t.date) = m.year_month_val
                    AND LEFT(t.account_nr, 1) = 3), 0) AS revenue,
        IFNULL((SELECT
                    ROUND(SUM(- tdg.accounting_amount / 100), 2)
                FROM
                    transaction t
                    JOIN transaction_dimension_group tdg ON tdg.transaction_id = t.id
                    JOIN transaction_dimension_group_entry tdge ON
                        tdge.transaction_dimension_group_id = tdg.id
                    JOIN dimension_entry de1 ON de1.id = tdge.dimension_entry_id
                WHERE
                    de1.id = de.id
                    AND EXTRACT(YEAR_MONTH FROM t.date) = m.year_month_val
                    AND LEFT(t.account_nr, 1) BETWEEN 4 AND 8), 0) AS cost,
        IFNULL((SELECT
                    - SUM(temp.hours * temp.internalCostperHour)
                FROM
                    (SELECT
                        e.date AS date,
                        u.name AS user,
                        e.hours AS hours,
                        e.activity_name AS activity,
                        e.project_id AS project,
                        IFNULL((SELECT
                                    ROUND(ic.amount / 100, 2)
                                FROM
                                    report_external_beta.time_report_internal_hourly_cost ic
                                WHERE
                                    ic.user_id = u.id
                                    AND ic.start_date <= e.date
                                    AND ic.activity_id = e.activity_id
                                    OR ic.activity_id IS NULL
                                    AND ic.user_id = u.id
                                    AND ic.start_date <= e.date
                                ORDER BY ic.start_date DESC
                                LIMIT 1), 0) AS internalCostperHour
                    FROM
                        report_external_beta.event e
                        JOIN user u ON u.id = e.user_id) temp
                WHERE
                    temp.project = p.id
                    AND EXTRACT(YEAR_MONTH FROM temp.date) = m.year_month_val), 0) AS internalCost
    FROM
        month m
        JOIN project p
        JOIN dimension_entry de ON de.id = p.dimension_entry_id
        JOIN dimension d ON d.id = de.dimension_id
    WHERE
        m.year = EXTRACT(YEAR FROM NOW())
        AND m.year_month_val <= EXTRACT(YEAR_MONTH FROM NOW())
        AND d.name = 'Projekt'
    ORDER BY m.year_month_val) temp

Client invoices

Client

SELECT
    c.id 'Client ID',
    c.foreign_id 'Customer number',
    c.name 'Cusomer',
    c.orgno 'Reg. no.',
    c.address1 'Address (line 1)',
    c.address1 'Address (line 2)',
    c.zip_code 'Postcode',
    c.state 'Postal locality',
    c.country_code 'Country',
    IF(c.active = 1, 'True', 'False') 'Active customer'
FROM
    client c

foreign_id is a free-text reference to a source system ID — shown as “Customer number” in Kleer.

Client invoice

Available both in v1 and Beta (Beta has more columns). Amounts, VAT and totals are noted in both issued currency (invoice_amount) and accounting currency (accounting_invoice_amount).

Example: accounting currency SEK, invoice issued 100 USD, rate 11 → accounting amount 1100.

SELECT
    ci.nr 'Invoice number',
    c.name 'Client',
    ci.delivery_type 'Delivery type',
    ci.your_reference 'Your reference',
    ci.our_reference 'Our reference',
    ci.invoice_date 'Invoice date',
    ci.due_date 'Due date',
    ci.currency_type 'Currency',
    ci.currency_rate 'Exchange rate',
    ROUND(ci.invoice_amount / 100, 2) 'Invoice amount (excl. tax)',
    ROUND(ci.invoice_total_amount / 100, 2) 'Invoice amount (incl. tax)',
    ROUND(ci.accounting_total_amount / 100, 2) 'Accounting amount (incl. tax)',
    ROUND(ci.accounting_remaining_amount / 100, 2) 'Accounting remaining amount',
    IF(ci.printed = 1, 'True', 'False') 'Sent',
    IF(ci.certified = 1, 'True', 'False') 'Certified',
    ci.payment_date 'Payment date',
    ci.reg_date 'Created date'
FROM
    report_external_beta.client_invoice ci
    JOIN
    client c ON c.id = ci.client_id

SELECT
    c.name 'Client',
    ci.nr 'Invoice nr',
    IF(ci.certified = 1, 'True', 'False') 'Certified',
    IF(ci.printed = 1, 'True', 'False') 'Sent',
    IF(ci.payment_date IS NULL, 'False', 'True') 'Registered payment',
    IF(s.factoring = 1, 'True', 'False') 'Factoring',
    IF(s.debt_collection = 1, 'True', 'False') 'Debt collection',
    IF(s.reminder = 1, 'True', 'False') 'Reminder'
FROM
    report_external_beta.client_invoice ci
    JOIN
    report_external_beta.client_invoice_status s ON s.client_invoice_id = ci.id
    JOIN
    client c ON c.id = ci.client_id

SELECT
    c.name 'Client',
    ci.nr 'Invoice nr',
    ROUND(ci.accounting_total_amount / 100, 2) 'Accounting amount',
    ROUND(ci.accounting_remaining_amount / 100, 2) 'Remaining amount',
    ci.due_date 'Due date',
    IFNULL((ci.payment_date), '') 'Payment date',
    IF(ci.accounting_remaining_amount = 0, 'Paid',
        IF(ci.accounting_remaining_amount < 0, 'Overpaid', 'Not paid')) 'Status',
    IF(ci.payment_date IS NULL, '',
        DATEDIFF(ci.payment_date, ci.due_date)) 'Date difference',
    IF(NOW() > ci.due_date, 'True', 'False') 'Current date passed due date',
    IF(ci.payment_date > ci.due_date, 'True', 'False') 'Payment date passed due date'
FROM
    report_external_beta.client_invoice ci
    JOIN
    client c ON c.id = ci.client_id
WHERE
    ci.certified = 1
ORDER BY ci.due_date DESC

SELECT
    EXTRACT(YEAR_MONTH FROM ci.invoice_date) 'Month of invoice date',
    c.name 'Client',
    ROUND(SUM(ci.accounting_amount / 100), 2) AS 'Accounting amount'
FROM
    client c
    JOIN
    report_external_beta.client_invoice ci ON ci.client_id = c.id
WHERE
    ci.certified = 1
GROUP BY EXTRACT(YEAR_MONTH FROM ci.invoice_date) , c.id
ORDER BY EXTRACT(YEAR_MONTH FROM ci.invoice_date) ,
    ROUND(SUM(ci.accounting_amount / 100), 2) DESC

Supplier invoices

SELECT
    s.id 'Supplier ID',
    s.foreign_id 'External reference',
    s.name 'Supplier',
    s.address1 'Address (line 1)',
    s.zip_code 'Postal locality',
    s.country_code 'Country',
    IF (s.active =1, 'True', 'False') 'Activate supplier'
FROM
    supplier s

Supplier invoice amounts are noted in issued currency — a currency_rate calculation is needed to get accounting currency.

SELECT
    s.name 'Supplier',
    si.invoice_date 'Invoice date',
    ROUND(si.amount / 100, 2) 'Invoice amount (issued currency)',
    si.currency_type 'Currency',
    si.currency_rate 'Currency rate',
    ROUND(si.amount * si.currency_rate / 100, 2) 'Invoice amount (accounting currency)',
    IF(si.certified = 1, 'True', 'False') 'Certified',
    ROUND(si.remaining / 100, 2) 'Remaining (issued currency)',
    si.reference_nr 'Invoice nr',
    si.reg_date 'Creation date'
FROM
    supplier_invoice si
    JOIN
    supplier s ON s.id = si.supplier_id

SELECT
    EXTRACT(YEAR_MONTH FROM si.invoice_date) 'Month of invoice date',
    s.name 'Supplier',
    ROUND(SUM(si.amount * si.currency_rate / 100), 2) AS 'Accounting amount'
FROM
    supplier s
    JOIN
    report_external_beta.supplier_invoice si ON si.supplier_id = s.id
WHERE
    si.certified = 1
GROUP BY EXTRACT(YEAR_MONTH FROM si.invoice_date) , s.id
ORDER BY EXTRACT(YEAR_MONTH FROM si.invoice_date) , ROUND(SUM(si.amount / 100), 2) DESC

Bank

SELECT
    a.id 'Account ID',
    a.bankgiro_nr 'Bankgiro nr',
    a.currency_type 'Account currency',
    a.accounting_account_nr 'Accounting ledger',
    a.country_code 'Account country',
    cb.date 'Date',
    ROUND(cb.amount / 100, 2) 'Closing balance'
FROM
    report_external_beta.bank_account a
    JOIN
    report_external_beta.bank_account_closing_balance cb ON cb.bank_account_id = a.id

Other

SELECT
    ap.year 'Year',
    ap.month 'Month',
    ROUND(ap.revenue_accounting_amount / 100, 2) 'Revenues (from create revenue integration)',
    ROUND(ap.client_invoice_accounting_amount / 100, 2) 'Client invoices',
    ROUND(ap.expense_accounting_amount / 100, 2) 'Expenses',
    ROUND(ap.supplier_invoice_accounting_amount / 100, 2) 'Supplier invoices'
FROM
    accrual_pending ap

Relaterade guider

• SQL Analytics - Documentation
• SQL Analytics - Produktblad
• SQL - Hur man uppdaterar en SQL-fråga i Excel ODBC