0

I have a table like below:

enter image description here

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?

Improve this question
1
  • record 4 has a date less then 3/8/2014 i.e 3/7/2014 it will also be deleted. Commented Apr 4, 2014 at 4:42

4 Answers 4

Reset to default
3

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

7 Comments

Remove the max(date) column from the Select in the in clause
@SoulTrain: Removed. Thanks, mate. Now tested and verified.
mate you should have <= instead of only <. It makes difference for case 2 that user has given.
@AK47 the users conditions 1 and 2 contradict with each other.
@damien, how? can you please explain?
|
2

Try this:

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

Comments

2

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

Improve this answer

Comments

2 
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;
Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.