J.Mack J.Mack - 9 months ago 25
SQL Question

SQL query that contains a function counting other Concurrent and Active tickets as new column

Hi I'd like to query a SQL dbo to get how many "tickets" were open(post-started and pre-resolved) within a 20 minute window of when the current rows "ticket" was created.

Goal Table (where 4 items were active from 8:50 to 9:10):

ID CreateDate ConcurrentItems
123 12/1/2016 09:00 4


Something along the lines of:

SELECT Ticket_ID as ID, Created_Date as CreateDate,
Count(
WHERE
WorkItemType = 'Work Request' AND
IncidentStartTime <= (Created_Date minus 10 minutes) AND
IncidentResolvedTime > (Created_Date plus 10 minutes)
)as ConcurrentItems

FROM CurrentView


Where the Created_Date in the COUNT would be same as in the SELECT

Answer Source

I would suggest outer apply:

SELECT Ticket_ID as ID, Created_Date as CreateDate, x.ConcurrentItems
FROM CurrentView v OUTER APPLY
     (SELECT COUNT(*) as ConcurrentItems
      FROM CurrentView v2
      WHERE v2.WorkItemType = 'Work Request' AND
            v2.IncidentStartTime <= DATEADD(minute, -10, v.Created_Date) AND 
            v2.IncidentResolvedTime > DATEADD(minute, 10, Created_Date)
    ) x;