Gary Stainburn wrote:
On Friday 07 Feb 2003 10:48 am, Tomasz Myrta wrote:
<cut>
Hi Tomasz,
I don't think you understand what I mean.
The history table could be thought of as the following SQL statement if the data had actually existed. This table actually represents a manually input summary of the pre-computerised data.
insert into history
select rosid, rojid, count(*) from roster_staff group by rssid, rsgsid;
If I have a history of
hsid | hjid | hcount
------+------+--------
1 | 2 | 3
1 | 3 | 1
5 | 5 | 4
6 | 5 | 3
9 | 4 | 4
14 | 5 | 4
and I have a roster of
rodate | rogid | rojid | rosid
-----------+-------+-------+-------
2003-02-15 | 1 | 2 | 1
2003-02-15 | 1 | 5 | 5
2003-02-16 | 1 | 5 | 1
I want my view to show
hsid | hjid | hcount
------+------+--------
1 | 2 | 4
1 | 3 | 1
1 | 5 | 1
5 | 5 | 5
6 | 5 | 3
9 | 4 | 4
14 | 5 | 4
I understood your problem well and I just thought some idea will be enough to continue work. Here is detailed query for your problem: create view some_view as select coalesce(hjid,rjid) as jid, coalesce(hsid,rsid) as sid, coalesce(hcount,1)+count(*)-1 as hcount from history full outer join roster on (hjid=rjid and hsid=rosid) group by hjid,rjid,hsid,rosid; Regards, Tomasz Myrta ---------------------------(end of broadcast)--------------------------- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
