declare @startDate datetime = ; declare @endDate datetime = ; declare @userId int = ; with timelog as ( select scrumTimeUpdateUserId as userId, 'D_' + format( scrumTimeUpdateDate, 'MM_dd_yyyy' ) as dayCode, requestId, scrumTimeUpdateHour hours from tbl_SCRUM_TimeLog tl where scrumTimeUpdateDate >= @startDate and scrumTimeUpdateDate <= @endDate and scrumTimeUpdateUserId = @userId ), -- select * from timeLog timesheet as ( select * from ( select dayCode, requestId, userId, hours from timeLog ) as timeLogSource pivot ( sum( hours ) for dayCode in ( #_dayCodes# ) ) as timeLogPivot ), result as ( select t.requestId, t.userId, r.trackingId, r.description, p.expenditureTask, #_dayCodes# from timesheet t inner join tbl_SCRUM_Requests r on r.requestId = t.requestId left outer join tbl_SCRUM_Projects p on p.projectId = r.projectId ) #forJSON( 'result', attributes.datastore, 'expenditureTask' )#