Date difference for same ID

Question

I ve got a data set similar to

    +----+------------+------------+------------+

    | ID |   Udate    | last_code  | Ddate      | 
    +----+------------+------------+------------+
    |  1 | 05/11/2018 | ACCEPTED   | 13/10/2018 |
    |  1 | 03/11/2018 | ATTEMPT    | 13/10/2018 | 
    |  1 | 01/11/2018 | INFO       | 13/10/2018 |
    |  1 | 22/10/2018 | ARRIVED    | 13/10/2018 | 
    |  1 | 15/10/2018 |   SENT     | 13/10/2018 | 
    +----+------------+------------+------------+

I m trying to get the date difference for each code on Udate, but for the first date I want to make datedifference between Udate and Ddate.

So I ve been trying:

DATEDIFF(DAY,LAG(Udate) OVER (PARTITION BY Shipment_Number ORDER BY Udate), Udate)

to get the difference between dates and it works so far, but I also need the first date difference between Udate and Ddate.

I was thinking about ISNULL()

Also, at the end I need an average of days between codes as well, usually they keep the same pattern. Sample output data:

    +----+------------+------------+------------+------------+

    | ID |   Udate    | last_code  | Ddate      | Difference |
    +----+------------+------------+------------+------------+
    |  1 | 05/11/2018 | ACCEPTED   | 13/10/2018 |     2      |
    |  1 | 03/11/2018 | ATTEMPT    | 13/10/2018 |     2      |
    |  1 | 01/11/2018 | INFO       | 13/10/2018 |     10     |
    |  1 | 22/10/2018 | ARRIVED    | 13/10/2018 |     7      |
    |  1 | 15/10/2018 |   SENT     | 13/10/2018 |     2      |
    +----+------------+------------+------------+------------+

Notice that when there is no previous code, the date diff is between Udate and Ddate.

Would appreciate any idea.

Thank you.

Answer 1

Well, ISNULL is the way to go here.
Since you also want the average difference, you can use a common table expression to get the difference, and query it to get the average:

First, Create and populate sample data (Please save us this step in your future questions)

-- This would not be needed if you've used ISO8601 for date strings (yyyy-mm-dd | yyyymmdd)
SET DATEFORMAT DMY; 

DECLARE @T AS TABLE
    (
    ID int,
    UDate date,
    last_code varchar(10),
    Ddate date
    ) ;

INSERT INTO @T (ID, Udate, last_code, Ddate) VALUES

(1, '05/11/2018', 'ACCEPTED', '13/10/2018'),
(1, '03/11/2018', 'ATTEMPT' , '13/10/2018'), 
(1, '01/11/2018', 'INFO'    , '13/10/2018'),
(1, '22/10/2018', 'ARRIVED' , '13/10/2018'), 
(1, '15/10/2018', 'SENT'    , '13/10/2018');

The cte:

WITH CTE AS
(
    SELECT  ID, 
            Udate, 
            last_code, 
            Ddate,
            DATEDIFF(
                DAY, 
                ISNULL(
                    LAG(Udate) OVER(PARTITION BY ID ORDER BY Udate), 
                    Ddate
                ), 
                UDate
            ) As Difference
    FROM @T
)

The query:

SELECT *, AVG(Difference) OVER(PARTITION BY ID) As AverageDifference
FROM CTE;

Results:

ID  Udate       last_code   Ddate       Difference  AverageDifference
1   15.10.2018  SENT        13.10.2018  2           4
1   22.10.2018  ARRIVED     13.10.2018  7           4
1   01.11.2018  INFO        13.10.2018  10          4
1   03.11.2018  ATTEMPT     13.10.2018  2           4
1   05.11.2018  ACCEPTED    13.10.2018  2           4