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