SQL Server 2022

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE your_table (
  date_time DATETIME,
  job_name NVARCHAR(255),
  status NVARCHAR(255)
);
INSERT INTO your_table (date_time, job_name, status)
VALUES 
  ('2020-01-01 07:30:30', 'job_1', 'success'),
  ('2020-01-01 15:30:30', 'job_1', 'disabled'),
  ('2020-01-01 18:30:30', 'job_1', 'failed'),
  ('2020-01-01 08:30:30', 'job_2', 'success'),
  ('2020-01-01 18:30:30', 'job_2', 'disabled'),
  ('2020-01-02 15:30:30', 'job_1', 'success'),
  ('2020-01-02 08:30:30', 'job_2', 'success'),
  ('2020-01-02 18:30:30', 'job_2', 'success');
8 rows affected

SELECT 
  CONVERT(DATE, date_time) AS date,
  job_name,
  CASE
    WHEN MAX(CASE WHEN status = 'failed' THEN 2 WHEN status = 'disabled' THEN 1 ELSE 0 END) = 2 THEN 'failed'
    WHEN MAX(CASE WHEN status = 'disabled' THEN 1 ELSE 0 END) = 1 THEN 'disabled'
    ELSE 'success'
  END AS status
FROM your_table
GROUP BY CONVERT(DATE, date_time), job_name;


date
job_name
status


2020-01-01
job_1
failed

2020-01-02
job_1
success

2020-01-01
job_2
disabled

2020-01-02
job_2
success

date	job_name	status
2020-01-01	job_1	failed
2020-01-02	job_1	success
2020-01-01	job_2	disabled
2020-01-02	job_2	success