Calculate DATEDIFF with IDs

I'm an SQL beginner, so the other thread didn't really help me.

I have a table in my DB titled 'mission' which stores the IDs and dates of each mission that takes place. What I want to do is count the days between each entry and present that in another column.

I have found that the DATEDIFF function would be best to use for this.

id,name,campaign_id,real_date,hist_date,mission_status
1,"The Fall of France I",2,2013-12-08,"1940-05-11 11:00:00",2
2,"The Fall of France II",2,2013-12-15,"1940-05-15 15:30:00",2
3,"The Fall of France III",2,2013-12-22,"1940-05-21 15:30:00",2
4,"The Fall of France IV",2,2014-01-05,"1940-05-24 05:30:00",2
5,"The Fall of France V",2,2014-01-12,"1940-06-01 05:30:00",2
6,"The Fall of France VI",2,2014-01-19,"1940-06-12 13:10:00",2

I would like the table to list the mission's id in column 1, its hist_date in column 2 and the difference between each date entry in days in column 3. Ex.

 id,hist_date,diff
    1,"1940-05-11 11:00:00",0
    2,"1940-05-15 15:30:00",4

Solution

Try this (T-SQL). It works even if your Id column is not continuous which can occur, for example, if a record is deleted.

SELECT A.Id, A.hist_date, ISNULL(DATEDIFF(Y, Max(B.hist_date), A.hist_date), 0) AS diff 
    FROM Mission AS A
    LEFT JOIN Mission AS B ON B.Id<A.id
    GROUP BY A.Id, A.hist_date

Result

Id  hist_date               diff
1   1940-05-11 11:00:00.000 0
2   1940-05-15 15:30:00.000 4
3   1940-05-21 15:30:00.000 6
4   1940-05-24 05:30:00.000 3
5   1940-06-01 05:30:00.000 8
6   1940-06-12 13:10:00.000 11