How to calculate consecutive work days?

[#1]

Hi all,

The following is an example of how my table looks like. Now i would like to hightlight out the staff who worked for X number of consecutives days.

Maybe a field to show between 21 to 23 what is the consecutive numbers of days they worked. or any other suggestion?

Entry

EntryDate	StaffName	TimeIn	Message
21/11/12	John	07:00:00	IN
21/11/12	Peter	08:10:00	IN
21/11/12	John	09:20:00	IN
21/11/12	Mary	07:20:00	IN
22/11/12	John	07:00:00	IN
22/11/12	Peter	08:00:00	IN
23/11/12	John	07:00:00	IN
23/11/12	Mary	07:00:00	IN
23/11/12	Joe	08:00:00	IN
23/11/12	Mary	09:00:00	IN

Result:

John : 3

Mary: 0

Peter: 2

Joe: 0

And how can i prompt user to enter the value for X. So if they click 3, John will be highlighted and 2 Peter will be highlighted.

Guidance please.

Thanks,

10e5x

[#2]

Hi Gysbert,

I have included the staffName but it doesnt change for the better. Seriously need your advise. I am stuck on this problem for many days. Please help me.

Thanks,

10e5x

[Gysbert_Wassenaar · Accepted solution]

Two things:

1. make sure the order by clause is order by StaffName, EntryDate, not order by EntryDate, StaffName

2. make sure EntryDate is recognized as a date. To be sure you can use date#(EntryDate,'DD/MM/YYY') as EntryDate in the load statement

Another thing, you may want to calculate the counter first using T1, then drop T1 and join Data on the second SQL load with the legal exits. You now have a couple of unnecessary joins which is kinda messy .

Entry2:

Load

EventDate,

date#(EntryDate,'DD/MM/YYYY') as EntryDate,

StaffName,PassNum,Company,TimeIn    ;

SQL Select

`EVENT_D` AS EventDate,

`EVENT_D` AS EntryDate,

`PASS_M` AS StaffName,

`PASS_N` AS PassNum,

`COMPANY_M` AS Company,

Min(EVENT_T) as TimeIn

From `TACS_ACCESS_DTL`

WHERE `MESSAGE_X` Like '%Legal Access%'

group by `EVENT_D`, `PASS_M`, `PASS_N`, `COMPANY_M`;

Data:

load *,

if(rowno()=1 or StaffName <> previous(StaffName),1,

if(EntryDate-previous(EntryDate)=1, peek('Counter')+1,peek(Counter))) as Counter

Resident Entry2 order by StaffName,EntryDate;

Drop table Entry2;

join (Data)

Load

     *;

SQL Select

`EVENT_D` AS EventDate,

`EVENT_D` AS ExitDate,

`PASS_M` AS StaffName,

`PASS_N` AS PassNum,

`COMPANY_M` AS Company,

Max(EVENT_T) as TimeOut

From `TACS_ACCESS_DTL`

WHERE `MESSAGE_X` Like '%Legal Exit%'

group by `EVENT_D`, `PASS_M`, `PASS_N`, `COMPANY_M`;

[#4]

THANKS gysbert, it works. the counter 3 is out. Although the selection is weird(not i wanted but its good enough, i shall modify from here)

I am looking at the script and table to uds how u manage to did that. i realise that my dates are not in the date format. Thanks for that nice advice.

May I come to you if i need further help?

Appreciated,

10e5x

[#5]

Hi Gysbert,

This is very close to the solution that I am looking for and wondered if you could help me? Rather than calculate consecutive days I want to identify instances of consecutive days. Not sure if it's etiquette to add the post here and have added previously but had no takers and your solution seems very close.

I sent you a friend request and hope to hear from you.

Many thanks

Natalie