From: Gary Stainburn <gary.stainburn@ringways.co.uk>
Organization: Ringways Garages Ltd
To: pgsql-sql@postgresql.org
Subject: SOLVED - Another date range join problem
Date: Thu, 5 Apr 2007 17:03:04 +0100
User-Agent: KMail/1.9.5
References: <200704051617.59022.gary.stainburn@ringways.co.uk>
In-Reply-To: <200704051617.59022.gary.stainburn@ringways.co.uk>
MIME-Version: 1.0
Content-Type: text/plain;
  charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
Message-Id: <200704051703.04288.gary.stainburn@ringways.co.uk>

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