sqldifference-between-rows

SQL To find difference between multiple rows

I have a table containing multiple records for different transactions i.e.

ID  Date         REF
1   01/09/2008   A
1   11/09/2008   A
1   01/10/2008   A
2   01/09/2008   A
2   01/10/2008   A
2   01/11/2008   B
2   01/12/2008   B

and I'm looking to summarise the data so that I have the average days for each id and ref... i.e.

ID  Ref    Avg_Days
1   A      15
2   A      30
2   B      30

Thanks in advance if anyone can help

Solution

  • Average day difference is a SUM of differences divided by COUNT(*)

    SUM of differences is in fact difference between MIN and MAX:

    SELECT  id, ref, DATEDIFF(day, MIN(date), MAX(date)) / NULLIF(COUNT(*) - 1, 0)
FROM    mytable
GROUP BY
        id, ref