Re: How to distribute budget value to actual rows in Postgresql

Andrus Moor wrote
> 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.

sum(...) OVER (ORDER BY)

This provides for a cumulative sum calculation using whatever order you
desire.

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

This is a separate query that would then be added to the budget/actual query
via:

UNION ALL

> 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 don't get how a rank function is going to useful here...

> 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.

My initial reaction is that you will need at least 3 separate sub-queries to
accomplish your goal - though it may be that you have to resort to using
pl/pgsql and implement procedural logic. Pure SQL will probably be
sufficient though.

To make this easier to manage you should use CTE/WITH:

WITH sub1 AS ()
, sub2 AS ()
, sub3 AS ()
, sub4 AS ( SELECT * FROM sub2 UNION ALL sub3 )
SELECT * FROM sub4;

David J.

--
View this message in context: http://postgresql.1045698.n5.nabble.com/How-to-distribute-budget-value-to-actual-rows-in-Postgresql-tp5791170p5791175.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.