This is what I did, I used plpgsql,
create or replace function first_accum(anyelement, anyelement) returns anyelement as $$
BEGIN
IF $1 IS NOT NULL THEN return $1;
ELSE return $2;
END IF;
END' language plpgsql;
then I created the aggregate
CREATE AGGREGATE first(sfunc = first_accum, basetype = anyelement, stype = anyelement);
first_accum is basically the same thing as coalesce, but CREATE AGGREGATE wasn't allowing me to use coalesce.
now I can get the salaries and positions each worker had on a given date.
SELECT name, first(startdate), first(salary)
FROM worker
JOIN position ON position.worker_id=worker.worker_id
WHERE fecha<='2006-05-01'
ORDER BY fecha DESC;
I'd appreciate some feedback, I hope there's a better way to do this. (maybe without creating the plpgsql function but using an internal function)
On 7/14/06, Sergio Duran <sergioduran@gmail.com> wrote:
Nice, Richard, but you use max(startdate), how about the salary? i cant use max(salary) how about if he got a pay cut?
My current solution is to write the nested query on the field list, like
SELECT worker.*,
(select salary
FROM position where worker_id=worker.worker_id
and fecha<='2006-04-01' LIMIT 1) as salary
FROM worker;
but I can only return 1 column from that subquery and repeating the same subquery for each column needed (position, date and salary) seems a little too much, if I write a procedure would postgres would optimize the access?
On 7/13/06, Richard Broersma Jr < rabroersma@yahoo.com> wrote: >
> worker: worker_id, name
> position: position_id, worker_id, position, startdate, salary
>
> If I perfom a query joining both tables, I can obtain all the workers and
> the positions the've had.
>
> SELECT name, startdate, position, salary FROM worker JOIN position
> USING(worker_id);
> worker1 | 2001-01-01 | boss | 999999
> worker2 | 2001-01-01 | cleaning | 100
> worker2 | 2006-04-01 | programmer | 20000
> worker2 | 2006-07-04 | management | 25000
>
> so far so good, now I need to obtain all the workers only with the position
> they had on a given date.
> if I wanted to know the positions on '2006-05-01' it would return
> worker1 | 2001-01-01 | boss | 999999
> worker2 | 2006-04-01 | programmer | 20000
>
This is just a quick guess. I am not sure if the logic is correct but it could be a starting
point.
select P2.worker_id, P2.pdate, P1.position, P1.salary
from position as P1
join
(select worker_id, max(startdate) as pdate
from position
where startdate <= '2006-05-01'
group by worker_id, position_id) as P2
on (P1.worker_id = P2.worker_id) and (P1.startdate = P2.pdate)
;
Regards,
Richard Broersma Jr.