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

## Advertisement

## Answer

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

**7**People found this is helpful