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. -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
