---
title: "SQL Analytics - Guide to navigate the Kleer database"
description: "A comprehensive guide to navigate the Kleer database"
lastModified: "2026-06-15"
search:
  tags:
    - "sql"
    - "analytics"
    - "guide"
    - "navigate"
    - "the"
    - "kleer"
---

:::note
This guide is a complement to the [SQL Analytics documentation](/teknisk-dokumentation/sql-analytics-documentation)
and the [product sheet](/teknisk-dokumentation/sql-analytics-produktblad). It assumes you can
already connect to the database. This document is not updated regularly — reasonable updates
can be made upon request, but Kleer cannot guarantee that all info is up to date at any given time.
:::

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.

| Table | Description |
| --- | --- |
| `dimension` | A category, e.g. "Region" |
| `dimension_entry` | An entry within a category, e.g. "Stockholm" |
| `month` / `date` | Help-tables used to generate linear date tables to aid SQL logic |

### Available dimension entries in company

```sql
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.

```sql title="Count days in date 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)
```

```sql title="Group by month"
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

| Table | Description |
| --- | --- |
| `event` (Beta) | A time report |
| `activity` (Beta) | An activity on which time is reported, used to differentiate types of work and absence |
| `project` (Beta) | Time is reported on a project; includes customer and project type |
| `project_dimension` (Beta) | Assigned dimension entries on a project |
| `client_project_budget` (Beta) | Assigned budget for project |
| `client_project_budget_activity` (Beta) | Granular activity budgeting for project |
| `expected_event_price` (Beta) | Event price settings for activities and users within a project |
| `time_report_internal_hourly_cost` (Beta) | Internal cost per activity on a user |

### 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.

| Column | Description |
| --- | --- |
| `id` | Unique event identifier |
| `company_id` | Unique company identifier |
| `project_id` | Client project ID |
| `user_id` | User ID |
| `date` | Date of reported time |
| `hours` | Number of hours reported |
| `days` | Number of days reported |
| `activity_id` / `activity_name` | Activity ID and name |
| `certified` | Boolean — whether time has been certified |
| `absence` / `absence_type` | Whether time is absence, and its type (nullable) |
| `invoiceable` | Boolean — whether time is invoiceable |
| `invoiced_hours` / `invoiced_price` | Hours and hourly price actually invoiced, if any |

### 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.

| Column | Description |
| --- | --- |
| `id` | Unique project identifier |
| `client_id` | Client unique identifier, null if internal project |
| `effective_our_reference_user_id` | Our reference on invoice settings of project |
| `label` | Effectively project nr and project name |
| `dimension_entry_id` | Project-specific dimension entry |
| `fixed_price` | Boolean — whether project is fixed price |

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.

```sql title="Project dimensions"
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`.

```sql title="Data for budget estimate - hours"
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
```

```sql title="Data for budget estimate - other costs"
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.

```sql title="Scheduled time"
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.

```sql title="Different types of events"
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 | Actual |
| --- | --- | --- |
| Hours | 8 (`event.hours`) | 10 (`event.invoiced_hours`) |
| Price | 15000 (`expected_event_price`) | — |
| Amount | 1200 SEK | 20000 (`event.invoiced_price`) |

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.

```sql title="Expected price"
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.

```sql title="Internal cost per event"
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)](#combining-accounting-and-time-records)
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.

| Table | Description |
| --- | --- |
| `voucher` | A voucher, also known as an accounting journal or verification |
| `account` | Account, unique for every accounting year |
| `accounting_year` | Accounting year |
| `transaction` | A row on a voucher, "a booking" on a specific account |
| `transaction_reference` | Connects transactions to other entities like invoices, suppliers, clients |
| `transaction_dimension_group` | A group of dimensions on a transaction |
| `transaction_dimension_group_entry` | A dimension entry within a transaction dimension group |

### 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.

```sql title="List voucher and accounting year"
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)
```

| Column | Description |
| --- | --- |
| `voucher_serie` | Signifies how the voucher was created |
| `description` | Auto-generated, e.g. certified client invoices produce "Kundfaktura \{Customer\} (\{Invoice nr\})" |
| `date` | Accounting date |

### 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.

| Column | Description |
| --- | --- |
| `account.description` | What the account is for — standard settings follow "BAS 2011" |
| `account.in_balance` | Ingoing balance for the specific accounting year |
| `accounting_year.start_date` / `end_date` | Start/end of the 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.

```sql title="Extract connection between transactions and invoices"
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.

```sql title="List transaction dimension groups and entries"
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.

```sql title="Nestled select queries to create dimension specific columns"
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.

```sql title="Extract in balance, period and out balance of account"
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](#internal-cost).

```sql title="Result of client project (including 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

| Table | Description |
| --- | --- |
| `client` | The customer — every client invoice has one connected |
| `client_invoice` (v1 and Beta) | The invoice itself |
| `client_invoice_credit` | Links a credit invoice to its debit invoice |
| `client_invoice_field` (Beta) | Custom field values and aliases on client invoices |
| `client_invoice_status` (Beta) | Booleans for factoring, debt collection, reminders |

### Client

```sql title="List customer register"
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.

| Column | Description |
| --- | --- |
| `foreign_id` | Reference to a source system ID, not visible in Kleer |
| `end_client_id` | For broker invoicing ("Can be invoiced via"): shows the end customer, while `client_id` shows the broker |
| `delivery_type` | `Mail`, `Email`, `Manually` (not sent), or `Svefaktura` (EDI) |
| `invoice_country_code` | Country |
| `accounting_country_code` | Territoriality ("Omsättningsland") |
| `printed` / `certified` | Whether sent / certified (booleans) |
| `payment_date` | Date registered as paid |

```sql title="List client invoice ledger"
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
```

```sql title="Status of client invoice"
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
```

```sql title="Payment status of client invoice"
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
```

```sql title="Invoice amount grouped by client and month of invoice date"
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

| Table | Description |
| --- | --- |
| `supplier` | The supplier — every supplier invoice has one connected |
| `supplier_invoice` (v1 and Beta) | The invoice itself |
| `bank_credit_transfer` (Beta) + `_cancelled` / `_completed` / `_failed` / `_pending` | Bank transactions and status connected to the supplier invoice |

```sql title="List supplier register"
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.

| Column | Description |
| --- | --- |
| `amount` / `vat` | Invoice amount / VAT in issued currency |
| `currency_rate` | Rate from issued currency to accounting currency |
| `certified` | Boolean — invoice certified or not |
| `remaining` | Remaining amount to pay |
| `reference_nr` | Invoice number |
| `reg_date` | Date the accounting proposal was submitted for certification |

```sql title="List supplier invoice ledger"
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
```

```sql title="Invoice amount grouped by supplier and month of invoice date"
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

| Table | Description |
| --- | --- |
| `bank_account` (Beta) | Company bank accounts |
| `bank_account_closing_balance` (Beta) | Closing balance on bank account |
| `bank_credit_transfer` (Beta) + status tables | Bank transactions connected to supplier invoices |

```sql title="Closing balance on bank account"
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

| Table | Description |
| --- | --- |
| `accrual_pending` | Pending accruals by type and month |

```sql title="Pending accruals"
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](/teknisk-dokumentation/sql-analytics-documentation)
- [SQL Analytics - Produktblad](/teknisk-dokumentation/sql-analytics-produktblad)
- [SQL - Hur man uppdaterar en SQL-fråga i Excel ODBC](/teknisk-dokumentation/sql-hur-man-uppdaterar-en-sql-fraga-i-excel-odbc)