I've managed to come up with a solution that works, and embellished it to give
exactly what I want.
create view availability_details as
select aid, asid, asdate, afdate, adays, count(rsgid) as allocated,
adays-count(rsgid) as afree from (
select aid, asid, asdate, afdate, coalesce(adays,afdate-asdate+1) as adays,
rsgid from availability a
left join roster_staff r on r.rsdate >= a.asdate and r.rsdate <= a.afdate
) as list
group by aid, asid, asdate, afdate, adays
order by asid, asdate;
select * from availability_details ;
aid | asid | asdate | afdate | adays | allocated | afree
-----+------+------------+------------+-------+-----------+-------
8 | 1 | 2007-03-29 | 2007-04-04 | 7 | 1 | 6
4 | 1 | 2007-04-06 | 2007-04-09 | 4 | 0 | 4
5 | 1 | 2007-04-14 | 2007-04-15 | 2 | 2 | 0
6 | 1 | 2007-04-21 | 2007-04-22 | 2 | 0 | 2
1 | 28 | 2007-03-01 | 2007-03-01 | 1 | 0 | 1
2 | 28 | 2007-03-02 | 2007-03-07 | 6 | 0 | 6
(6 rows)
--
Gary Stainburn
This email does not contain private or confidential material as it
may be snooped on by interested government parties for unknown
and undisclosed purposes - Regulation of Investigatory Powers Act, 2000
---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster
