# Set the "status" using a formula or SQL

**URL:** <https://forum.seatable.com/t/set-the-status-using-a-formula-or-sql/2434>\
**Category:** Formulas\
**Tags:** formula, sql-query\
**Created:** [May 26, 2023, 6:23am UTC](https://forum.seatable.com/t/set-the-status-using-a-formula-or-sql/2434 "2023-05-26T06:23:07Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![eash](https://forum.seatable.com/user_avatar/forum.seatable.com/eash/32/4222_2.png) [@eash](https://forum.seatable.com/u/eash)\
**Post date:** [May 26, 2023, 6:23am UTC](https://forum.seatable.com/t/set-the-status-using-a-formula-or-sql/2434/1 "2023-05-26T06:23:07Z")

</div>

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.

 ![image](https://forum.seatable.io/uploads/default/original/2X/7/7c0cc6c6f89bacd13934ec82d32794225185085f.png)

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.

 ![image](https://forum.seatable.io/uploads/default/original/2X/8/8cac07b4339037793a65d484c6aa979474076488.png)

---

<div class="post-metadata">

**Author:** ![eash](https://forum.seatable.com/user_avatar/forum.seatable.com/eash/32/4222_2.png) [@eash](https://forum.seatable.com/u/eash)\
**Post date:** [May 30, 2023, 10:42am UTC](https://forum.seatable.com/t/set-the-status-using-a-formula-or-sql/2434/2 "2023-05-30T10:42:52Z")

</div>

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

```auto
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

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

```

 ![image](https://forum.seatable.io/uploads/default/original/2X/a/a63705507e37794cde69547e355b3eeee0422ab7.png)

As a result, there is a status for each employee today.  
 ![image](https://forum.seatable.io/uploads/default/original/2X/4/41cb9746dd0498d40734da5ec52c4f72037ea770.png)

---

<div class="post-metadata">

**Author:** ![system](https://forum.seatable.com/uploads/default/original/1X/04603423fe81a46c321283663086c475429e8ccb.png) [@system](https://forum.seatable.com/u/system)\
**Post date:** [June 1, 2023, 10:43am UTC](https://forum.seatable.com/t/set-the-status-using-a-formula-or-sql/2434/3 "2023-06-01T10:43:30Z")

</div>

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