Calculate the number of periods of absence

[terre]

I have a table shown below....

DateAbs WorkdayID
24/01/2007 24
25/01/2007 25
22/02/2007 53
7/03/2007 66
8/03/2007 67
10/05/2007 130
4/06/2007 155
3/08/2007 215
25/10/2007 298

I want to return the number of periods of absence (7)
and the total number of days absent (9).

Is there a simple query that will achieve this please?

 

[CaptainD]

Something like this

SELECT COUNT(DateAbs) as [TotalAbs] FROM tAbsDates
UNION
SELECT COUNT(a.[NumberOfPeriods]) AS [Periods] FROM
(SELECT DISTINCT DatePart("m",DateAbs) AS NumberOfPeriods
FROM tAbsDates) a;

 

[terre]

Perhaps I need to clarify

The table name is tblAbsDates

2,3, or even 60 consecutive absences count as only one period of absence.......as does just 1 day

I want to know how to calculate the number of periods of absence as well as the number of days absent.

To allow for holidays, weekends etc the table has a field WorkdayID. Thus consecutive workdays will have consecutive WorkdayID's.
Hope this clarifies

Thanks in advance
Terre

 

[CaptainD]

DateAbs WorkdayID
24/01/2007 24
25/01/2007 25
22/02/2007 53
7/03/2007 66
8/03/2007 67
10/05/2007 130
4/06/2007 155
3/08/2007 215
25/10/2007 298

I want to return the number of periods of absence (7)

How are you getting 7 out of these dates?

The only thing I noticed that comes out to 7 are the seven different months.

 

[SkipVought]

You did not initially, describe what the definition of a PERIOD is.

Is a period defined as consecutive workdayIDs, in which case the periods would be...
[tt]
DateAbs WorkdayID Period
24/01/2007 24 1
25/01/2007 25 1
22/02/2007 53 2
7/03/2007 66 3
8/03/2007 67 3
10/05/2007 130 4
4/06/2007 155 5
3/08/2007 215 6
25/10/2007 298 7
[/tt]
Is this the case?

Skip,

_{Did you hear what happened when the OO programmer lost his library?...
He's now living in OBJECT poverty!}

 

[terre]

Yes Skip....exactly, and 4,5 or even 50 consecutive workdays would also be one period......

Sorry for my descriptions if they caused confusion

Terre

 

[CaptainD]

I assume these are also tied to a person or persons, how do you distinguish one from another?

 

[terre]

Thanks for persistence CaptainD

There is a bigger table which has a EmployeeID field as well as the others.

However, I am seeking to calculate the number of absence periods of an already obtained subset.(a datasheet subform linked to EmployeeID)
Thx
terre

 

[CaptainD]

I'm at a loss with the way you have it set up. There is nothing to distiguish a "Period", especially when you throw in the exclusions for Holidays and weekends.

I might suggest you throw in another table or field that adds a "Ticket" number for an Absence. When someone first calls in you start a new ticket and for each consecutive absence, you keep the same ticket number. If it is a new accurance, you start a new ticket. With that you can then do a count for each occurance.

 

[terre]

The data is imported from another database.
Holidays etc are already accounted for within the workdayid

I'll see what i can do

 

[terre]

If only I could do as SkipVought indicated

 

[terre]

I got out of it by using the following.......

SELECT Count(tblAbsent.WorkDayID) AS CountOfWorkDayID, [WorkDayID]-DCount("*","tblAbsent","WorkDayID <= " & [WorkDayID]) AS Expr1
FROM tblAbsent
GROUP BY [WorkDayID]-DCount("*","tblAbsent","WorkDayID <= " & [WorkDayID]);

Which gave me

CountOfWorkDayID Expr1
3 0
1 18
2 26
1 60
1 76
1 109
1 157


and then counting how many records

Thanks for those who thought about the problem