How to distribute budget value to actual rows in Postgresql

From: "Andrus" <kobruleht2(at)hot(dot)ee>
To: <pgsql-general(at)postgresql(dot)org>
Subject: How to distribute budget value to actual rows in Postgresql
Date: 2014-02-09 21:03:53
Message-ID: C22281FBB47749529C11968F04C83B16@dell2
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Budget table contains jobs with loads:

create temp table budget (
job char(20) primary key,
load numeric(4,1) not null check (load>0 )
);
insert into budget values ( 'programmer', 3 );
insert into budget values ( 'analyst', 1.5 );

Actual table contains actual loads by employees:

create temp table actual (
job char(20),
employee char(20),
load numeric(4,1) not null check (load>0 ),
contractdate date,
primary key (job, employee)
);

insert into actual values ( 'programmer', 'John', 1, '2014-01-01' );
-- half time programmer:
insert into actual values ( 'programmer', 'Bill', 0.5, '2014-01-02' );

insert into actual values ( 'analyst', 'Aldo', 1, '2014-01-03' );
insert into actual values ( 'analyst', 'Margaret', 1, '2014-01-04' );

Result table should show difference between budget and actual jobs so that
budget load is
distributed to employees in contract date order.

If budget load is greater than sum of job loads, separate budget line with
empty employee
should appear.

In data above, 1.5 programmers are missing and 0.5 analysts are more.

Result should be

Job Employee Budget Actual Difference

programmer John 1 1 0
programmer Bill 0.5 0.5 0
programmer 1.5 0 1.5
analyst Aldo 1 1 0
analyst Margaret 0.5 1 -0.5

How to create such table in modern Postgresql ?
Can rank function with full join used or other idea ?

I tried

select
coalesce(budget.job, actual.job ) as job,
employee,
budget.load as budget,
coalesce(actual.load,0) as actual,
coalesce(budget.load,0)-coalesce( actual.load,0) as difference
from budget full join actual on (job)
order by contractdate

but this does not distribute budget load to employee rows.

I posted this also in

http://stackoverflow.com/questions/21664842/how-to-distribute-budget-value-to-actual-rows-in-postgresql

Andrus.

Responses

Browse pgsql-general by date

  From Date Subject
Next Message David Johnston 2014-02-09 22:23:48 Re: How to distribute budget value to actual rows in Postgresql
Previous Message Reece Hart 2014-02-09 00:28:16 Re: password-less access, without using pg_hba