SQL 问题：获取组中每条记录的 Datediff（以经过的天数为单位）

Question

给定此表：

如何获取每组 ID_Number 的每个 status_date 之间的日期差异（以天为单位）？换句话说，我需要找到给定 ID_Number 的每个状态的已用天数。

需要了解的一些事项：

• 所有 ID_Number 都会有一个 receive_date，它应该是每个 ID_Number 的最早日期（但应用程序不强制执行）
• 对于每个 ID_Number，都会有一个带有相应的 status_date 的状态，该状态是给出 ID_Number 的日期特殊状态。
• 状态列并不总是每次都按相同的顺序（应用程序不强制）
• 所有 ID_Number 将有一个关闭日期，它应该是最新日期（但应用程序不强制）

示例输出： 因此，对于 ID_Number 2001，第一个日期 (received_date) 是 2009-05-02，您遇到的下一个日期的状态为“开放”，并且是 2009-05-02，因此经过的天数为 0。继续到遇到的下一个日期日期为 2009 年 5 月 10 日，状态为“投资”，从前一日期算起已过去天数为 8 天。遇到的下一个日期是 2009-07-11，从上一个日期算起已过去的天数是 62。

编辑添加：

是否可以将经过的天数最终作为此表/视图上的一列？ 我还忘了补充一点，这是 SQL Server 2000。

Answer 1

我的理解是，您需要相同 id 的第一个 status_date 和下一个 status_date 之间的差异，依此类推直到关闭日期。

这仅适用于 SQL 2005 及更高版本。

;with test as (
    select 
        key,
        id_number,
        status,
        received_date,
        status_date,
        closed_date,
        row_number() over (partition by id order by status_date, key ) as rownum
    from @test
    )
select 
    t1.key,
    t1.id_number,
    t1.status,
    t1.status_date,
    t1.received_date,
    t1.closed_date,
    datediff(d, case when t1.rownum = 1 
                then t1.received_date
                else    
                    case when t2.status_date is null 
                        then t1.closed_date 
                        else t2.status_date 
                    end
            end,
            t1.status_date
         ) as days
from test t1
left outer join test t2
on t1.id = t2.id
    and t2.rownum = t1.rownum - 1

该解决方案适用于 SQL 2000，但我不确定执行效果如何：

select *,
    datediff(d,
        case when prev_date is null
            then closed_date
            else prev_date
        end,
        status_date )
from ( 
    select *,
        isnull( ( select top 1 t2.status_date 
          from @test t2
          where t1.id_number = t2.id_number
            and t2.status_date < t1.status_date
          order by t2.status_date desc
          ),received_date) as prev_date 
    from @test t1
) a
order by id_number, status_date

注意：将 @Test 表替换为您的表名称。

Answer 2

一些示例输出确实会有所帮助，但这是对您的意思的猜测，假设您想要每个 ID_Number/Status 组合的信息：

select ID_Number, Status, EndDate - StartDate as DaysElapsed
from (
    select ID_Number, Status, min(coalesce(received_date, status_date)) as StartDate, max(coalesce(closed_date, status_date)) as EndDate
    from Table1
    group by ID_Number, Status
) a

Answer 3

棘手的一点是确定以前的状态并将其放在与当前状态相同的行上。如果Key和StatusDate之间存在相关性（即Key(x) > Key(y)总是意味着 StatusDate(x) >= StatusDate(y))。不幸的是，情况似乎并非如此。

PS：我假设 Key 是您桌子上的唯一标识符；你没有说过任何其他的话。

SELECT  Key,
        ID_Number, 
        (
        SELECT  TOP 1 Key
        FROM    StatusUpdates prev
        WHERE   (prev.ID_Number = cur.ID_Number)
            AND (   (prev.StatusDate < cur.StatusDate)
                OR  (   prev.StatusDate = cur.StatusDate
                    AND prev.Key < cur.Key
                    )
                )
        ORDER BY StatusDate, Key /*Consider index on (ID_Number, StatusDate, Key)*/
        ) PrevKey
FROM    StatusUpdates cur

一旦以此为基础，就可以轻松地从当前或之前的 StatusUpdate 推断出您需要的任何其他信息。例如

SELECT  c.*,
        p.Status AS PrevStatus,
        p.StatusDate AS PrevStatusDate,
        DATEDIFF(d, c.StatusDate, p.StatusDate) AS DaysElapsed
FROM    (
        SELECT  Key,
                ID_Number, 
                Status,
                SattusDate,
                (
                SELECT  TOP 1 Key
                FROM    StatusUpdates prev
                WHERE   (prev.ID_Number = cur.ID_Number)
                    AND (   (prev.StatusDate < cur.StatusDate)
                        OR  (   prev.StatusDate = cur.StatusDate
                            AND prev.Key < cur.Key
                            )
                        )
                ORDER BY StatusDate, Key
                ) PrevKey
        FROM    StatusUpdates cur
        ) c
        JOIN StatusUpdates p ON
            p.Key = c.PrevKey