Monday, August 6, 2012

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


create table holiday(d date);

insert into holiday(d) values

-- Monday logic sourced here:

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
  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
  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
  , diff - row_number() over(partition by n order by d) as grp
 from shifted
    -- 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
    case when extract(dow from $1) < $2 then
        $1 - ( extract(dow from $1) + (7 - $2) )::int
        $1 - ( extract(dow from $1) - $2)::int
$$ language 'sql';

No comments:

Post a Comment