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