declare @endDate datetime = ; declare @startDate datetime = dateadd( day, -#_daysInTimePeriod#, @endDate ); declare @startDate datetime = ; with timelog as ( select tl.scrumTimeUpdateUserId, m.firstName + ' ' + m.lastName developerName, tl.requestId, scrumTimeUpdateDate workDay, sum( cast(scrumTimeUpdateHour as decimal(18,2))) hoursLogged from tbl_SCRUM_Timelog tl inner join tbl_fspro_members m on m.fspro_userId = tl.scrumTimeUpdateUserId where tl.ownerId = and tl.ownerType = and tl.scrumTimeUpdateDate between @startDate and @endDate and tl.scrumTimeUpdateUserId = group by tl.scrumTimeUpdateUserId, m.firstName, m.lastName, tl.requestId, tl.scrumTimeUpdateDate ), projects as ( select distinct s.requestId, s.trackingID SCRUM, s.description, p.name projectName, f.selectionLabel as expenditureType, f2.selectionLabel as expenditureTask from tbl_SCRUM_Requests s with (nolock) inner join tbl_SCRUM_Projects p with (nolock) on p.OwnerID = s.OwnerID and p.OwnerType = s.OwnerType and p.projectId = s.projectid inner join tbl_SCRUM_FieldLookups f with (nolock) on f.OwnerID = s.OwnerID and f.OwnerType = s.OwnerType and f.ownerId = and f.ownerType = and f.fieldName = 'REQ_EXPENDTYPE' and f.selectionValue = p.expenditureType left outer join tbl_SCRUM_FieldLookups f2 with (nolock) on f2.OwnerID = s.OwnerID and f2.OwnerType = s.OwnerType and f2.ownerId = and f2.ownerType = and f2.fieldName IN ('REQ_CAPEXTASK','REQ_OPEXTASK') and f2.selectionValue = p.expenditureTask inner join tbl_SCRUM_Timelog tl with (nolock) on tl.ownerId = s.ownerId and tl.ownerType = s.ownerType and tl.scrumTimeUpdateDate between @startDate and @endDate and tl.requestid = s.requestid where s.ownerid = and s.ownerType = and ( s.AssignedTo = or tl.scrumTimeUpdateUserId = ) ), timesheet as ( select tl.scrumTimeUpdateUserId, s.requestId, tl.developerName, coalesce(projectName, '') projectName, s.expenditureType, s.expenditureTask, tl.workDay, sum( tl.hoursLogged ) timesheetHours from timelog tl inner join projects s on s.requestId = tl.requestId group by tl.scrumTimeUpdateUserId, s.requestid, tl.developerName, projectName, s.expenditureType, s.expenditureTask, tl.workDay union all select tl.scrumTimeUpdateUserId, r.requestId, tl.developerName, coalesce(projectName, '') projectName, 'SCRUM' expenditureType, r.trackingID + ' ' + r.description as expenditureTask, tl.workDay, sum( tl.hoursLogged ) timesheetHours from timelog tl left join projects s on s.requestId = tl.requestId inner join tbl_SCRUM_Requests r on r.requestId = tl.requestId where s.requestId is null group by tl.scrumTimeUpdateUserId, r.requestId, projectName, tl.developerName, r.trackingId, r.description, tl.workDay ) select expenditureTask, sum(workDay#val(i+1)#) as '#dateformat( dateadd("d",-val(_daysInTimePeriod-i),_endDate), "yyyy-mm-dd")#' , from ( select distinct expenditureTask, case when workDay = '#dateformat( dateadd("d",-val(_daysInTimePeriod-i),_endDate), "yyyy-mm-dd")#' then sum ( timesheetHours ) else 0 end as workDay#val(i+1)# , from timesheet group by expenditureTask, workDay ) as x group by expenditureTask order by expenditureTask desc for json auto, include_null_values