Need help w/ a query 1

[4281967]

I am trying to get a count of how many contracts were renewals -
The data elements in the table (F1720) are:
CHAN8 = Cust #
CHDOCO = contract #
CHLANO = site #
CHCSDT = start date
CHCDTE = end date


here is my SQL:
SELECT F1720.CHAN8, F1720.CHDOCO, F1720.CHLANO, F1720.CHCSDT, F1720.CHCDTE
FROM F1720
ORDER BY F1720.CHAN8, F1720.CHDOCO;


basically - any contract where the diff between the end date and the start of the next contract is over 6 months - this is considered a renewal and i need to count it. (Note: this is true only where the site # is the same)


hope this makes sense...

Thanks

 

[dhookom]

Sample data and expected results please?

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[4281967]

(sample data)
CHAN8 CHDOCO CHLANO CHCSDT CHCDTE
30657 1869 30658 12/1/2003 11/30/2004
30657 6388 30658 12/1/2004 11/30/2005
30657 10910 30658 12/1/2005 11/30/2006

30675 1870 30676 4/1/2003 3/31/2004
30675 3130 30676 4/1/2004 3/31/2005
30675 7765 30676 10/1/2005 9/31/2006 **

30783 2058 30784 11/1/2003 10/31/2004
30783 6027 30784 11/1/2004 10/31/2005
30783 10586 30799 11/1/2006 10/31/2007 ***

30793 1368 30794 3/1/2004 11/30/2004
30793 6443 30794 12/1/2004 11/30/2005

In this example I added a blank line between cust #'s just to make it easier to read.


Section 1 no count since all dates are within 6 months.

Section 2 (**)- this is a count since the start date is more than 6 months since previous end date.

Section 3 (***) - this is not a count - although dates are more than 6 months apart - the site # (CHLANO) is diferent

Section 4 no count since all dates are within 6 months.

 

[dhookom]

Try this SQL that uses a subquery:

SELECT F1720.*
FROM F1720
WHERE (((DateDiff("m",(SELECT Max(CHCDTE) FROM F1720 as Copy WHERE Copy.CHLANO=F1720.CHLANO AND Copy.CHCSDT<F1720.CHCSDT),[CHCSDT]))>=7));

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[4281967]

Duane,
Interesting SQL - I think It works, but it's bringing my machine to a crawl...

Thanks

 

[4281967]

Actually - the query locks Access up

 

[dhookom]

It works great for 11 records. How many records are in your table? Do you have indexes on CHCDTE, CHLANO, and CHCSDT?

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[4281967]

they were not indexed - adding the indexes helped greatly. Thank you