I've managed to do it using a function, shown below, but is there a better
way?
create type site_user_department_limits as (s_id char, de_id int4, date date,
day_id_week int4, day_limit);
create or replace function site_user_department_limits(date,date) returns setof site_user_department_limits as '
select s.s_id, s.de_id, v.date,v.day_of_week::int4,
coalesce(l.day_limit,s.day_limit,0)::int4 as day_limit from ( select date_range as date, extract(DOW from
date_range)as day_of_week
from date_range($1,$2)
) as v
left outer join site_user_department_standard_week s on s.day_of_week =
v.day_of_week
left outer join site_user_department_date_limit l on s.s_id = l.s_id and
s.de_id = l.de_id and v.date = l.de_date
'
language sql;
goole=# select * from site_user_department_limits('2013-10-06','2013-10-12');s_id | de_id | date | day_id_week |
day_limit
------+-------+------------+-------------+-----------H | 80 | 2013-10-06 | 0 | 0H | 80 |
2013-10-07| 1 | 5H | 80 | 2013-10-08 | 2 | 5H | 80 | 2013-10-09 |
3 | 5H | 80 | 2013-10-10 | 4 | 8H | 80 | 2013-10-11 | 5 | 3H
| 80 | 2013-10-12 | 6 | 2
(7 rows)
goole=#
--
Gary Stainburn
Group I.T. Manager
Ringways Garages
http://www.ringways.co.uk