Good day everyone I need to create a report to show the created, resolved and backlog per day for the past week.
I am having a difficult time to figure out the backlog part.
The backlog must show all the incidents that were active at the end of that day.
I have this so far, but the figures are not the same as the ones manually recorded for the day and im not sure what else to try....
Select (CONVERT(date, CreatedDate)),
count(CreatedDate) as [Created],
count(IR3.ResolvedDate) as [Resolved],
(Select count(ir.CreatedDate)from IncidentDimvw IR with(nolock)
join IncidentTierQueuesvw IRSupport on IR.TierQueue_IncidentTierQueuesId=IRSupport.IncidentTierQueuesId
where ir.Status_IncidentStatusId in(2,5)
and IRSupport.IncidentTierQueuesValue in(@Support)
select max(CreatedDate) from IncidentDimvw IR2 with (nolock)
from IncidentDimvw IR3 with (nolock)
left join IncidentTierQueuesvw IRSupport on IR3.TierQueue_IncidentTierQueuesId=IRSupport.IncidentTierQueuesId
and IRSupport.IncidentTierQueuesValue in (@Support)
group by CONVERT(date, CreatedDate)
order by CONVERT(date, CreatedDate)