I have two tables, one defining a standard week by user department, the other
defining a calendar where specific dates can deviate from the standard. The
tables are shown below.
I'm trying to generate a view where I can do
select * from user_department_daily_limits where de_date >= '2013-10-06' and
de_date <= '2013-10-12'
and it will generate 7 records using the deviation table for records that
exist or the standard week where it doesn't.
I'm working on the idea that I will actually have to use a date range
generator functoin to actually drive the view but I still can't get my head
round it. Because I'm forced to work on Postgresql 8.3.3 I've had to write my
own date_range function.
The best I can come up with is the following select but I can't work out how
to convert it to a view.
select s.s_id, s.de_id, v.date,v.day_of_week,
coalesce(l.day_limit,s.day_limit,0) as day_limit from ( select date_range as date, extract(DOW from date_range) as
day_of_week
from date_range('2013-10-06'::date,'2013-10-12'::date)
) 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;
Gary
create table site_user_department_standard_week ( s_id char not null, de_id int4 not null, day_of_week
int4not null CHECK (day_of_week >= 0 and day_of_week <= 6), day_limit int4 not null CHECK (day_limit >= 0), primary
key(s_id,de_id, day_of_week), foreign key (s_id, de_id) references site_user_departments (s_id, de_id)
);
-- user_department_date_limit
-- defines records by user department / date to override the
-- standard week
create table site_user_department_date_limit ( s_id char not null, de_id int4 not null, de_date date
notnull, day_limit int4 not null CHECK (day_limit >= 0), primary key (s_id,de_id, de_date), foreign key (s_id, de_id)
referencessite_user_departments (s_id, de_id)
);
--
Gary Stainburn
Group I.T. Manager
Ringways Garages
http://www.ringways.co.uk