Difference between two consecutive days in days in sql server

Question

Below is the company table, I need to get the difference between two consecutive dates in a few days based on companyId and rownum

company_id  date    row_num
101         2017-01-12              1
101         2017-02-22              2
118         2017-03-23              1
119         2017-04-18              1
123         2017-01-12              1
123         2017-01-15              2
123         2017-01-22              3
501         2017-01-30              1
501         2017-02-02              2

Expected yield:

company_id  date    days
101         2017-01-12              0
101         2017-02-22              41
118         2017-03-23              0
119         2017-04-18              0
123         2017-01-12              0
123         2017-01-15              3
123         2017-01-22              7
501         2017-01-30              0
501         2017-02-02              3

Answer 1

This can simply be done using self-join without using any analytic function, as it is row_numalready available in the table.

SELECT  a.company_id, 
        a.billing_failure_date, 
        a.row_num,
        ISNULL(DATEDIFF (d, b.billing_failure_date, a.billing_failure_date), 0) AS Days
FROM    TableName a
        LEFT JOIN TableName b
            ON a.company_id = b.company_id
                AND a.row_num = b.row_num + 1

Here is the Demo .

Answer 2

FOR BELOW SQL 2012 use this

SELECT * , ISNULL(DATEDIFF( DAY , 
            ( SELECT TOP 1 billing_failure_date FROM companyTable b WHERE a.company_id = b.company_id
                AND a.billing_failure_date > b.billing_failure_date 
            ORDER BY  billing_failure_date DESC ) ,billing_failure_date),0) Days
FROM companyTable a

OUTPUT

company_id  billing_failure_date row_num     Days
----------- -------------------- ----------- -----------
101         2017-01-12           1           0
101         2017-02-22           2           41
118         2017-03-23           1           0
119         2017-04-18           1           0
123         2017-01-12           1           0
123         2017-01-15           2           3
123         2017-01-22           3           7
501         2017-01-30           1           0
501         2017-02-02           2           3

(9 rows affected)

FOR SQL 2012+ Try this solution -

SELECT * , 
DATEDIFF( DAY , 
ISNULL(LAG(billing_failure_date) OVER (PARTITION BY company_id ORDER BY billing_failure_date ASC),billing_failure_date)
,billing_failure_date) Days
FROM companyTable

OUTPUT

company_id  billing_failure_date row_num     Days
----------- -------------------- ----------- -----------
101         2017-01-12           1           0
101         2017-02-22           2           41
118         2017-03-23           1           0
119         2017-04-18           1           0
123         2017-01-12           1           0
123         2017-01-15           2           3
123         2017-01-22           3           7
501         2017-01-30           1           0
501         2017-02-02           2           3

(9 rows affected)

Answer 3

SQL 2008 :

    ; WITH T (Company_Id, Billing_Failure_Date , RowNum )
    AS
    (
        SELECT 101, '2017-01-12', 1 
        UNION ALL 
        SELECT 101, '2017-02-22', 2
        UNION ALL 
        SELECT 118, '2017-03-23', 1
        UNION ALL 
        SELECT 119, '2017-04-18', 1
        UNION ALL 
        SELECT 123, '2017-01-12', 1
        UNION ALL 
        SELECT 123, '2017-01-15', 2
        UNION ALL 
        SELECT 123, '2017-01-22', 3
        UNION ALL 
        SELECT 501, '2017-01-30', 1
        UNION ALL 
        SELECT 501, '2017-02-02', 2
    )

    , W AS 
    (
    SELECT * , (SELECT MAX(Ottr.Billing_Failure_Date) FROM T Ottr WHERE Ottr.Company_Id = Innr.Company_Id AND Ottr.RowNum < Innr.RowNum AND Ottr.Billing_Failure_Date < Innr.Billing_Failure_Date) Prev_Billing_Failure_Date
    FROM T Innr
    )
    SELECT Company_Id, Billing_Failure_Date , RowNum , ISNULL(DATEDIFF(DAY,Prev_Billing_Failure_Date, Billing_Failure_Date),0) [Days]
    FROM W