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

Hi folks, me again.

I'm back with my availability and roster tables again.

I have:

Table "public.availability"
  Column   |            Type        
-----------+------------------------
 aid       | serial primary key
 asid      | integer                
 asdate    | date                   
 afdate    | date                   

      Table "public.roster_staff"
   Column    |  Type   |   Modifiers
-------------+---------+---------------
 rsdate      | date    | not null
 rsgid       | integer |
 rsgsid      | integer |
 rssid       | integer |


I want to do a join giving the details from the availability with a count of 
roster_staff records within the range asdate->afdate for each staff member 
(asid <-> rssid)

e.g.
availability

1	1	2007-04-01	2007-04-01
2	1	2007-04-10	2007-04-15

roster_staff

2007-04-01	4	5	1
2007-04-11	4	6	1
2007-04-13	4	3	1
2007-04-14	5	5	1
2007-04-15	5	6	1

giving

1	1	2007-04-01	2007-04-01	1
2	1	2007-04-10	2007-04-15	4

-- 
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