Date difference records

[sarah2855]

I have a table called Customers, This table has CustomerName, Order, and Order Date:

Customer1 Order A 1/1/2006
Order B 1/6/2006
Order C 1/1/2009

Customer2 Order A 1/1/2007
Order B 1/1/2010
Order C 1/6/2010

We are not interested in records that their Order date difference is more than two years. In other words we only keep records that their order happens in two year period. For example: in the above situation, we can keep the Customer1,Order A, 1/1/2006, because in less than two years there was an order. The same for Customer 1, Order B 1/6/2006 because less than 2 years ago there was a order. however, we shouldn't keep Customer1, Order C, 1/1/2009 because in a two year time span(before and after) there was no order. So we delete it from table.

The same logic for Customer 2, which means, first records for Customer 2,Order A,1/1/2007 will be deleted.

I don't know how to implement this, Can anyone help me out? I would really appreciate it.

Thanks,

[patjones]

Hi sarah,

I remember seeing a question similar to this answered recently (within the past several months) but don't remember the thread right now. Let me see if I can find it. And by all means I encourage you to look as well. I'm pretty sure this topic or something like it has been covered here previously.

Pat

[sarah2855]

Hi sarah,

I remember seeing a question similar to this answered recently (within the past several months) but don't remember the thread right now. Let me see if I can find it. And by all means I encourage you to look as well. I'm pretty sure this topic or something like it has been covered here previously.

Pat

Thanks Pat,
I just created an account here. I will definitely look for this topic. If you find it please let me know.

Thanks in advance,
Sarah

[NeoPa]

This SQL should do it for you. I suggest you look at it to understand how it's working for best results.

DELETE *
FROM   [Customers] AS tCo
WHERE  (
    SELECT Count(tCi.[Order Date]) AS ODCount
    FROM   [Customers] AS tCi
    WHERE  (tCi.Customername=tCo.CustomerName)
      AND  (tCi.[Order Date] Between
                DateAdd('yyyy',-2,tCo.[Order Date]) And
                DateAdd('yyyy',2,tCo.[Order Date]))
    )>1

Welcome to Bytes!

[patjones]

This SQL should do it for you. I suggest you look at it to understand how it's working for best results.

DELETE *
FROM   [Customers] AS tCo
WHERE  (
    SELECT Count(tCi.[Order Date]) AS ODCount
    FROM   [Customers] AS tCi
    WHERE  (tCi.Customername=tCo.CustomerName)
      AND  (tCi.[Order Date] Between
                DateAdd('yyyy',-2,tCo.[Order Date]) And
                DateAdd('yyyy',2,tCo.[Order Date]))
    )>1

Welcome to Bytes!

This is a beautiful piece of logic. Should the criteria on the subquery be WHERE ( . . . ) = 0 though? I think sarah2855 is looking to delete records where there is nothing within two years of [Order Date].

[NeoPa]

Thank you Pat.

I understand why you ask the question, but the answer's no. The search will always find the matching record. It is a search for all records within that date range. Not other records within that date range. It will always match at least itself.

[patjones]

Thank you Pat.

I understand why you ask the question, but the answer's no. The search will always find the matching record. It is a search for all records within that date range. Not other records within that date range. It will always match at least itself.

OK, I see that...so the subquery will always generate at least one hit. But if it evaluates to > 1 then doesn't it mean it is finding another date within that range, and so the record with tCo.[Order Date] should not be deleted?

Pat

[NeoPa]

Yes Pat. Spot on. That should read <2 as opposed to >1.

Corrected SQL :

DELETE *
FROM   [Customers] AS tCo
WHERE  (
    SELECT Count(tCi.[Order Date]) AS ODCount
    FROM   [Customers] AS tCi
    WHERE  (tCi.Customername=tCo.CustomerName)
      AND  (tCi.[Order Date] Between
                DateAdd('yyyy',-2,tCo.[Order Date]) And
                DateAdd('yyyy',2,tCo.[Order Date]))
    )<2

[OldBirdman]

I have a question!
Why?
The initial problem is clearly stated, and the answers definitely find an SQL solution.
In the data for Customer1, Order C will be deleted because more than 2 years elapsed between it and Order B. Then Order D cannot be within 2 years of Order B, and will be deleted when it appears. So will all following orders.
If in the future 2 orders are less than 2 years apart, say Order J 1/11/2014 and Order K 1/16/2014, they will be kept if the delete routine is not run between 1/11/2014 and 1/16/2014, but Order J will be deleted if code run on say 1/13/2014, and then Order K will be deleted at a future execution. This seems rather arbitrary to me.

[patjones]

I have a question!
Why?
The initial problem is clearly stated, and the answers definitely find an SQL solution.
In the data for Customer1, Order C will be deleted because more than 2 years elapsed between it and Order B. Then Order D cannot be within 2 years of Order B, and will be deleted when it appears. So will all following orders.
If in the future 2 orders are less than 2 years apart, say Order J 1/11/2014 and Order K 1/16/2014, they will be kept if the delete routine is not run between 1/11/2014 and 1/16/2014, but Order J will be deleted if code run on say 1/13/2014, and then Order K will be deleted at a future execution. This seems rather arbitrary to me.

Maybe this table is not going to be used operationally in the future, or something. I guess we would need the OP to tell us what motivates this process, and then perhaps we can present a different solution. Anyway, NeoPa gave one good answer within the framework of the question asked, so that's a good start.

Pat

[NeoPa]

Thanks Pat. It is important to answer within the framework of the question. You're right.

On the other hand, it can also be positively helpful to raise such queries as OB is doing. Preferably as well as, rather than instead of, an answer, which I see as covered in this instance.