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

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:

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

Fiddle