Total hours worked for employees in 2018 and 2019-CodePudding

I am new to stackoverflow and new to sql. I have employee timesheets which has a unique id and hours works by employees. I was wondering if theres a way to calculate the sum of the hours for each employee in 2018.

I dont know if this query is too basic but what i have so far is this:

select distinct PersonId, SUM(reghours) as '2018' 
from TimeSheetsView
where left(yearweek,4)='2018' 
group by PersonId, reghours

CodePudding user response：

If you wanted to sum up both 2018 and 2019 in their own columns you could use a case statement to target those specific records in 2018 and 2019 placing them in their own column. You don't need a distinct with the group by either and like Mitch said you don't need that reghours it will split aggregation.

In the THEN piece it will sum up that field and ELSE is 0 so it won't obviously sum up any other rows that don't meet that condition.

Select PersonId
, SUM(CASE WHEN left(yearweek,4)='2018' THEN reghours ELSE 0 END) as '2018_RegHours' 
, SUM(CASE WHEN left(yearweek,4)='2019' THEN reghours ELSE 0 END) as '2019_RegHours' 
from TimeSheetsView
where left(yearweek,4) IN ('2018', '2019')
group by PersonId