Count of Consecutive days absent excluding weekends

MilaMicha

Hi all,

 

I have a table with attendancedata having date, empno timein/timeout columns without weekend dates(Friday and saturday are weekends)

 

Please Note:

               1,  The table  is DateTimeAttendance

               2,  Time-In is NULL for those employees who are absent.

 

So my condition here is (TimeIn=Null)

 

I tried the following analytics features of Vertica on data and got the outpout as follows

 

 

 

 

 

 

select name,date,CONDITIONAL_TRUE_EVENT(date - lag(date)>1)over(partition by name order by date) as ConsecutiveDatesCounter from DateTimeAttendance where timein is null group by name,date ;

 

sample ouput :

 

name                    date ConsecutiveDatesCounter

 

Aaron Gadsen      19/3/2014    0

 

Aaron Gadsen      23/3/2014    1

 

Aaron Gadsen      24/3/2014    1

 

Aaron Gadsen      25/3/2014    1

 

Aaron Gadsen      26/3/2014    1

 

Aaron Gadsen      27/3/2014    1

 

Aaron Gadsen      30/3/2014    2

 

Aaron Gadsen      31/3/2014    2

 

 

Here 28/3/2014 and 29/3/2014 are weekends, so i want the ConsecutiveDatesCounter 1 should not changed to 2, it should remain  as 1

 

 

 

I want to get the output as follows

 

 

name                    date       ConsecutiveDatesCounter

 

Aaron Gadsen      19/3/2014    0

 

Aaron Gadsen      23/3/2014    1

 

Aaron Gadsen      24/3/2014    1

 

Aaron Gadsen      25/3/2014    1

 

Aaron Gadsen      26/3/2014    1

 

Aaron Gadsen      27/3/2014    1

 

Aaron Gadsen      30/3/2014    1

 

Aaron Gadsen      31/3/2014    1

 

Next query on the above result will be as follows

 

select name, count(1) num_days,min(date)startdate,max(date)enddate from (select name,date,CONDITIONAL_TRUE_EVENT(date - lag(date)>1)over(partition by name order by date) as ConsecutiveDatesCounter from DateTimeAttendance where timein is null group by name,date ) as consecutive group by name, consecutiveDatesCounter order by startdate;

 

 

Final Output should be like this :

 

name                 num_days   startdate      enddate       

 

Aaron Gadsen     1               19/3/2014   19/3/2014   

 

Aaron Gadsen     7               23/3/2014    31/3/2014

 

 

Please help me to resolve this issue of weekend in this scenario..

 

Thanks in Advance