SQL To find the difference between multiple lines

Question

I have a table containing several 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 going to summarize the data so that I have average days for each identifier and ref ... that is.

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

Thanks in advance if anyone can help

Answer 1

Average daily difference is the SUMdifference divided byCOUNT(*)

SUMThe differences are actually the difference between MINand MAX:

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

Answer 2

Something like this ... not quite sure how this information will help you with anything, though .... you need more information about what you are trying to average for days.

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

Reference: AVG , DATEPART

Answer 3

SQL Server 2005, .

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

:

,

([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 as

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

which end as -X (0) + X (n)

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

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