Home > Back-end >  Find sum of hours for each date worked
Find sum of hours for each date worked

Time:10-10

I have a table of timesheet entries set up like this:

id job_id employee_id hours_worked date_worked
1 1 111 8 2022-10-01
2 1 222 8 2022-10-01
3 1 222 8 2022-10-02
4 2 222 8 2022-10-03
5 2 111 8 2022-10-04
6 2 222 5 2022-10-05
7 3 111 8 2022-10-04
8 4 333 8 2022-10-07
9 4 111 3 2022-10-09

I'm trying to find the sum of hours for the first, second, third etc dates that work was done on each job

Ideally I'd like something like this:

job_id Day1_hours Day2_hours Day3_hours
1 16 8 0
2 8 8 5
3 8 0 0
4 8 3 0

The trouble I'm running into is that there can be multiple employees working on each day of the job, so using a query to select the min(date_worked) greater than a subquery for min(date_worked) is sometimes giving me the same dates. There are sometimes days in between work done on a job, so I can't just add a day to the minimum value and check hours for that date.

How can I find the sum of hours_worked for the first date_worked, then the second, third etc?

CodePudding user response:

PIVOT's are great but conditional aggregations offer a bit more flexibility

Example

Select job_id
      ,[Day1_Hours] = sum( case when DN=1 then hours_worked else 0 end)
      ,[Day2_Hours] = sum( case when DN=2 then hours_worked else 0 end)
      ,[Day3_Hours] = sum( case when DN=3 then hours_worked else 0 end)
 From ( Select *
              ,DN = dense_rank() over (partition by job_id order by date_worked)
         From  YourTable
      ) A
 Group By Job_ID

CodePudding user response:

select job_id
      ,[1] as day1_hours
      ,[2] as day2_hours
      ,[3] as day3_hours
from   (
       select  job_id 
              ,hours_worked
              ,dense_rank() over(partition by job_id order by date_worked) as days
       from   t
       ) t
pivot  (sum(hours_worked) for days in([1],[2],[3])) p
job_id day1_hours day2_hours day3_hours
1 16 8 null
2 8 8 5
3 8 null null
4 8 3 null

Fiddle

  • Related