Home Analytics

Burn down - ticket report

Ryan_KelleyRyan_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

  • Brett_MoffettBrett_Moffett Cireson PACE Super IT Monkey ✭✭✭✭✭

    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

  • Ryan_KelleyRyan_Kelley Customer IT Monkey ✭
    edited July 2022

    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]

  • Ryan_KelleyRyan_Kelley Customer IT Monkey ✭

    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.


  • Brett_MoffettBrett_Moffett Cireson PACE Super IT Monkey ✭✭✭✭✭

    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:

    • On the week starting on Monday the 18th of April, there was 34 SR's that were created but not resolved during that week.
    • The next week (Monday the 24th of April) of the 34 from the previous week, 9 of them were still open.
    • This means that 25 SR's that should have been resolved the week of the 18th were completed the week after they were created.

    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?

  • Ryan_KelleyRyan_Kelley Customer IT Monkey ✭

    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.

    • On the week starting on Monday the 18th of April, there was 34 SR's that were created but not resolved during that week. -- YES!
    • The next week (Monday the 24th of April) of the 34 from the previous week, 9 of them were still open. -- YES!
    • This means that 25 SR's that should have been resolved the week of the 18th were completed the week after they were created. -- YES!

    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!

  • Brett_MoffettBrett_Moffett Cireson PACE Super IT Monkey ✭✭✭✭✭

    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?

    1. Is it low staff numbers?
    2. Is it lazy staff members?
    3. Is it that all tickets were logged late on a Friday night?
    4. Were the tickets closed then re-opened at a later date?
    5. Were the tickets closed to make the stats look better on a Friday night, then re-opened on Monday?
    6. Was the customer actually happy with the result or was the ticket just closed to make the stats look good?

    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. :)

  • Ryan_KelleyRyan_Kelley Customer IT Monkey ✭

    @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 :)

  • Ryan_KelleyRyan_Kelley Customer IT Monkey ✭

    @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!

  • Ryan_KelleyRyan_Kelley Customer IT Monkey ✭

    @Brett_Moffett

    Just following up! :)

    Thank you again for helping - I am still trying to solve the SQL myself

Sign In or Register to comment.