SQL Delete specific records with condition

Question

I have a table like below:

And the delete condition is:

delete from Employee only if the date is smaller than a specific date and no record is larger than that date

e.g.

• if the date is 3/8/2014, then only record with EmployeeID 3 will be removed as the record with EmployeeID 4 has date larger than 3/8/2014, and EmployeeID 5 won't be removed as the date is 3/9/2014
• if the date is 3/9/2014, then record with EmployeeID 3 and 5 will be removed, as the record with EmployeeID 4 has date larger than 3/9/2014

At first, I tried

delete from Employee where Date > @Date

But the above SQL would delete all records wherever the date is smaller than the @Date

What amendments should be made to the above SQL?

Answer 1

create table #t (EmployeeID int, Date datetime)

insert #t values 
(3, '20140304'),
(3, '20140305'),
(3, '20140306'),
(4, '20140307'),
(4, '20140308'),
(4, '20140310'),
(5, '20140309')

declare @date date = '20140308'

;with x as (
    select t.*
    from #t t
    where Date <= @date and not exists (
    select * from #t t2 where t.EmployeeId = t2.EmployeeID and Date > @date)
)
delete x;

Answer 2

Try this:

DELETE FROM TableName
WHERE EmployeeID IN 
(SELECT EmployeeID FROM TableName
 GROUP BY EmployeeID
 HAVING MAX(DATE)<=@Date)

Tested and verified.

See an example in SQL Fiddle.

Answer 3

Here it is,

Declare @d date ='3/8/2014'


delete from myEmp where empID in
(
select empID from myEmp
group by empID
having MAX(empDate) <=@d
)

Link for Demo, DEMO

Answer 4

Try this:

delete from Employee
where EmployeeID in
(select EmployeeID 
from Employee
group by Employeeid
having max(Date) < @Date)