SCSM DW Query - Backlog

Gerhard_GoossensGerhard_Goossens Customer Adept 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

Answers

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

  • Gerhard_GoossensGerhard_Goossens Customer Adept IT Monkey ✭✭
    Awesome!!!!

    Will give it a shot.

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