Getting a list of elements between two dates

[Tolga Uzuner]

Hi there,

I have a table called AVIN with two columns: element and ndate.

I would like to list all dates on which my table has an entry for an
element and that ndate, and has another entry for that element but
another ndate which is not more than 7 days after.

element is text, and ndate is format date.

I tried this:

SELECT AVIN.element, AVIN.ndate
FROM AVIN
WHERE
ndate<dateadd("d",7,ndate) AND ndate>dateadd("d",0,ndate);

But this returns an empty table, even though I know it should not be empty.

Thanks,
Tolga

 

[John Spencer]

You need to use a coordinated subquery to do this

SELECT Element, nDate
FROM AVIN
WHERE Exists
(SELECT *
FROM AVIN as Tmp
WHERE Tmp.Element = AVIN.Element
And tmp.nDate > AVIN.nDate
And tmp.nDate < DateAdd("d",7,AVIN.nDate))

This won't catch those records were Element and NDate are duplicated.

 

[Tolga Uzuner]

Many thanks,
Tolga

You need to use a coordinated subquery to do this

SELECT Element, nDate
FROM AVIN
WHERE Exists
(SELECT *
FROM AVIN as Tmp
WHERE Tmp.Element = AVIN.Element
And tmp.nDate > AVIN.nDate
And tmp.nDate < DateAdd("d",7,AVIN.nDate))

This won't catch those records were Element and NDate are duplicated.