Burn down - ticket report
Ryan_Kelley
Customer IT Monkey ✭
Using Cireson Analytics: I was looking to create a "burn down report" IE a variance between week to week of tickets that get closed.
Example(this is in excel as part of manual process in excel) 1) The top row would be active tickets of the week - minus the ones we closed during the week 2): Resolved will be resolved for the week 3) would be created for the week 4) (this is the tricky part) 4 - is how many tickets we closed week to week -- please let me know if you have questions on this!
What I have so far in SQL:
DECLARE
@vYear AS INT,
@vJan1Year DATETIME,
@vDec31Year DATETIME
/* Set the @vYear variable to the year you are analyzing */
SET
@vYear = year(getdate())
SET
@vJan1Year = DATEADD(yyyy, @vYear - 1900, 0)
SET
@vDec31Year = DATEADD(yyyy, @vYear - 1899, 0)
; WITH
cte_Fridays AS
(
SELECT
@vJan1Year AS DateOfYear
UNION ALL
SELECT
DATEADD(d, 1 ,cte_Fridays.DateOfYear)
FROM
cte_Fridays
WHERE
DATEADD(d,1,cte_Fridays.DateOfYear) < @vDec31Year
)
SELECT
cte_Fridays.DateOfYear,
COUNT(t.[WorkItemId])
FROM
cte_Fridays
LEFT JOIN
[CiresonAnalytics].[dbo].[SR_IR] AS t
ON
(t.[CompletedDate] IS NULL OR t.[CompletedDate] > cte_Fridays.DateOfYear)
AND
t.[Created] < cte_Fridays.DateOfYear
WHERE
DATENAME(dw, DATEADD(d, 0 ,cte_Fridays.DateOfYear)) = 'Friday'
GROUP BY
cte_Fridays.DateOfYear
ORDER BY
DateOfYear
OPTION
(MaxRecursion 366)
My issue is that I do not see that velocity the way I would need it and ran into my stumbling block. Thank you for any assistance!
Answers
Hi @Ryan_Kelley
I'm not sure I'm understanding the equations behind your example Excel dataset.
Can you provide the basic logic of how you calculate the Velocity and explain why the resolved and created numbers for a given week change as the weeks go on?
Thanks
Brett
Happy to try to explain, but I may need to get further explanation from those who created it.
The idea is if something is active on the week then it will be on the top row.
But if it gets closed any subsequent weeks then it will be recorded on that week.
IE I had SR000001 created on week 1 and therefore it will count as a 1 for active
Then if that ticket gets closed it will record that the ticket was completed but in another week.
Thus it will show the "burn rate" of how fast week close tickets.
I will obtain more info tomorrow--- In the meantime - hopefully this provides useful to someone but I hopefully created the first three rows of active completed and created and would like feedback if possible. Thank you!
Query:
select
DATENAME(MONTH, i.Created) AS Month,
CONVERT(VARCHAR(10),DATEADD(WEEK,DATEDIFF(WEEK,'19000101',i.Created),'19000101'),110) + ' to ' +
CONVERT(VARCHAR(10),DATEADD(DAY,-1,DATEADD(WEEK,1+DATEDIFF(WEEK,'19000101',i.Created),'19000101')),110) AS 'Weeks range',
Sum( case when i.status='In Progress' or i.status='Active' or i.status<>'Failed' or i.status='Submitted' then 1 else 0 end ) 'Active',
Sum( case when i.status='Completed' or i.status='Resolved' or i.status='Closed' then 1 else 0 end ) 'Resolved',
Sum( case when i.status='Submitted' then 1 else 0 end ) as 'Created'
from [CiresonAnalytics].[dbo].[SR_IR] i
where
YEAR(Created) = '2022'
and
i.status<>'Cancelled'
GROUP BY DATEPART(MONTH, i.Created), DATENAME(MONTH, i.Created),
CONVERT(VARCHAR(10),DATEADD(WEEK,DATEDIFF(WEEK,'19000101',i.Created),'19000101'),110) + ' to ' +
CONVERT(VARCHAR(10),DATEADD(DAY,-1,DATEADD(WEEK,1+DATEDIFF(WEEK,'19000101',i.Created),'19000101')),110)
ORDER BY DATEPART(MONTH, i.Created) ASC, [Weeks RANGE]
Thank you for helping! :)
I got more information on how the velocity is calculated:
Takes the top of the prior week end and minuses the active for the next week end--
IE the top row will eventually go to 0
The bottom row shows as when the tickets were resolved.
Hmmmm, I think I understand what you are trying to achieve here mathematically, but not from a business perspective. I'm not trying to pick apart your approach here, I'm just trying to get a better understanding of what you are trying to achieve and to see if I can assist in suggesting a better way to gather the same result.
Are you taking the velocity value as the lower the value the worse the service desk is performing? Or the lower the number the better they are performing?
And based on that result, what business decisions can you make to try to steer the value in the right direction?
Also, I'm not sure but, I think there is also an issue with the maths. the 17th of April is 9-34 and gives you -25. However the 3rd of April is 0-1 and gives you a result of 0, where it should be -1.... Not sure if this is an Excel issue or some other artifact.
With that said, I'm still trying to wrap my head around this approach.
Please let me know if I'm getting close here:
If this is correct, Does this show us what weeks were busier than others? How would this analytic deal with 40 SR's being raised at 4:49 on a Friday night?
How would it report or factor for things like illness or staffing levels?
Hello!
Thank you for answering and helping!
1) Velocity would be the worse the team is performing - IE
Top row will eventually go to 0 as the tickets get resolved.
If this is correct, Does this show us what weeks were busier than others? -- YES! and it will show how quickly the team is crushing out the tickets.
How would this analytic deal with 40 SR's being raised at 4:49 on a Friday night?- A week is Saturday to Friday - That means it will count for the given week
How would it report or factor for things like illness or staffing levels? -- It does not this report gives an overview of how quickly a team gets to and resolves tickets. If multiple members of a team are sick or if there are staffing issues then the report will show that fewer tickets are getting resolved and more and more are staying open for longer periods IE If a ticket is opened in January 3 and stays open till may then the number in active will stay as 1.
Thank you so much for helping!
OK. That all makes sense @Ryan_Kelley
I've asked a few of my colleagues to way in on this as I'm not as good at SQL as they are. :)
One comment I would make is that good service desk metrics should give management a clear understanding of what needs to be done to fix the situation. For example: If this burn down report had staffing levels that weighted the result then you could easily see if staffing was the issue.
Unfortunately the way this burn down result is shown there is no way to know what is causing the slow down. If this report works for what you need then don't let me sway your decision to use it. The thing to keep in mind is does this report answer these sorts of questions?
Hope this adds some perspective on reporting that might not have been considered while we wait on smart SQL people to take a look at this query. :)
@Brett_Moffett - Thank you for looking into this and for your input!
The goal of the report is for the first columns as presented as we usually did the metrics manually. We need some more metrics as time goes on and would like any feedback or pre-made sql queries of possible that may show more in depth look as to what our tickets are doing and who is doing them. :)
I personally am making department management reports so each dept will get a overview of what their dept. is doing and how fast they are dealing with tickets. How many tickets come from what category. Who deals with the most tickets ect.
Interestingly!
The questions you pose in 1-6 are helpful and we are starting to address this in other reports where we see how many tickets were closed by who ect. Currently we are still building service management metrics and finding our oldest tickets to close.
For us we still largely depend on managers to hold meeting and from them determine if everyone is doing their job. Most metrics are done manually. :D
For now on this burn down report - this will be more in general and then we will narrow it down by dept and see how fast a dept is getting to the tickets and if not fast at all then do more research as to why.( alot of our team members may not do many tickets but have more projects ect.
I appreciate your help and am looking forward to the SQL people and their insights :)
@Brett_Moffett - Good day!
Hoping that the smart SQL people know any way of solving this pickle!
Or otherwise hoping this question does not drop off :)
Thank you again for helping!
@Brett_Moffett
Just following up! :)
Thank you again for helping - I am still trying to solve the SQL myself