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

How to distribute budget value to actual rows in Postgresql

From
"Andrus"
Date:
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.



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

From
David Johnston
Date:
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.