Hi all,
I have a db that contains has the following information. ID is the primary key, EventDate is the date on which the event occurred, Name1 and Name2 are the names of the participants in the event and the CountName column is the desired outcome.
ID EventDate Name1 Name2 CountName AA1 01/01/2000 AAA BBB 0 AA2 31/02/2000 CCC DDD 0 AA3 10/03/2001 EEE AAA 1 AA4 15/11/2001 GGG HHH 0 AA5 20/05/2002 AAA III 2 AA6 29/08/2003 JJJ KKK 0 AA7 25/10/2003 BBB LLL 1 AA8 10/12/2004 AAA MMM 3 AA9 20/05/2006 AAA NNN 2
The database contains data about events, let's say form 01/01/2000 until 31/12/2006. I need to count the number of times a name appears in Name1 and Name2 in a five year time period prior to the focal event date. I guess its best to illustrate with an example. Let's look at AAA. On the 01/01/2000 the count is 0 because in there are not prior events for this name in the db. On the 10/03/2001 the count is 1 as the event of the 01/01/2000 was in the five year period before the focal event etc. Note that on the event of the 20/05/2006 the count is 2 as the event on the 01/01/2000 is more then 5 years before the focal event so should not be counted.
I have been trying to use various combinations of select and update queries but everything seems to lead me to a dead end. Any suggestions as to how to approach this problem would be greatly appreciated.