Set the "status" using a formula or SQL

Good afternoon, the table has a column “statuses of periods” of absence. One of the statuses can be “Regular vacation”, “Sick leave”, “Business trip”, etc.

Is it possible to make it so that if one of the statuses was ‘Regular vacation’, ‘Business trip’, ‘Sick leave’, then it would fit into the column “Current status”. And if all statuses are “Working”, then set the value “Working” in the “Current status” column? Is it possible to do this using an SQL plugin? Tell me the approximate form of an SQL query for this.

          • Explanation - - - - -
            This status is calculated using the formula - if “today” is between the “start date” of the period and the “end date” of the period, then write “The name of the period”, if not, the status of the period = “Working”. Therefore, one person has several periods with the status “Working”. And in the statistics, I want to display how many people are currently working, and how many are on vacation - on sick leave or on a business trip.

I solved the task by removing the “On work” status in the current table. I left only empty lines and absence statuses.

IF(
    AND({mark} <> 'delete period', {mark} <> 'delete a period 2'), #do not include these periods in the formula
    IF(
        AND({Start period} <= TODAY(), {End period} >= TODAY()), 
        {Period type}, 
        ''
    ),
    ''
)

In the linked table “Employees” added the formula

if({Period status}, {Period status}, 'On work')

As a result, there is a status for each employee today.
image

This topic was automatically closed 2 days after the last reply. New replies are no longer allowed.