We're Moving!

The Vertica Forum is moving to a new OpenText Analytics Database (Vertica) Community.

Join us there to post discussion topics, learn about

product releases, share tips, access the blog, and much more.

Create My New Community Account Now


Count of Consecutive days absent excluding weekends — Vertica Forum

Count of Consecutive days absent excluding weekends

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

 

 

 

 

 

 

 

 

 

 

 

 

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Emoji
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file