SQL Query: Count with "having" date in the last five years

[pierrelap]

Hello, I need to code a query that:
1-counts the number of time two companies have been in a deal together
2-in the five years that preceded the deal

Lead Participant DealDate
AAA BBB 01/01/1990
BBB AAA 01/01/1991
AAA BBB 01/01/1992
AAA BBB 01/01/1993
AAA BBB 01/01/1994
BBB AAA 01/01/1995
AAA BBB 01/01/1996
BBB AAA 01/01/1997

I managed to find the code for the first part:

SELECT DISTINCT Lead, Participant, Count(DealDate) AS Count
FROM test
GROUP BY Lead, Participant
ORDER BY Lead;

It gave me this and it is perfect

Lead Participant Count
AAA BBB 5
BBB AAA 3

However, I'm puzzled by the second part. How do I query this table so
I have a column that counts the deal in the PREVIOUS 5 years?

I would need this result

Lead Participant DealDate Count last 5 years
AAA BBB 01/01/1990 0
BBB AAA 01/01/1991 0
AAA BBB 01/01/1992 1
AAA BBB 01/01/1993 2
AAA BBB 01/01/1994 3
BBB AAA 01/01/1995 1
AAA BBB 01/01/1996 4
BBB AAA 01/01/1997 2

Where the Count last 5 years is the number of deals in the previous 5
years.

Thanks

[ja]

try this:
SELECT DISTINCT Lead, Participant, Count(DealDate) AS Count
FROM test
(assuming 2005 is the present year)
where dealdate > '12/31/2000'
GROUP BY Lead, Participant
ORDER BY Lead;


On 26 May 2005 17:40:24 -0700, pi*******@gmail.com wrote:

Hello, I need to code a query that:
1-counts the number of time two companies have been in a deal together
2-in the five years that preceded the deal

Lead Participant DealDate
AAA BBB 01/01/1990
BBB AAA 01/01/1991
AAA BBB 01/01/1992
AAA BBB 01/01/1993
AAA BBB 01/01/1994
BBB AAA 01/01/1995
AAA BBB 01/01/1996
BBB AAA 01/01/1997

I managed to find the code for the first part:

SELECT DISTINCT Lead, Participant, Count(DealDate) AS Count
FROM test
GROUP BY Lead, Participant
ORDER BY Lead;

It gave me this and it is perfect

Lead Participant Count
AAA BBB 5
BBB AAA 3

However, I'm puzzled by the second part. How do I query this table so
I have a column that counts the deal in the PREVIOUS 5 years?

I would need this result

Lead Participant DealDate Count last 5 years
AAA BBB 01/01/1990 0
BBB AAA 01/01/1991 0
AAA BBB 01/01/1992 1
AAA BBB 01/01/1993 2
AAA BBB 01/01/1994 3
BBB AAA 01/01/1995 1
AAA BBB 01/01/1996 4
BBB AAA 01/01/1997 2

Where the Count last 5 years is the number of deals in the previous 5
years.

Thanks

[pierrelap]

Hello,

The problem in the code you gave me is that I want the where to be

where "dealdate of each line">"dealdate of this particular line"-5
years.

Look at the table I gave, the "Count last 5 years" does that