Incident backlog trend

Stephane_Bouillon

Does anybody have a query that shows the trend of open incidents for the last 60 days ? As backlog I consider the total number of incidents that were not in a closed state on any given day.

It is very interesting from a team performance standpoint to know if over time the backlog is decreasing, increasing, or remaining more or les stable.

If something is available out-of-the-box that's even better.

Stephane

Stephane_Bouillon

The same would be nice to have for service requests as well.

James_Johnson

If you're looking for a graphical representation you could take a look at the code I posted in https://community.cireson.com/discussion/5518/sample-code-for-dashboards-300-blog#latest

Otherwise here's the sql query we use for a line graph for past 3 months of incidents of priority greater than 3.

WITH Dates AS (
    SELECT
     EventDate = DATEADD(MONTH,-3,CONVERT(DATE,GETDATE()))
    UNION ALL SELECT
     EventDate = DATEADD(DAY, 1, EventDate)
    FROM
     Dates
    WHERE
     EventDate < CONVERT(DATE,GETDATE())
),
PriorityWI AS (
SELECT ResolvedDate,Created FROM workitem WHERE ClassId = 'A604B942-4C7B-2FB2-28DC-61DC6F465C68' AND PriorityId > 3
)
SELECT EventDate [Date], 
(
	SELECT COUNT(PriorityWI.Created) -  
	(
		SELECT COUNT(PriorityWI.Created) FROM PriorityWI WHERE CONVERT(DATE,PriorityWI.ResolvedDate) <= EventDate
	) FROM PriorityWI WHERE CONVERT(DATE,PriorityWI.created) <= EventDate
) AS [Priority_Backlog]
FROM Dates
group by EventDate

Gerhard_Goossens

Today is your lucky day. I created this from borrowed code that I found somewhere... :-)

This is for IRs and SRs, you can split it by removing the "UNION ALL"

There is also a token that I pass to the dashboard that you can remove or change as needed

WITH Dates AS (
    SELECT
        EventDate = DATEADD(MONTH, -1, CONVERT(DATE, GETDATE()))
    UNION ALL
    SELECT
        EventDate = DATEADD(DAY, 1, EventDate)
    FROM
        Dates
    WHERE
        EventDate < CONVERT(DATE, GETDATE())
)


SELECT  com.[Date],
		sum(com.Backlog) as [Backlog]


		from
(
SELECT
    EventDate [Date],
    (
        SELECT
            COUNT(wi.id) - (
                SELECT
                    COUNT(wi.id)
                FROM
                    workitem wi
					
    LEFT JOIN [Enumeration] enum ON enum.EnumerationID = wi.TierId 
    LEFT JOIN [DisplayString] dssupport ON dssupport.ElementID=enum.EnumerationID
                WHERE
                    CONVERT(DATE, wi.CompletedDate) <= EventDate
                    AND wi.ClassId = '04B69835-6343-4DE2-4B19-6BE08C612989'
					AND StatusId IN ('72B55E17-1C7D-B34C-53AE-F61F8732E425', 'A52FBC7D-0EE3-C630-F820-37EAE24D6E9B', '59393F48-D85F-FA6D-2EBE-DCFF395D7ED1', '05306BF5-A6B9-B5AD-326B-BA4E9724BF37') --Active, Pending
					AND dssupport.DisplayString in ('{{SuppGroup}}')
            )
        FROM
            workitem wi
			
    LEFT JOIN [Enumeration] enum ON enum.EnumerationID = wi.TierId 
    LEFT JOIN [DisplayString] dssupport ON dssupport.ElementID=enum.EnumerationID
        WHERE
            CONVERT(DATE, wi.created) <= EventDate
                    AND wi.ClassId = '04B69835-6343-4DE2-4B19-6BE08C612989'
					AND StatusId IN ('72B55E17-1C7D-B34C-53AE-F61F8732E425', 'A52FBC7D-0EE3-C630-F820-37EAE24D6E9B', '59393F48-D85F-FA6D-2EBE-DCFF395D7ED1', '05306BF5-A6B9-B5AD-326B-BA4E9724BF37') --Active, Pending
					AND dssupport.DisplayString in ('{{SuppGroup}}')
    ) as [Backlog]
FROM
    Dates
group by
    EventDate
---------------------------------------------
	UNION ALL
---------------------------------------------
	
SELECT
    EventDate [Date],
    (
        SELECT
            COUNT(wi.id) - (
                SELECT
                    COUNT(wi.id)
                FROM
                    workitem wi
					
    LEFT JOIN [Enumeration] enum ON enum.EnumerationID = wi.TierId 
    LEFT JOIN [DisplayString] dssupport ON dssupport.ElementID=enum.EnumerationID
                WHERE
                    CONVERT(DATE, wi.resolvedDate) <= EventDate
                    AND wi.ClassId = 'A604B942-4C7B-2FB2-28DC-61DC6F465C68'
					AND StatusId IN ('5E2D3932-CA6D-1515-7310-6F58584DF73E', 'B6679968-E84E-96FA-1FEC-8CD4AB39C3DE') --Active, Pending
					AND dssupport.DisplayString in ('{{SuppGroup}}')
            )
        FROM
            workitem wi
			
    LEFT JOIN [Enumeration] enum ON enum.EnumerationID = wi.TierId 
    LEFT JOIN [DisplayString] dssupport ON dssupport.ElementID=enum.EnumerationID
        WHERE
            CONVERT(DATE, wi.created) <= EventDate
            AND wi.ClassId = 'A604B942-4C7B-2FB2-28DC-61DC6F465C68'
			AND StatusId IN ('5E2D3932-CA6D-1515-7310-6F58584DF73E', 'B6679968-E84E-96FA-1FEC-8CD4AB39C3DE') --Active, Pending
			AND dssupport.DisplayString in ('{{SuppGroup}}')
    ) as [Backlog]




	FROM
    Dates
group by
    Dates.EventDate) Com


Group by com.Date
order by com.Date

Adam_Dzyacky

Nice one @Gerhard_Goossens !