CODEWITHSUNDEEP
sql
Vlad
Vlad

Reputation: 73

Get last status

I have table:

enter image description here

I want to get the latest status for today, provided that if there are no vacations or business trips on the current date, the "Job" status should be displayed. I use this query, but it's incorrect (some times it shows two values)

SELECT Name,Status,Start,End
  FROM employee as emp
  Where Start = (Select max(Start) from employee  as emp2 where emp.Name = emp2.Name )
 Order by Name

Current result

Name     Status        Start     End
People-1 Job           2/3/2020  0001-01-01 00:00:00.000
People-1 business trip 4/20/2022 4/22/2022

Expected result:

Name     Status        Start    End
People-1 Job           2/3/2020 0001-01-01 00:00:00.000

Upvotes: 3

Views: 161

Answers (1)

robodisco
robodisco

Reputation: 4272

It's a bit hard to fully understand your desired behaviour but assuming the following is true this sql should work:

  1. There are multiple 'employee' records in the table
  2. You want all records for all employees which match the following
    • the start date must be the most recent date in the data set (this is not the same as saying the start date must equal today's date)
    • the status must not be vacation or business trip.
create temporary table employees (
  start date,
  name varchar,
  status varchar
);

insert into employees (start, name, status) values
('2022-05-03', 'Adam', 'vacation'),
('2022-05-03', 'Bill', 'business_trip'),
('2022-05-05', 'Adam', 'business_trip'),
('2022-05-05', 'Bill', 'Job'),
('2022-05-05', 'Wendy', 'Job');

select * from employees
where start = ( select max(start) from employees) AND
( status != 'vacation' AND status != 'business_trip' );

   start    | name  | status
------------+-------+--------
 2022-05-05 | Bill  | Job
 2022-05-05 | Wendy | Job

Its very close to what you posted though so I'm not quite sure why you are getting multiple results for the data set you shared.I'd go back and double check your data set and confirm the sql query you shared is in fact the one you used to produce the output.

Hope that helps or at least provides some inspiration to fix your problem.

Upvotes: 2

Related Questions