showing columns in a particular pattern in postgresql

Question

i have written a query which returns a table showing monthly total working hours of each person in the company. so the result is:

name*****jan************feb**********march ......... dec
Tom     170:24:31    186:27:09    140:00:00........158:17:56

i need a query which gives the following output:

name***jan***jan_salary****feb***feb_salary***....***dec***dec_salary

the salary of each month is the total working hours of that month multiplied by 150$. how can i do this? below is the query i have written to compute total working hours of each person for each month:

with hours as (
  select "Staff_Id", 
     "Date", 
     case 
       when row_number() over w % 2 = 0 then 
          TO_CHAR("Time" - lag("Time") over w,'HH24:MI:SS')
     end as hours
  from "Org"."Clock"
  window w as (partition by "Staff_Id", "Date" order by "Time")
), hours_per_month as (
  select "Staff_Id", 
     extract(year from "Date")::int as work_year,
     extract(month from "Date")::int as work_month,
     sum(hours::interval) work_hours
  from hours
  where hours is not null
  group by "Staff_Id", work_year, work_month
)
select "Staff_Id", 
   work_year,
   sum("work_hours") filter (where work_month = 1) as jan,
   sum("work_hours") filter (where work_month = 2) as feb,
   sum("work_hours") filter (where work_month = 3) as march,
   sum("work_hours") filter (where work_month = 4) as april,
   sum("work_hours") filter (where work_month = 5) as may,
   sum("work_hours") filter (where work_month = 6) as june,
   sum("work_hours") filter (where work_month = 7) as july,
   sum("work_hours") filter (where work_month = 8) as aug,
   sum("work_hours") filter (where work_month = 9) as sep,
   sum("work_hours") filter (where work_month = 10) as oct,
   sum("work_hours") filter (where work_month = 11) as nov,
   sum("work_hours") filter (where work_month = 12) as dec
from hours_per_month  
group by "Staff_Id", work_year

Hambone · Accepted Answer · 2019-01-19 22:33:12Z

I would think you can just add the salary into your "hours_per_month" subquery as follows:

select "Staff_Id", 
   extract(year from "Date")::int as work_year,
   extract(month from "Date")::int as work_month,
   sum(hours::interval) work_hours,
   trunc (extract (epoch from sum(hours::interval)) / 3600) * 150 as salary
from hours
where hours is not null
group by "Staff_Id", work_year, work_month

This presupposes that you want to pay whole hours (15.8 hours = 15 hours of pay), but if not, that's easy enough to tweak.

extract (hours might work also, but not if the number of total hours is > 24. In my opinion, it's not worth it to assume it will, even in a case like this where it would be unrealistic to assume a human would work in excess of 24 hours at one time. Also, you could unwittingly later clone this code to work for something like "machine hours," in which case it would be very possible.

Then, in your main query:

select "Staff_Id", 
   work_year,
   sum("work_hours") filter (where work_month = 1) as jan,
   sum(salary) filter (where work_month = 1) as jan_salary,
   sum("work_hours") filter (where work_month = 2) as feb,
   sum(salary) filter (where work_month = 2) as feb_salary,
   ...
   sum("work_hours") filter (where work_month = 12) as dec,
   sum(salary) filter (where work_month = 12) as dec_salary
from hours_per_month  
group by "Staff_Id", work_year

Totally off-topic, and I'm not trying to tell you how to live your life, but when I see quotes around field and table names, unelss it's at the very last step of the query (rendering output) it makes my skin crawl. It also introduces so many chances for errors. As it happens, I'm not alone in my opinion:

https://wiki.postgresql.org/wiki/Don%27t_Do_This#Don.27t_use_upper_case_table_or_column_names

great! but how can i do it using cross tab instead of filter? also for computing total working hours of each person in each month?

Collectives™ on Stack Overflow

showing columns in a particular pattern in postgresql

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related