SOLVED - Another date range join problem - Mailing list pgsql-sql

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