Brtdku

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

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