Min/Max Dates

[Allie]

I have a list of employee absences and I'd like to find the number of days
between the earliest and latest dates. It almost works....
I have a min/max set up as follows:

SELECT Records.BADGE, Sum(Records.DAYS) AS SumOfDAYS, Min(Records.DATE) AS
MinOfDATE, Max(Records.DATE) AS MaxOfDATE,
Abs(DateDiff('d',[MINOFDATE],[MAXOFDATE])) AS DIFFERENCE
FROM Records
GROUP BY Records.BADGE;

If a particular employee has absences for all one year, the query returns
the results perfectly fine. But in the example below, where the earliest and
latest dates span over 2 years - the days are wrong. Ex:

2/5/08
10/9/08
1/3/09
It should find the days between 2/5/08 and 1/3/09.
Instead, it picks 1/3/09 as mindate and 10/9/08 as maxdate. I believe
because of the MONTHS.

How can I get it consider the year first? Or is there a better way to do this?

 

[tkelley via AccessMonster.com]

This may not be it, given that I don't know the whole picture. But my first
inclination is to wonder if it's treating your dates as dates. What happens
if you try this:

SELECT Records.BADGE, Sum(Records.DAYS) AS SumOfDAYS, Min(CDate(Records.DATE))
AS
MinOfDATE, Max(CDate(Records.DATE)) AS MaxOfDATE,
Abs(DateDiff('d',[MINOFDATE],[MAXOFDATE])) AS DIFFERENCE
FROM Records
GROUP BY Records.BADGE;


As you can see, when I use the DateDiff on your two dates (explicitly typed),
it does indeed return 333:

? DATEdiff("d",#2/5/08#,#1/3/09#)
333

I have a list of employee absences and I'd like to find the number of days
between the earliest and latest dates. It almost works....
I have a min/max set up as follows:

SELECT Records.BADGE, Sum(Records.DAYS) AS SumOfDAYS, Min(Records.DATE) AS
MinOfDATE, Max(Records.DATE) AS MaxOfDATE,
Abs(DateDiff('d',[MINOFDATE],[MAXOFDATE])) AS DIFFERENCE
FROM Records
GROUP BY Records.BADGE;

If a particular employee has absences for all one year, the query returns
the results perfectly fine. But in the example below, where the earliest and
latest dates span over 2 years - the days are wrong. Ex:

2/5/08
10/9/08
1/3/09
It should find the days between 2/5/08 and 1/3/09.
Instead, it picks 1/3/09 as mindate and 10/9/08 as maxdate. I believe
because of the MONTHS.

How can I get it consider the year first? Or is there a better way to do this?

 

[KARL DEWEY]

I think they want difference of 10/9/08 and 1/3/09.
Use a subquery to find max date then max that is less than that of the
subquery.
If not subquery, Badge and max in a query inner joined on badge that pulls
max less than first query.
--
KARL DEWEY
Build a little - Test a little

This may not be it, given that I don't know the whole picture. But my first
inclination is to wonder if it's treating your dates as dates. What happens
if you try this:

SELECT Records.BADGE, Sum(Records.DAYS) AS SumOfDAYS, Min(CDate(Records.DATE))
AS
MinOfDATE, Max(CDate(Records.DATE)) AS MaxOfDATE,
Abs(DateDiff('d',[MINOFDATE],[MAXOFDATE])) AS DIFFERENCE
FROM Records
GROUP BY Records.BADGE;


As you can see, when I use the DateDiff on your two dates (explicitly typed),
it does indeed return 333:

? DATEdiff("d",#2/5/08#,#1/3/09#)
333

I have a list of employee absences and I'd like to find the number of days
between the earliest and latest dates. It almost works....
I have a min/max set up as follows:

SELECT Records.BADGE, Sum(Records.DAYS) AS SumOfDAYS, Min(Records.DATE) AS
MinOfDATE, Max(Records.DATE) AS MaxOfDATE,
Abs(DateDiff('d',[MINOFDATE],[MAXOFDATE])) AS DIFFERENCE
FROM Records
GROUP BY Records.BADGE;

If a particular employee has absences for all one year, the query returns
the results perfectly fine. But in the example below, where the earliest and
latest dates span over 2 years - the days are wrong. Ex:

2/5/08
10/9/08
1/3/09
It should find the days between 2/5/08 and 1/3/09.
Instead, it picks 1/3/09 as mindate and 10/9/08 as maxdate. I believe
because of the MONTHS.

How can I get it consider the year first? Or is there a better way to do this?