I have a question with MS SQL Query I am trying to write.
The table fields I am using are (there will be a few more e.g. matching CompanyID to another table for the CompanyName and EXTMins will also be used so I would add mins and hours together but hopefully this request can be written or some guidance I should be able to write the rest)
The table is called Timesheets.
The user can only enter RTI or NRT for Cost Code, ExtHour can be any integer and the CompanyID is matched to another table which for the moment is not important.
I want a query to
Total up the total of ExtHours for NRT and RTI and group by company. I want these answers in just one row for each company.
e.g. Company ID, Total for NRT, Total for RTI.
Now not all companies will have RTI but if there is no data I want it to be 0.
I have tried something very simple which is below.
SELECT costcode, SUM(exthours) AS Expr1, CompanyId
GROUP BY costcode, CompanyId
This works to a limit but it does not group it how I want it.
I was thinking of a query which is like this
SELECT costcode,( SELECT SUM(exthours) from Timesheets where costcode = 'RTI') as RTITotal, (SELECT SUM(extHours) from Timesheets where costCode = 'NRT') AS NRTTOtal, CompanyId
But that doesn't work, so any suggestions would be very useful.