By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
444,246 Members | 1,480 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 444,246 IT Pros & Developers. It's quick & easy.

Date difference records

P: 21
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,
May 24 '10 #1
Share this Question
Share on Google+
10 Replies


patjones
Expert 100+
P: 931
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
May 24 '10 #2

P: 21
@zepphead80
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
May 25 '10 #3

NeoPa
Expert Mod 15k+
P: 31,625
This SQL should do it for you. I suggest you look at it to understand how it's working for best results.
Expand|Select|Wrap|Line Numbers
  1. DELETE *
  2. FROM   [Customers] AS tCo
  3. WHERE  (
  4.     SELECT Count(tCi.[Order Date]) AS ODCount
  5.     FROM   [Customers] AS tCi
  6.     WHERE  (tCi.Customername=tCo.CustomerName)
  7.       AND  (tCi.[Order Date] Between
  8.                 DateAdd('yyyy',-2,tCo.[Order Date]) And
  9.                 DateAdd('yyyy',2,tCo.[Order Date]))
  10.     )>1
Welcome to Bytes!
May 25 '10 #4

patjones
Expert 100+
P: 931
@NeoPa
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].
May 25 '10 #5

NeoPa
Expert Mod 15k+
P: 31,625
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.
May 25 '10 #6

patjones
Expert 100+
P: 931
@NeoPa
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
May 25 '10 #7

NeoPa
Expert Mod 15k+
P: 31,625
Yes Pat. Spot on. That should read <2 as opposed to >1.

Corrected SQL :
Expand|Select|Wrap|Line Numbers
  1. DELETE *
  2. FROM   [Customers] AS tCo
  3. WHERE  (
  4.     SELECT Count(tCi.[Order Date]) AS ODCount
  5.     FROM   [Customers] AS tCi
  6.     WHERE  (tCi.Customername=tCo.CustomerName)
  7.       AND  (tCi.[Order Date] Between
  8.                 DateAdd('yyyy',-2,tCo.[Order Date]) And
  9.                 DateAdd('yyyy',2,tCo.[Order Date]))
  10.     )<2
May 25 '10 #8

100+
P: 675
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.
May 25 '10 #9

patjones
Expert 100+
P: 931
@OldBirdman
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
May 25 '10 #10

NeoPa
Expert Mod 15k+
P: 31,625
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.
May 26 '10 #11

Post your reply

Sign in to post your reply or Sign up for a free account.