Avarage Time between incidents

Transact-SQL
1

Hi,

I need to calculate the average time difference between incidents per person.
Can it be done with sql ?

For example, My table looks like this:

Name, Date

Yuval, 2020-01-01
Dor, 2020-01-29
Mika, 2020-04-20
Yuval, 2020-03-01
Dor, 2020-07-29
Mika, 2020-10-14
Yuval, 2020-07-01
Dor, 2020-04-29
Mika, 2020-06-20

The result should be like this:

Name, Average time between incidents(in days)
Yuval, 60
Dor,47
Mika, 32

Thanks!

2

To answer your actual q (and not the implied one):
Yes, it can be done with SQL.

Please post directly usable test data, meaning CREATE TABLE and INSERT statment(s).

1 Like
3

Also, your data doesn't match your results. This is what I calculate, so please provide more insight. DDL and sample data will help as well

|Person|IncidentDate|NextIncidentDate|DaysDiff|

|Dor|2020-01-29|2020-04-29|91|
|Dor|2020-04-29|2020-07-29|91|
|Dor|2020-07-29|NULL|0|
|Mika|2020-04-20|2020-06-20|61|
|Mika|2020-06-20|2020-10-14|116|
|Mika|2020-10-14|NULL|0|
|Yuval|2020-01-01|2020-03-01|60|
|Yuval|2020-03-01|2020-07-01|122|
|Yuval|2020-07-01|NULL|0|

4

I believe this will give you the avg days, as requested:

SELECT 
    Name,
    CASE WHEN COUNT(*) = 1 THEN 0 ELSE
    (DATEDIFF(DAY, MIN(Date), MAX(Date)) + 1) / 
        (COUNT(*) - 1) END AS [Average time between incidents(in days)]
FROM your_table_name_goes_here
GROUP BY Name
5

Here's what I was referring to as usable sample data:

CREATE TABLE #data (
    Name varchar(30) NOT NULL,
    Date date NOT NULL
    )

INSERT INTO #data VALUES
    ('Yuval', '2020-01-01'),
    ('Dor', '2020-01-29'),
    ('Mika', '2020-04-20'),
    ('Yuval', '2020-03-01'),
    ('Dor', '2020-07-29'),
    ('Mika', '2020-10-14'),
    ('Yuval', '2020-07-01'),
    ('Dor', '2020-04-29'),
    ('Mika', '2020-06-20')
1 Like
6

Thanks a lot Scott !
:slightly_smiling_face:
Very nice approach.
Took me a while to understand your logic..
But that clarified me that I can't use static date range, but rather an incident count.

Can you help me add to the query a condition where only the last X rows\incidents of each person will be calculated in this query ?

Thanks.

P.S - Got the note about the usable sample data

7 
DECLARE @number_of_incidents int 
SET @number_of_incidents = 3

;WITH cte_incidents AS (
    SELECT *, ROW_NUMBER() OVER(PARTITION BY Name ORDER BY Date DESC) AS incident_num
    FROM your_table_name
)
SELECT 
    Name,
    CASE WHEN COUNT(*) = 1 THEN 0 ELSE
    (DATEDIFF(DAY, MIN(Date), MAX(Date)) + 1) / 
        (COUNT(*) - 1) END AS [Average time between incidents(in days)]
FROM cte_incidents
WHERE incident_num BETWEEN 1 AND @number_of_incidents
GROUP BY Name
1 Like
8

Amazing.
Helped me so much.
Really appreciate it!
Thanks.