CODEWITHSUNDEEP
postgresql
Piechartking
Piechartking

Reputation: 135

Postgresql Apportionment of Values in Same Column

If I am running payroll reports, I have normal employees whose pay is divided between 2 different departments. So basically, one employee's payroll costs will be apportioned 50% to my report, because I don't count the other 50% (those payroll costs count toward the other department)

However, the director oversees (for example) 8 teams, so her payroll costs are apportioned 12.5% for the report.

For the report, I add the employees 50% costs but I don't know how to also add the Director's 12.5% costs together in the same column. My code is this, but I would like to find out how to tweak it so that I get what I need:

select 
 to_char("Date", 'YYYY') as "Date",
 case
   when "Type" in ('E001', 'E002', 'E003', 'E004') then 0.5 * sum ("Amount") filter (where "Type" in ('E001', 'E002', 'E003', 'E004'))
   when "Type" like 'E9%' then 0.125 * sum("Amount") filter (where "Type" like 'E9%')
 end as "Salaries Shared with Accounting"
 from "Transactions"
 group by 1
 order by min("Date");

As you can see, this will return 50% of the payroll for each employee, but I cannot get it to output 50% of E001, E002, E003, and E004, and also add in 12.5% of E999.

Upvotes: 0

Views: 108

Answers (1)

Nick
Nick

Reputation: 123

Looks like the data model is not designed to deal with this particular use case, however there is a rather unpleasant solution using conditional query:

select
        to_char("Date", 'YYYY') as "Date",
        case 
            when "Type" in ('E001', 'E002', 'E003', 'E004') then 0.5 * sum ("Ammount") filter (where "Type" in ('E001', 'E002', 'E003', 'E004')
            when "Type" like 'E9%' then 0.125 * sum("Ammount") filter (where "Type" like 'E9%'
        end as "Salaries Shared with Accounting"

However there might be a more satisfying solution involving window functions. I believe it is possible to fully automate the calculations above, rather than changing query each time someone gets additional project assigned.

Upvotes: 1

Related Questions