April 29, 2009 at 1:59 pm
Hi all,
I need some suggestion here please.
I have this example below
where i need to delete orders from the table after max delivery date for completed orders.
If the orderid has UnitId = 89 then keep this order for 6 month and then delete after 6 month, and if orderid doesn't have UnitId = 89 keep this order for 1 month and then delete.
How can i do this?
Thanks
ORDERIDDeliveryDatePriceUnitIdStatus
27652009-02-13 00:00:00.00049.954Completed
27652009-02-13 00:00:00.0009.9589Completed
27652009-02-14 00:00:00.00049.9589Completed
27662009-02-15 00:00:00.00019.954Completed
27662009-02-15 00:00:00.00029.954Completed
April 29, 2009 at 3:09 pm
I hope the following code will help you:
/*
ORDERID DeliveryDate Price UnitId Status
2765 2009-02-13 00:00:00.000 49.95 4 Completed
2765 2009-02-13 00:00:00.000 9.95 89 Completed
2765 2009-02-14 00:00:00.000 49.95 89 Completed
2766 2009-02-15 00:00:00.000 19.95 4 Completed
2766 2009-02-15 00:00:00.000 29.95 4 Completed
*/
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,'Completed'
;
select
*
from
#Order
where
DeliveryDate < case when UnitId = 89 then dateadd(mm,-6,dateadd(dd,datediff(dd,0,getdate()),0))
else dateadd(mm,-1,dateadd(dd,datediff(dd,0,getdate()),0))
end;
delete from #Order
where
DeliveryDate < case when UnitId = 89 then dateadd(mm,-6,dateadd(dd,datediff(dd,0,getdate()),0))
else dateadd(mm,-1,dateadd(dd,datediff(dd,0,getdate()),0))
end
and StatusCode = 'Completed';
select
*
from
#Order
drop table #Order;
April 29, 2009 at 3:12 pm
Please look at this article on how to post data to get the best help. The more effort you put into forming a question, the more people will be willing to help.
http://qa.sqlservercentral.com/articles/Best+Practices/61537/[/url]
the code below should be a good start for what you are looking for if i understand correctly.
delete from {tablename}
where deliverydate < (select dateadd(mm,-6,DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE()))))
and unitid = 89
delete from {tablename}
where deliverydate < (select dateadd(mm,-1,DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE()))))
and unitid 89
April 29, 2009 at 4:48 pm
Thank you so much, one more question:
How can i completly eliminate from delete orderid = 2765 if it has UnitID = 89 and UnitID = 4 in this case for another 6 month and then purge after 6 month?
April 29, 2009 at 6:56 pm
trans54 (4/29/2009)
Thank you so much, one more question:How can i completly eliminate from delete orderid = 2765 if it has UnitID = 89 and UnitID = 4 in this case for another 6 month and then purge after 6 month?
Let me answer that with a question.
Based on this data:
2765 2009-02-13 00:00:00.000 49.95 4 Completed
2765 2009-02-13 00:00:00.000 9.95 89 Completed
2765 2009-02-14 00:00:00.000 49.95 89 Completed
If you run the my DELETE query today, the record with UnitID = 4 will be deleted. What will happen when you run the same code (with no changes) on August 14th and again on August 15th?
April 29, 2009 at 9:09 pm
I understand, but this is not what i was asking.
Thanks anyway for your help!
April 29, 2009 at 9:26 pm
trans54 (4/29/2009)
I understand, but this is not what i was asking.Thanks anyway for your help!
If that isn't what you were asking, how about clarifying the question? You asked how to delete specific completed items based on a specific requirement of the number of months a record is to be retained. UnitID = 89 was to be kept 6 months while all others were to be deleted after 1 month.
Your question asked how you would delete all of the records for a specific order. The only other answer I can arrive at without clarification is that those orders with a mix of UnitID's (4 and 89) in your sample data, will be completely deleted after 6 months.
What answer were you expecting?
April 30, 2009 at 7:51 am
Sorry, maybe i did not explained correctly or did not provide enough information.
If any record in the orderid group has unit 89 then completly skip this orderid from purging and only purge this entire set of records after 6 month from the maximum date of delivery, in this case 2009-02-14 00:00:00.000.
In this case all 3 records in orderid 2765 should be deleted after 6 month.
But If the orderid group doesn't have any unit 89 this entire orderid set can go after 1 month.
2765 2009-02-13 00:00:00.000 49.95 4 Completed
2765 2009-02-13 00:00:00.000 9.95 89 Completed
2765 2009-02-14 00:00:00.000 49.95 89 Completed
April 30, 2009 at 10:27 am
trans54 (4/30/2009)
Sorry, maybe i did not explained correctly or did not provide enough information.If any record in the orderid group has unit 89 then completly skip this orderid from purging and only purge this entire set of records after 6 month from the maximum date of delivery, in this case 2009-02-14 00:00:00.000.
In this case all 3 records in orderid 2765 should be deleted after 6 month.
But If the orderid group doesn't have any unit 89 this entire orderid set can go after 1 month.
2765 2009-02-13 00:00:00.000 49.95 4 Completed
2765 2009-02-13 00:00:00.000 9.95 89 Completed
2765 2009-02-14 00:00:00.000 49.95 89 Completed
That changes the requirements. The next question follows, do you only want to delete complete groups? If an order for 2 items has different delivery dates do you want to base the delete date on the latest delivery date?
April 30, 2009 at 10:35 am
That changes the requirements. The next question follows, do you only want to delete complete groups? If an order for 2 items has different delivery dates do you want to base the delete date on the latest delivery date?
Lynn, this is exactly what i want.
April 30, 2009 at 1:31 pm
Okay, took a bit of thinking, and it is possible someone else may have a better idea but this works with your test data. You need to test further in your own test environment and with much more data.
/*
ORDERID DeliveryDate Price UnitId Status
2765 2009-02-13 00:00:00.000 49.95 4 Completed
2765 2009-02-13 00:00:00.000 9.95 89 Completed
2765 2009-02-14 00:00:00.000 49.95 89 Completed
2766 2009-02-15 00:00:00.000 19.95 4 Completed
2766 2009-02-15 00:00:00.000 29.95 4 Completed
*/
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,'Completed'
;
select
*
from
#Order
;
with OrderPurge as (
select
OrderId,
max(DeliveryDate) PurgeDate
from
(
select
OrderId,
max(dateadd(mm,6,DeliveryDate)) DeliveryDate
from
#Order
where
UnitId = 89
group by
OrderId
union all
select
OrderId,
max(dateadd(mm,1,DeliveryDate))
from
#Order
where
UnitId 89
group by
OrderId
) dt
group by
OrderId
)
delete from #Order
from
#Order o
inner join OrderPurge op
on (o.OrderId = op.OrderId)
where
op.PurgeDate < getdate()
and o.StatusCode = 'Completed'
;
select
*
from
#Order
;
drop table #Order;
April 30, 2009 at 1:42 pm
Hi
I hope I got you. Try this;
DECLARE @t TABLE (Id INT NOT NULL IDENTITY, OrderId INT, DeliveryDate DATETIME, Price Money, UnitId INT, Status VARCHAR(100))
INSERT INTO @t
SELECT '2765', '2009-02-13T00:00:00.000', '49.95', '4', 'Completed'
UNION ALL SELECT '2765', '2009-02-13T00:00:00.000', '9.95', '89', 'Completed'
UNION ALL SELECT '2765', '2009-02-14T00:00:00.000', '49.95', '89', 'Completed'
UNION ALL SELECT '2766', '2009-02-15T00:00:00.000', '19.95', '4', 'Completed'
UNION ALL SELECT '2766', '2009-02-15T00:00:00.000', '29.95', '4', 'Completed'
; WITH
cte (OrderId, DeleteDate) AS
(
SELECT
OrderId,
MAX(CASE WHEN UnitId = 89 THEN DATEADD(MONTH, 6, DeliveryDate) ELSE DATEADD(MONTH, 1, DeliveryDate) END)
FROM @t
WHERE Status = 'Completed'
GROUP BY OrderId
)
DELETE t
FROM @t t
JOIN cte ON t.OrderId = cte.OrderId
WHERE cte.DeleteDate < GETDATE()
SELECT * FROM @t
Greets
Flo
How to Post Data/Code to get the best Help How to Post Performance Problems
April 30, 2009 at 1:45 pm
Apologize twice...
1st) I didn't see Lynn's answer
2nd) I forgot the "WHERE Status = 'Completed'" in my statement. Just corrected.
Greets
Flo
How to Post Data/Code to get the best Help How to Post Performance Problems
April 30, 2009 at 1:55 pm
Florian Reischl (4/30/2009)
Apologize twice...1st) I didn't see Lynn's answer
2nd) I forgot the "WHERE Status = 'Completed'" in my statement. Just corrected.
Greets
Flo
Flo,
No apologies needed. Your solution is the one I was trying to find and couldn't see because of the forest. For whatever reason, I just didn't see the case statement inside the max function in the CTE.
Good job.
April 30, 2009 at 1:59 pm
HOWEVER!
What other status codes are possible, and is it possible that one order may have multiple statuses? I'm sure you only want to delete orders where all the items have been completed. The code we have provided so far won't work if there are multiple statuses.
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply