This is a good query if you want all timesheet actuals, including those that may not have been updated via a republish of a project.

 

SELECT    TSR.ResourceName,
        TSC.ClassName ‘Project Classifications’,
        TSA.ActualWorkBillable ‘AssignmentActualWork’ ,
        TSA.TimeByDay,
        TSC.Type,
        mtt.TaskName,
        ISNULL(MTP.ProjectName, TSC.ClassName) ‘ProjectName’
FROM dbo.MSP_TimesheetActual AS TSA — Stores planned & actual work per day
INNER JOIN dbo.MSP_TimesheetLine AS TSL
ON TSA.TimesheetLineUID = TSL.TimesheetLineUID — Tells us what line type the actual belongs to
INNER JOIN dbo.MSP_TimesheetClass AS TSC
ON TSL.ClassUID = TSC.ClassUID — Tells us all the line types
INNER JOIN dbo.MSP_Timesheet AS TS
ON TS.TimesheetUID = TSL.TimesheetUID — Tells us who’s timesheet the line belongs to
INNER JOIN dbo.MSP_TimesheetResource AS TSR
ON TS.OwnerResourceNameUID = TSR.ResourceNameUID — tells us the TS resource name when the TS was submitted
INNER JOIN dbo.MSP_EpmResource_UserView mer
ON mer.ResourceUID = TSR.ResourceUID
INNER JOIN MSP_TimesheetTask mtt
on mtt.TaskNameUID = TSL.TaskNameUID
INNER JOIN MSP_TimesheetProject MTP
ON MTP.ProjectNameUID = TSL.ProjectNameUID
LEFT OUTER JOIN MSP_EpmProject_UserView MEP
ON MEP.pROJECTuid = MTP.ProjectUID
–Where tsa.TimeByDay between @StartDate and @EndDate
–and TSA.ActualWorkBillable <> 0.00