CODEWITHSUNDEEP
mysqlsql
Ayn76
Ayn76

Reputation: 31

SQL Query on finding difference of dates between current and previous row

I am trying to find difference of 2 consecutive dates. Firstly, I sort the dates in descending order, and then, I take the difference of every 2 consecutive row.

A sample is like thisHere

Can someone please help me out?

Upvotes: 0

Views: 2712

Answers (4)

Gordon Linoff
Gordon Linoff

Reputation: 1270463

Your data is rather awkward. You seem to suggest that it has an ordering, but there are exact duplicates. Let me assume that you have another column that correctly specifies the ordering, such as an "id" or "time" column. If so:

select t.*,
       datediff(date, lag(date) over (partition by group order by date, <ordering col>)) as num_days
from t;

You can take your chances without the ordering column, but using window functions with order by when there are ties can lead to problems in some cases.

Upvotes: 0

Siva Koteswara Rao
Siva Koteswara Rao

Reputation: 129

SELECT id, group, date, (DAY (date) -DAY(LAG(date) ) ) AS 'daysdiff' FROM table

Upvotes: 0

FlyingSpiderMonkey
FlyingSpiderMonkey

Reputation: 27

You can also use LAG function

select id,Group,Date,
datediff(day,lag(date,1) over (partition by Date order by Date asc), Date)
as Difference_2_days
from yourtable;

Upvotes: 0

Bence Gajd&#225;n
Bence Gajd&#225;n

Reputation: 612

Something like this should do the trick:

set @PrevDate := (Date from table order by Date asc limit 1);

select Date, datediff(Date, @PrevDate) as DateDiff, 
    @PrevDate := Date as Increment
from table
order by Date asc;

You can use a variable to store the previous row's value, in this case @PrevDate is the variable which is set initially to the first Date value of the first row, with the same order as the main query. The @PrevDate is needs to be reset with the current row's value, after the DateDiff calculation.

Upvotes: 2

Related Questions