asked on

TSQL delete

HI, I have a table with the below structure.. now i want
to delete all those id's where the stock_codes are similar
within last two months from the min(date).. for instance the below id shall
be deleted as it has the same stock_code within 2 months..

ID Date             stock_id

4942129      2007-12-26 00:00:00.000      ANIKOCM205117830
4942129      2008-01-27 00:00:00.000      ANIKOCM205117830

can anybody please give me that sql code please ?

ASKER CERTIFIED SOLUTION

Guy Hengel [angelIII / a3]

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Guy Hengel [angelIII / a3]

of course, double-check + backup before trying this in prod...

momi_sabag

with a as (
select stock_id, min(date) as mdate
from myTable
group by stock_id)
, b as (
select id
from myTable t1 join a t2 on t1.stock_id = t2.stock_id
where datediff(m, t2.mdate, t1.date) <= 2
group by id
having count(*) > 1
)
delete t1
from myTable t1 join b on t1.id = b.id

gvamsimba

ASKER

Hi angel111,where is table i , i cannot find it in ur code ?

gvamsimba

ASKER

delete o
FROM mytable o
inner join mytable i
on o.id = i.id
and o.stock_code = i.stock_code
and o.[date] > i.[date]
and o.[date] < dateadd(month, 2, i.[date])