Min/Max Date between 2 diff years?

A

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?
 
D

Dorian

Make sure you have the columns defined as DateTime. It looks like they are
being processed as strings.
-- Dorian
"Give someone a fish and they eat for a day; teach someone to fish and they
eat for a lifetime".
 
A

Allie

Dorian - Great, thanks!

Dorian said:
Make sure you have the columns defined as DateTime. It looks like they are
being processed as strings.
-- Dorian
"Give someone a fish and they eat for a day; teach someone to fish and they
eat for a lifetime".


Allie said:
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?
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top