I have a table of employees. The schema is something like below:

Columns: name varchar
         status varchar
         id varchar

The status could be anything from "online", "offline", "away", "busy". This table captures status of all employees at certain time, periodically.

Sample input:

Frank "online"
Toby "away"
Frank "busy"
Alice "offline"
Alex "away"
Toby "online"

I have to create a report as below:

ALex "not available"
Alice "not available"
Frank "busy"
Toby "available"

Basically the condition is that an employee will be shown as "busy", if he/she has been busy at least once, "available", if he/she has been "online" at least once and never "busy", and "not available" if he/she has never been "online" or "busy".

How to write such a query. I am absolutely a noob in SQL. Please help me.

Regards.

CodePudding user response：

I mean you can use next query:

SELECT 
    `name`,
    CASE 
        WHEN `statuses` LIKE '%busy%' THEN 'busy'
        WHEN `statuses` LIKE '%online%' AND `statuses` NOT LIKE '%busy%'   THEN 'available'
        WHEN `statuses` NOT LIKE '%online%' AND `statuses` NOT LIKE '%busy%'   THEN 'not available'
    END `status`
FROM (
    SELECT 
        `name`, 
        GROUP_CONCAT(`status`) `statuses`
    FROM eployees GROUP BY `name`
) emploees_statuses;

Test SQL query here

MSSQL use STRING_AGG instead GROUP_CONCAT;

SELECT 
    name,
    CASE 
        WHEN statuses LIKE '%busy%' THEN 'busy'
        WHEN statuses LIKE '%online%' AND statuses NOT LIKE '%busy%'   THEN 'available'
        WHEN statuses NOT LIKE '%online%' AND statuses NOT LIKE '%busy%'   THEN 'not available'
    END status
FROM (
SELECT 
    name, 
    STRING_AGG(status, ',') statuses
FROM eployees GROUP BY name
) emploees_statuses;

MSSQL STRING_AGG online

CodePudding user response：

I'm not sure I understand your post, can't you just do :

    INSERT INTO ONLINE_EMPLOYEE 
    (SELECT *
    FROM EMPLOYEE
    WHERE STATUS = 'ONLINE');

And then repeat on the other status ?

CodePudding user response：

I would do the following...

1. Translate the statuses to integers

• 0 = 'busy'
• 1 = 'online'
• NULL for the rest (to be otherwise ignored)

2. The the minimum of all those integer states

• If they've ever been 'busy' the minimum would be 0
• Else, if they've ever been 'online' the minimum would be 1
• Else, the minimum would be NULL

3. Recode the integer values in to strings

• 0 = 'busy'
• 1 = 'available'
• Else, 'not available'

For example...

SELECT
  name,
  CASE
    MIN(
      CASE status
        WHEN 'busy'   THEN 0
        WHEN 'online' THEN 1
      END
    )
    WHEN 0 THEN 'busy'
    WHEN 1 THEN 'available'
           ELSE 'not available' END  AS status
FROM
  employees
GROUP BY
  name

This should also work in all SQL dialects.