CODEWITHSUNDEEP
sqlt-sql
k1f1
k1f1

Reputation: 433

SQL Calculate Days between two dates in one table

I have a table dbo.Trans which contains an id called bd_id(varchar) and transfer_date(Datetime), also an identifier member_id pk is trns_id and is sequential

Duplicates of bd_id and member_id exist in the table.

transfer_date       |bd_id| member_id | trns_id
2008-01-01 00:00:00 | 432 | 111       | 1  
2008-01-03 00:00:00 | 123 | 111       | 2  
2008-01-08 00:00:00 | 128 | 111       | 3  
2008-02-04 00:00:00 | 123 | 432       | 4
.......

For each member_id, I want to get the amount of days between dates and for each bd_id

E.G., member 111 used 432 from 2008-01-01 until 2008-02-01 so return should be 2
Then next would be 5

I know the DATEDIFF() function exists but I am not sure how to get the difference when dates are in the same table.

Any help appreciated.

Upvotes: 4

Views: 23802

Answers (3)

Mikael Eriksson
Mikael Eriksson

Reputation: 139010

You could try something like this.

select T1.member_id,
       datediff(day, T1.transfer_date, T3.transfer_date) as DD
from YourTable as T1
  cross apply (select top 1 T2.transfer_date
               from YourTable as T2
               where T2.transfer_date > T1.transfer_date and
                     T2.member_id = T1.member_id
               order by T2.transfer_date) as T3

SE-Data

Upvotes: 13

Vahid Javaherifar
Vahid Javaherifar

Reputation: 155

You must select 1st and 2nd records that you want, then get their dates and get DATEDIFF of those two dates.

DATEDIFF(date1, date2);

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1271003

Your problem is getting the next member date.

Here is an example using a correlated subquery to get the next date:

select t.*, datediff(day, t.transfer_date, nextdate) as Days_Between
from (select t.*,
             (select min(transfer_date)
              from trans t2
              where t.bd_id = t2.bd_id and
                    t.member_id = t2.member_id and
                    t.transfer_date < t2.transfer_date
             ) as NextDate
      from trans t
     ) t

SQL Server 2012 has a function called lag() that makes this a bit easier to express.

Upvotes: 0

Related Questions