Counting Consecutive Workdays

[M1ndbender]

I am wondering if there is a way to count the consecutive days worked somehow.

Something like the attached. I used one month of data for one person but there could be a whole years data for multiple people

Hopefully this makes sense

Sorry for the paste of data but I keep getting a Cannot access attachments upload path error no matter what file type I try or what PC I try


Data Returned from query         
Person Date Hours   
Dave   9/1/2021   9   
Dave   9/2/2021      
Dave   9/3/2021      
Dave   9/4/2021   9   would be the max consecutive days of 7
Dave   9/5/2021   9   
Dave   9/6/2021   9   
Dave   9/7/2021   9   
Dave   9/8/2021   9   
Dave   9/9/2021   9   
Dave   9/10/2021   8   
Dave   9/11/2021      
Dave   9/12/2021      
Dave   9/13/2021   10   
Dave   9/14/2021   10   
Dave   9/15/2021   10   
Dave   9/16/2021   10   
Dave   9/17/2021   10   
Dave   9/18/2021   10   
Dave   9/19/2021      
Dave   9/20/2021   10   
Dave   9/21/2021   10   
Dave   9/22/2021      
Dave   9/23/2021   10   
Dave   9/24/2021      
Dave   9/25/2021   10   
Dave   9/26/2021      
Dave   9/27/2021   10   
Dave   9/28/2021   10   
Dave   9/29/2021   10   
Dave   9/30/2021   10   
Bill   9/1/2021      
Bill   9/2/2021   8   would be the max consecutive days of 12
Bill   9/3/2021   8   
Bill   9/4/2021   8   
Bill   9/5/2021   8   
Bill   9/6/2021   8   
Bill   9/7/2021   8   
Bill   9/8/2021   8   
Bill   9/9/2021   8   
Bill   9/10/2021   8   
Bill   9/11/2021   8   
Bill   9/12/2021   8   
Bill   9/13/2021   8   
Bill   9/14/2021      
Bill   9/15/2021      
Bill   9/16/2021      
Bill   9/17/2021      
Bill   9/18/2021      
Bill   9/19/2021   10   Would be the second instance of the occurance within the timeframe
Bill   9/20/2021   10   
Bill   9/21/2021   10   
Bill   9/22/2021   10   
Bill   9/23/2021   10   
Bill   9/24/2021   10   
Bill   9/25/2021   10   
Bill   9/26/2021   10   
Bill   9/27/2021   10   
Bill   9/28/2021   10   
Bill   9/29/2021   10   
Bill   9/30/2021   10   


SAMPLE REPORT

Max Consecutive Days   
Bill   12
Dave   7

Max Consecutive days worked      0-6 Days Worked      7-12 Days Worked   12+ Days Worked   
Bill                                                                                            1
Dave                                                                                            1
                                            

# Instances During the Time frame    0-6 Days Worked      7-12 Days Worked   12+ Days Worked   
Bill                                                                                            2   
Dave                                                                                            1   

[dougp]

The attachment is... what?  If it's plain text, pastebin.com should work well.

Maybe your attachment will demonstrate, but what do you mean?  Do you mean consecutive business days (omitting weekends and holidays) worked between non-worked (paid leave, leave without pay, etc.) business days?  Worked business days between the last leave day and today?  In other words, employee A has worked x days without taking any time off?

[BigChris]

If nothing else, you're going to have to set up a calendar table, with all of the dates and something that will identify whether each day is a working day or not.

[M1ndbender]

I still cannot get any attachment to stick. Tried multiple file formats and multiple pcs. I pasted it in as text with the data returned from the query at the top and the sample report at the bottom. Not ideal but at least now you can see what I am talking about

[M1ndbender]

In excel this formula seems to work but translating it to Cognos is a struggle

=MAX(FREQUENCY(IF('Days Worked'!M5:GK5>0,COLUMN('Days Worked'!M5:GK5)),IF('Days Worked'!M5:GK5<=0,COLUMN('Days Worked'!M5:GK5))))

in this formula the 'Days Worked' tab and columns M5:GK5 are the date values