SCSM DW Query - Backlog

Gerhard_Goossens

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.IncidentTierQueuesIdwhere 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.IncidentTierQueuesIdwhere CreatedDate between @StartDate and @EndDateand IRSupport.IncidentTierQueuesValue in (@Support)group by CONVERT(date, CreatedDate)order by CONVERT(date, CreatedDate)

Brian_Wiest

With the help of my SQL engineer this will give you a base line. It runs against the datamart. You should be able to edit this to get your support group filter added to further drill down.

declare @sdt datetime
set @sdt =  '1 jan 2018'

declare @edt datetime
set @edt = '31 dec 2018'
;

with clean as (
    select
        cast(createddate as date) cd,        -- Get rid of the time part so comparisons work right
        cast(resolveddate as date) rd
    from incidentdimvw
    ),
cre as (
    select cd, count(*) ccnt        --  Count the created tickets by day
    from clean
    group by cd
    ),
res as (
    select rd, count(*) rcnt        -- Count the resolved tickets by day
    from clean
    group by rd
    ),
dts as (                                    -- Create the complete list of days that had either a create or a resolve on them
    select cd as dt
    from cre
    union
    select rd
    from res
    ),
together as (        -- Combine the two lists and...
    select dt,
        (select ccnt from cre where dts.dt = cre.cd) ccnt,
        (select rcnt from res where dts.dt = res.rd) rcnt,
        (select count(*)   -- ...look back through the tickets and find the ones that are still open on any given day
            from clean c
            where cd <= dts.dt and (rd > dts.dt or rd is null)
            ) ocnt
    from dts
    )
select *
from together
where dt between @sdt and @edt -- Limit the report to the date range of interest
order by dt

Gerhard_Goossens

Here are the results I get

Brian_Wiest

With the help of my SQL engineer this will give you a base line. It runs against the datamart. You should be able to edit this to get your support group filter added to further drill down.

declare @sdt datetime
set @sdt =  '1 jan 2018'

declare @edt datetime
set @edt = '31 dec 2018'
;

with clean as (
    select
        cast(createddate as date) cd,        -- Get rid of the time part so comparisons work right
        cast(resolveddate as date) rd
    from incidentdimvw
    ),
cre as (
    select cd, count(*) ccnt        --  Count the created tickets by day
    from clean
    group by cd
    ),
res as (
    select rd, count(*) rcnt        -- Count the resolved tickets by day
    from clean
    group by rd
    ),
dts as (                                    -- Create the complete list of days that had either a create or a resolve on them
    select cd as dt
    from cre
    union
    select rd
    from res
    ),
together as (        -- Combine the two lists and...
    select dt,
        (select ccnt from cre where dts.dt = cre.cd) ccnt,
        (select rcnt from res where dts.dt = res.rd) rcnt,
        (select count(*)   -- ...look back through the tickets and find the ones that are still open on any given day
            from clean c
            where cd <= dts.dt and (rd > dts.dt or rd is null)
            ) ocnt
    from dts
    )
select *
from together
where dt between @sdt and @edt -- Limit the report to the date range of interest
order by dt

Gerhard_Goossens

Awesome!!!!

Will give it a shot.

Thank you
G

Tom_Hendricks

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.