CODEWITHSUNDEEP
sql-server
user1429225
user1429225

Reputation: 11

How can I count the sum of gaps between date ranges

t_no.    eff_date         term_date
65803   2011-01-01        2011-06-30
65803   2011-09-01        2011-11-30
65804   2012-02-01        NULL
65805   2011-02-01        2011-06-30

I want sql code that calculate the gap between 2 different record with the same t_no. (e.g. 2 months for 65803).

Data could have one or more record for the same t_no.

Upvotes: 1

Views: 260

Answers (1)

Quassnoi
Quassnoi

Reputation: 425683

WITH    q AS
        (
        SELECT  *, ROW_NUMBER() OVER (PARTITION BY t_no ORDER BY term_date) rn
        FROM    mytable
        )
SELECT  qp.t_no, SUM(DATEDIFF(day, qp.term_date, qn.eff_date))
FROM    q qp
JOIN    q qn
ON      qn.t_no = qp.t_no
        AND qn.rn = qp.rn + 1
GROUP BY
        qp.t_no

Upvotes: 3

Related Questions