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