Help on purging old records

  • 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

  • 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;

  • Thank you very much all for your time spent on this!

    I will get back as soon as i tested.

  • I tested and it worked perfectly fine, thanks again for you time!

  • Glad to see that we could help!

    Greets

    Flo

Viewing 5 posts - 16 through 19 (of 19 total)

You must be logged in to reply to this topic. Login to reply