CODEWITHSUNDEEP
sql-serversql-server-2014
Jericho
Jericho

Reputation: 213

MS SQL 2014 - Count of repeating ocurrences over range of dates

I have a table that tracks the Datetime of Incident_IDs created for specific Device_IDs and I am trying to find a way to track chronic issues over a range of dates. The definition of chronic issue is any Device_ID that had 3 or more Incident_IDs created in the past 5 days. I need to be able to search over a range of different dates (mostly monthly).

Given table:

IF OBJECT_ID('tempdb.dbo.#temp') IS NOT NULL
DROP TABLE #temp

CREATE TABLE #temp
    (Device_ID INT,
    Incident_ID INT,
    Incident_Datetime DATETIME)

INSERT INTO #temp
VALUES
    (2,1001,'2016-02-01'),
    (3,1002,'2016-02-02'),
    (2,1003,'2016-02-09'),
    (2,1004,'2016-02-10'),
    (5,1005,'2016-02-12'),
    (2,1006,'2016-02-13'),
    (5,1007,'2016-02-14'),
    (5,1008,'2016-02-15'),
    (3,1009,'2016-02-18'),
    (3,1010,'2016-02-19'),
    (3,1011,'2016-02-20'),
    (5,1012,'2016-02-21'),
    (3,1013,'2016-03-18'),
    (3,1014,'2016-03-19'),
    (3,1015,'2016-03-20');

The desired result for chronic issues for 02-2016 is:

Device_ID   Incident_ID Incident_Datetime
2           1003            2/9/16 0:00
2           1004            2/10/16 0:00
2           1006            2/13/16 0:00
3           1009            2/18/16 0:00
3           1010            2/19/16 0:00
3           1011            2/20/16 0:00
5           1005            2/12/16 0:00
5           1007            2/14/16 0:00
5           1008            2/15/16 0:00

I have tried the following query which shows me the ascending count of incidents and allows me to find those device_ids that have had chronic issues but I'm having a hard time isolating all the incidents that make up the chronic issue while excluding those outliers that occurred outside the 3 day range.

SELECT c.Device_ID, c.Incident_ID, c.Incident_Datetime,
    (SELECT COUNT(*)
    FROM #temp AS t
    WHERE
        c.Device_ID = t.Device_ID
        AND
        t.Incident_Datetime BETWEEN DATEADD(DAY,-5,c.Incident_Datetime) AND c.Incident_Datetime) AS Incident_Count
FROM #temp AS c
WHERE
    c.Incident_Datetime >= '2016-02-01'
    AND
    c.Incident_Datetime < '2016-03-01'
ORDER BY 
    Device_ID, Incident_Datetime

Upvotes: 3

Views: 82

Answers (3)

gordy
gordy

Reputation: 9796

Here's a way to derive a running incidents within n days total:

with
incidents as (
    select * from #temp cross apply (
    select incident_datetime, 1 union all
    select incident_datetime + 5, -1) x(dt, delta)),
rolling as (
    select *, incidents_in_range = sum(delta)
        over (partition by device_id order by dt)
    from incidents)

select t.* from #temp t join rolling r
    on r.device_id=t.device_id
    and t.incident_datetime between r.incident_datetime - 5 and r.incident_datetime
where r.incidents_in_range >= 3

..basically find the points at which "3 incidents in 5 days" was reached, and then join back to include the incidents within 5 days.

Upvotes: 0

Boyd P
Boyd P

Reputation: 434

This is probably not quite as nice as Jake's answer, but here's an alternative solution that might work:

        WITH cte AS 
        (
          SELECT tmp.Device_ID, tmp.Incident_Datetime FROM #temp AS tmp
          CROSS APPLY
          ( 
            SELECT Device_ID 
            FROM #temp AS t 
            WHERE tmp.Device_ID = t.Device_ID AND t.Incident_Datetime BETWEEN DATEADD(d,-5,tmp.Incident_Datetime) AND tmp.Incident_Datetime
            GROUP BY Device_ID HAVING COUNT(Incident_ID) >= 3 
          ) p
          WHERE tmp.Incident_Datetime BETWEEN '02-01-2016' AND '03-01-2016'
        )        

        SELECT f.*
        FROM #temp f
        INNER JOIN cte
        ON f.Device_ID = cte.Device_ID
        WHERE f.Incident_Datetime BETWEEN DATEADD(d,-5,cte.Incident_Datetime) AND cte.Incident_Datetime 
        GROUP BY f.Device_ID, f.Incident_ID, f.Incident_Datetime
        ORDER BY f.Device_ID, f.Incident_Datetime

Upvotes: 1

Jake
Jake

Reputation: 574

How about this...

DECLARE @StartDate datetime, @EndDate datetime
SET @StartDate='2016-02-01'
SET @EndDate='2016-03-01'

SELECT c.Device_ID, c.Incident_ID, c.Incident_DateTime FROM #temp c
INNER JOIN (SELECT t.Device_ID, Count(*) FROM #temp
    WHERE t.Incident_DateTime BETWEEN DATEADD(dd, -3, c.Incident_DateTime) AND DATEADD(dd, +3, c.Incident_DateTime)
    GROUP BY t.Device_ID
    HAVING Count(*) > 2)) t ON c.Device_ID = t.Device_ID
AND c.Incident_DateTime BETWEEN @StartDate AND @EndDate
ORDER BY 
c.Device_ID, c.Incident_Datetime

Upvotes: 0

Related Questions