From: | Gary Stainburn <gary(dot)stainburn(at)ringways(dot)co(dot)uk> |
---|---|
To: | pgsql-sql(at)postgresql(dot)org |
Subject: | SOLVED - Another date range join problem |
Date: | 2007-04-05 16:03:04 |
Message-ID: | 200704051703.04288.gary.stainburn@ringways.co.uk |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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
From | Date | Subject | |
---|---|---|---|
Next Message | Sumeet | 2007-04-05 17:42:08 | auto vacuuming |
Previous Message | Scott Marlowe | 2007-04-05 15:24:26 | Re: Generating dates prior to generate_series |