Thread: Another date range join problem

Hi folks, me again.

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

I have:

Table "public.availability" Column   |            Type        
-----------+------------------------aid       | serial primary keyasid      | integer                asdate    | date
               afdate    | date                   
 
     Table "public.roster_staff"  Column    |  Type   |   Modifiers
-------------+---------+---------------rsdate      | date    | not nullrsgid       | 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     

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