SQL Server 中两个连续日期之间的天数差异

Question

以下是公司表格，根据公司ID和行号获取两个连续日期之间的天数差异：

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

预期输出:

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

- user3014311

---

你使用的是哪个版本的SQL Server？ - Pawan Kumar

Answer 1

使用自连接即可简单地完成此操作，无需使用任何分析函数，因为 row_num 已经在表中可用。

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

这里有一个演示。

- John Woo

Answer 2

对于SQL 2012以下版本，请使用以下内容

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)

对于 SQL 2012+，请尝试以下解决方案 -

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

输出

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)

- Pawan Kumar

---

OP提到他正在使用SQL 2008、2005。 - Mahesh.K

我看过你关于 SQL 难题的文章，非常惊人和令人印象深刻。 - Mahesh.K

谢谢你，Mahesh。 - Pawan Kumar

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

- hkravitz