April 30, 2009 at 2:04 pm
Correct. If there are only one or two additional status to be deleted it could become part of the CASE-clause. If there are more I would probably suggest a mapping table.
Greets
Flo
How to Post Data/Code to get the best Help How to Post Performance Problems
April 30, 2009 at 2:07 pm
Here is an updated test suite.
create table #Order (
OrderId int,
DeliveryDate datetime,
Price money,
UnitId int,
StatusCode varchar(32)
);
insert into #Order
select 2765,'2009-02-13 00:00:00.000',49.95, 4,'Completed' union all
select 2765,'2009-02-13 00:00:00.000',9.95, 89,'Completed' union all
select 2765,'2009-02-14 00:00:00.000',49.95, 89,'Completed' union all
select 2766,'2009-02-15 00:00:00.000',19.95, 4,'Completed' union all
select 2766,'2009-02-15 00:00:00.000',29.95, 4,'Not Completed' union all
select 2767,'2009-02-15 00:00:00.000',19.95, 4,'Completed' union all
select 2767,'2009-02-15 00:00:00.000',29.95, 4,'Completed'
;
select * from #Order;
with OrderPurge(
OrderId,
StatusCode,
DeleteDate
) AS (
SELECT
OrderId,
max(
case StatusCode
when 'Completed' then 0
when 'Not Completed' then 1
else 2
end
) as StatusCode,
MAX(CASE WHEN UnitId = 89 THEN DATEADD(mm, 6, DeliveryDate) ELSE DATEADD(mm, 1, DeliveryDate) END)
FROM
#Order
GROUP BY
OrderId
)
delete from #Order
from
#Order o
inner join OrderPurge op
on (o.OrderId = op.OrderId)
where
op.DeleteDate < getdate()
and op.StatusCode = 0
;
select * from #Order;
drop table #Order;
April 30, 2009 at 2:18 pm
Thank you very much all for your time spent on this!
I will get back as soon as i tested.
May 1, 2009 at 10:38 pm
I tested and it worked perfectly fine, thanks again for you time!
May 2, 2009 at 4:20 am
Glad to see that we could help!
Greets
Flo
How to Post Data/Code to get the best Help How to Post Performance Problems
Viewing 5 posts - 16 through 19 (of 19 total)
You must be logged in to reply to this topic. Login to reply