Home SQL Server

SCSM DW Query - Backlog

Gerhard_GoossensGerhard_Goossens Customer Advanced IT Monkey ✭✭✭
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)
and ir.CreatedDate=(
   select max(CreatedDate) from IncidentDimvw IR2 with (nolock)
    where IR2.Id=IR.Id
and convert(date,IR2.CreatedDate)<=convert(date,IR3.CreatedDate)
)as [Backlog]
from IncidentDimvw IR3 with (nolock)
left join IncidentTierQueuesvw IRSupport on IR3.TierQueue_IncidentTierQueuesId=IRSupport.IncidentTierQueuesId
where CreatedDate between @StartDate and @EndDate
and IRSupport.IncidentTierQueuesValue in (@Support)
group by CONVERT(date, CreatedDate)
order by CONVERT(date, CreatedDate)

Best Answer


  • Gerhard_GoossensGerhard_Goossens Customer Advanced IT Monkey ✭✭✭
    Here are the results I get

  • Gerhard_GoossensGerhard_Goossens Customer Advanced IT Monkey ✭✭✭

    Will give it a shot.

    Thank you
  • Tom_HendricksTom_Hendricks Customer Super IT Monkey ✭✭✭✭✭
    It's always nice when someone else does the hard work and you just have to make small adjustments, right?  Thanks, @Brian_Wiest:)

    This is a bit different, but it might be useful nonetheless so I am going to share.

    I wanted a version of this query that I could utilize with PowerBI, which in my case means letting PowerBI do the filtering rather than passing parameters to the query.  I also wanted to be able to slice this by team or show everyone together, so I added the support group in.  Now that you are aware of the differences, here you go:

    WITH clean AS (<br>	SELECT<br>        CAST(CreatedDate AS date) AS cd<br>	,	CAST(ResolvedDate AS date) AS rd<br>	,	sg.IncidentTierQueuesValue AS sg<br>FROM<br>	DWDataMart.dbo.IncidentDimvw ir<br>LEFT JOIN<br>	DWDataMart.dbo.IncidentTierQueuesvw sg<br>ON	sg.IncidentTierQueuesId = ir.TierQueue_IncidentTierQueuesId<br>), cre AS (<br>	SELECT<br>        cd<br>	,	COUNT(*) AS ccnt<br>	,	sg AS csg<br>	FROM<br>		clean AS clean_2<br>	GROUP BY cd, sg<br>), res AS (<br>	SELECT<br>        rd<br>	,	COUNT(*) AS rcnt<br>	,	sg AS rsg<br>	FROM<br>		clean AS clean_1<br>	GROUP BY rd, sg<br>), dts AS (<br>	SELECT<br>		cd AS dt<br>	,	csg AS dsg<br>	FROM<br>		cre AS cre_2<br>	UNION<br>	SELECT<br>		rd<br>	,	rsg<br>	FROM<br>		res AS res_2<br>), together AS (<br>	SELECT<br>		dt<br>	,	dsg<br>	,  (<br>			SELECT<br>				ccnt<br>			FROM<br>				cre AS cre_1<br>			WHERE<br>				dts_1.dt = cd<br>			AND	csg = dsg<br>		) AS ccnt<br>	,	(<br>			SELECT<br>				rcnt<br>			FROM<br>				res AS res_1<br>			WHERE<br>				dts_1.dt = rd<br>			AND	rsg = dsg<br>		) AS rcnt<br>	,	(<br>			SELECT<br>				COUNT(*) AS Expr1<br>			FROM<br>				clean AS c<br>			WHERE<br>				(cd <= dts_1.dt)<br>			AND	(<br>					rd > dts_1.dt<br>				OR	rd IS NULL<br>				)<br>			and sg = dsg<br>		) AS ocnt<br>	FROM<br>		dts AS dts_1<br>	GROUP BY<br>		dt<br>	,	dsg<br>)<br>SELECT	TOP (100) PERCENT<br>	dt AS Day<br>,	dsg AS SupportGroup<br>,	ccnt AS Created<br>,	rcnt AS Resolved<br>,	ocnt AS Backlog<br>FROM<br>	together AS together_1<br>ORDER BY<br>	Day

    I am still vetting the results, so I am not yet confident that my changes have not caused double-counting, for example.  On first pass it looks good for IRs.  The backlog looks disproportionately ridiculous when I adapt this query for SR tickets, however, hence my suspicions.

    It was harder to get this to work with support groups (tiers...) than to work with the WHERE clause, so I think that will be easier to add back in using Brian's example.

    This query is going to return quite a bit more data than Brian's, by its very nature.  I put this into a view on my DB server to try to optimize it with indexes, etc.  Just be aware that it will run very slow at first, but adding Brian's params back in will shrink the result set and likely speed things up despite the extra scans.

    And yes, this is very easy to adapt for Service Requests once you have it running.

Sign In or Register to comment.