I need select the employee id's from the last delivery unless the status = OTD from the hta table. Then I need to get all remaining employee id's from the employee table and display those results. Unfortunately employees with a status of otd are also in the employee table. Can I write a query that will return these results? Or do I have to do it differently? If so can you guide me.
SELECT h.employee_id
, MAX(h.delivery_date)
, e.employee_first
, h.status
FROM TABLE_HTA h
RIGHT
JOIN TABLE_EMPLOYEES e
ON h.employee_id = e.employee_id
WHERE h.status <> "OTD"
GROUP
BY h.employee_id
ORDER
BY MAX(h.delivery_date) ASC
Table_hta<br>
order_id | employee_id | status | delivery_date |<br>
1 | 23 | OTD | 1/1/15 3:45pm<br>
2 | 24 |DELIVER | 1/1/15 3:50pm<br>
3 | 25 |DELIVER | 1/1/15 3:51pm<br>
<br>
Table_employees<br>
employee_id | employee_first |<br>
23 Bob<br>
24 James<br>
25 Henry<br>
26 Sally<br>
Results<br>
employee_id | employee_first | delivery_date<br>
26 |Sally<br>
24 |James |1/1/15 3:50pm<br>
25 |James |1/1/15 3:51pm<br>
Sally was included because she did not have the status OTD. Bob was excluded because he had the status OTD.
You'll get more and better answers if you include CREATE TABLE and INSERT statements in your question.
create table table_hta (
order_id integer primary key,
employee_id integer not null,
status varchar(10) not null,
delivery_date datetime not null
);
insert into table_hta values
(1, 23, 'OTD', '2015-01-01 3:45'),
(2, 24, 'DELIVER', '2015-01-01 3:50'),
(3, 25, 'DELIVER', '2015-01-01 3:51');
create table table_employees (
employee_id integer primary key,
employee_first varchar(10) not null
);
insert into table_employees values
(23, 'Bob'),
(24, 'James'),
(25, 'Henry'),
(26, 'Sally');
The employees you're interested in are
All the employees are in "table_employees". The exceptions are in "table_hta". This will give you the employees you're interested in.
select employee_id
from table_employees
where employee_id not in (select employee_id
from table_hta
where status = 'OTD');
We can use the WHERE clause just as it is. To get the max delivery dates (a poor name for delivery times, by the way), we do a left join to preserve the employee id numbers we're interested in.
select e.employee_id, e.employee_first, h.delivery_date
from table_employees e
left join (select employee_id, max(delivery_date) as delivery_date
from table_hta group by employee_id) h
on e.employee_id = h.employee_id
where e.employee_id not in (select employee_id
from table_hta
where status = 'OTD');
The fact that you're interested in the maximum delivery date (delivery time) suggests that the table allows multiple delivery dates. If it allows multiple delivery dates, it probably allows data like this, too.
order_id | employee_id | status | delivery_date |
1 | 24 | OTD | 1/1/15 3:45pm
2 | 24 |DELIVER | 1/1/15 3:50pm
Think hard about what you want to include in situations like this.