SQL To find difference between multiple rows

Question

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

Please try reformatting your question to better read. Man! That really hurts! — Ahmed, Jul 21 '09 at 12:31
Also, what do you mean by average days? We'll need an example of how you caclulate that for a given ID/Ref combination — Dave, Jul 21 '09 at 12:34
By "average days" you mean average number of days between two rows with id x, in date order? — Draemon, Jul 21 '09 at 12:35
@ Dave ID 1 ref A- has 10 days between first and second instance and 20 days between the second and third instance - so its 15. In all fairness just in this format would be great... i.e. ID Payref DaysBetween 1 A 10 1 A 20 — , Jul 21 '09 at 12:38
@Mark: its better to add your clarifactions to your question itself rather then leaving the details floundering in the comments. That way others can compare the real requirements in the question with the answers in order to vote correctly. — AnthonyWJones, Jul 21 '09 at 13:02

score 3 · Answer 1 · answered Jul 21 '09 at 12:37

Something like this... not really sure how this info will help you with anything though.... need more info as to what your trying to average the days for.

SELECT ID, REF, AVG(DATEPART(day, [Date]))
FROM dbo.Table1
GROUP BY ID, REF

Reference: AVG, DATEPART

Quassnoi · Accepted Answer · 2009-07-21T13:34:40.727

3

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

edited Jul 21 '09 at 13:34

answered Jul 21 '09 at 12:38

Quassnoi

413,100
91
616
614

@Quassnoi - thats great. I've amended the code to SELECT id, ref, DATEDIFF(day, MIN(date), MAX(date)) , COUNT(*) FROM mytable GROUP BY id, ref as in my example I'd need COUNT(*)-1 when COUNT(*)>2 and COUNT(*) when COUNT(*)=2. THanks to everyone for all the help – Jul 21 '09 at 13:12

Adriaan Stander · Answer 3 · 2009-07-21T13:07:03.470

Using sql server 2005 try this.

DECLARE @Table TABLE(
        ID INT,
        Date DATETIME,
        Ref VARCHAR(MAX)
)

INSERT INTO @Table (ID,Date,Ref) SELECT 1, '01 Sep 2008', 'A'
INSERT INTO @Table (ID,Date,Ref) SELECT 1, '11 Sep 2008', 'A'
INSERT INTO @Table (ID,Date,Ref) SELECT 1, '01 Oct 2008', 'A'
INSERT INTO @Table (ID,Date,Ref) SELECT 2, '01 Sep 2008', 'A'
INSERT INTO @Table (ID,Date,Ref) SELECT 2, '01 Oct 2008', 'A'
INSERT INTO @Table (ID,Date,Ref) SELECT 2, '01 Nov 2008', 'B'
INSERT INTO @Table (ID,Date,Ref) SELECT 2, '01 Dec 2008', 'B'


;WITH Ordered AS (
    SELECT  ID,
            Ref,
            Date,
            ROW_NUMBER() OVER (PARTITION BY ID, Ref  ORDER BY Date) SubNumber
    FROM    @Table t
)
SELECT  Ordered.ID,
        Ordered.Ref,
        AVG(DATEDIFF(dd, Ordered.Date, OrderedNext.Date)) AVG_Days
FROM    Ordered INNER JOIN
        Ordered OrderedNext ON  Ordered.ID = OrderedNext.ID
                            AND Ordered.Ref = OrderedNext.Ref
                            AND Ordered.SubNumber + 1 = OrderedNext.SubNumber
GROUP BY Ordered.ID,
        Ordered.Ref

Also have a look at it mathematically:

Let say

([X(1)-X(0)] + [X(2)-X(1)] + [X(3)-X(2)] + ... + [X(n-1)-X(n-2)] + [X(n)-X(n-1)]) / (n-1).

expand the top part as

-X(0) + X(1) - X(1) + X(2) - X(2) + X(3) - ... - X(n-2) + X(n-1) - X(n-1) + X(n)

whcih end up as -X(0) + X(n)

so we have [X(n) - X(0)] / (n - 1)

so take (MAX - MIN) / (Count - 1) for count > 1

SQL To find difference between multiple rows

3 Answers3