Monitoring perfect attendance. Consecutive calendar date query

Postgresql implementation for Monitoring perfect attendance

create table tx
(
i serial not null primary key,
n varchar(10), d date,
constraint ux_tx unique(n,d)
);

insert into tx(n,d) values
('john','2012-7-3'),
('john','2012-7-5'),
('john','2012-7-6'),
('john','2012-7-9'),
('john','2012-7-12'),
('john','2012-7-13'),
('john','2012-7-16'),
('john','2012-7-17'),
('john','2012-7-18'),
('john','2012-7-20'),
('john','2012-7-30'),
('john','2012-7-31'),

('paul','2012-7-3'),
('paul','2012-7-5'),
('paul','2012-7-18'),
('paul','2012-7-19'),
('paul','2012-7-20'),
('paul','2012-7-23'),
('paul','2012-7-24'),
('paul','2012-7-25'),
('paul','2012-7-26'),
('paul','2012-7-27'),
('paul','2012-7-30'),
('paul','2012-7-31'),
('paul','2012-8-1'),
('paul','2012-8-3'),
('paul','2012-8-6'),
('paul','2012-8-7');

create table holiday(d date);

insert into holiday(d) values
('2012-7-4');


-- Monday logic sourced here: http://www.ienablemuch.com/2010/12/finding-previous-day-of-week.html




with first_date as
(
--  select dateadd( ww, datediff(ww,0,min(d)), 0 ) as first_date -- get the monday of the earliest date

select previous_date_of_day(min(d), 1) as first_date 
from tx 
)
,shifted as
(
 select
  tx.n, tx.d, 
     
  (tx.d - fd.first_date) - ( (tx.d - fd.first_date) / 7 * 2 ) as diff
   
 from tx
 cross join first_date fd
 union
 select
  xxx.n, h.d, 
   
  
  (h.d - fd.first_date) - ((h.d - fd.first_date) / 7 * 2) as diff
  
 from holiday h 
 cross join first_date fd
 cross join (select distinct n from tx) as xxx
)
,grouped as
(
 select
  *
  , diff - row_number() over(partition by n order by d) as grp
 from shifted
)
select
     
    -- remove staging columns from the output...
    -- * 
     
    -- ...just output what the user will see:
    d, n
     
 ,dense_rank() over (partition by n order by grp) as nth_streak
 ,count(*) over (partition by n, grp) as streak
from grouped
where d not in (select d from holiday)  -- remove the holidays

Get the previous day of the date if the day is not exactly on that date:

create or replace function previous_date_of_day(the_date date, dow int) returns date
as
$$
select
    case when extract(dow from $1) < $2 then
        $1 - ( extract(dow from $1) + (7 - $2) )::int
    else
        $1 - ( extract(dow from $1) - $2)::int
    end;
$$ language 'sql';