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.