Count Consecutive Days Absent

[aolevano]

Hi All,   (Using Cognos 8.4 report studio)

I'm stumped on trying to get a count of consecutive absences for a given date range.  I have 3 sets of data I can use:
  (A) lists all calendar dates in a year (starts count at July 1),
  (B) lists possible days of attendance for person  (Sat., Sun., Holidays excluded from set),
  (C) lists a set of actual absent days for person


Sample Data:

(A) 
CAL_DATE   DAY_NUMBER
2011-11-23    146
2011-11-24    147
2011-11-25    148
2011-11-26    149
2011-11-27    150
2011-11-28    151
2011-11-29    152
2011-11-30    153
2011-12-01    154

(B)
ValidDate          (note that Thanksgiving (11/24), Sat., Sun. dates not there; not valid attendance dates)
2011-11-23                 
2011-11-25                 
2011-11-28                 
2011-11-29                 
2011-11-30   
2011-12-01               

(C)
AbsentDate     
2011-11-23                 
2011-11-25                 
2011-11-28     
2011-12-01

Results should show 3 consecutive absences for 11/23, 11/25, 11/28, and 1 consecutive absence on 12/1

I've been attempting a number of things using left outer from from (B) on (C) using running-count, running-total, etc.  Can't get this to work because the count doesn't reset on a "present" day (11/29, 11/30)
   
Any help appreciated,
Thanks!

[blom0344]

I'm dutch, so that's probably why I am missing your point   ;), but as I read it the results you want are identical to the set of absence days?  I'm sure not, but what then?

[aolevano]

The absent days are the data details;  I need the aggregate count for each set of consecutive days.  So this person should have a result like :

Staff          Consec. Days
PersonA           3
PersonA           1

Obviously, there'd be multiple Staff members; each with the possibility of multiple sets of consecutive days (or none at all).  I need the count to "reset" on a day the staff member is "present"...

[blom0344]

Using set operators (except / union)

Set B
except
Set C

would yield (Set D):

2011-11-29                 
2011-11-30

Use this (D) as input for the union set with Set C
Add a dataitem 'Type' with value 'A' for set C and V for set D:

Result:

A  2011-11-23                 
A  2011-11-25                 
A  2011-11-28                 
V  2011-11-29                 
V  2011-11-30   
A  2011-12-01

[aolevano]

Thanks for the idea.

..But from this result set, how do I get consec. counts to come out?   Results should be two records, counts of 3 and 1.
If I group on "Type", I'd get a single count of 4...