时间戳

首页 > 解决方案 > SQL Server - 根据 columndata 从 X 个月前获取值

sql-server - SQL Server - 根据 columndata 从 X 个月前获取值

问题描述

假设我有下表（数据完全是虚构的）：

ID | MonthDate | PersonID | Name | Status  | MonthsAgoSinceLastCheck
1  | 2017-12   | 900      | Jack | Ill     | -
2  | 2018-01   | 900      | Jack | Ill     | 1
3  | 2018-02   | 900      | Jack | Ill     | 2
4  | 2018-03   | 900      | Jack | Healthy | 1
5  | 2017-02   | 901      | Bill | Ill     | -
6  | 2017-03   | 901      | Bill | Ill     | 1
7  | 2017-05   | 901      | Bill | Healthy | 1

对于每条记录，我想查看该人自上次检查后 X 个月前的状态（列MonthsAgoSinceLastCheck）。注意MonthDate可以跳过几个月。

所以在这种情况下，结果将是

ID | MonthDate | PersonID | Name | Status  | MonthsAgoSinceLastCheck | PreviousSatus
1  | 2017-12   | 900      | Jack | Ill     | -                       | -
2  | 2018-01   | 900      | Jack | Ill     | 1                       | Ill
3  | 2018-02   | 900      | Jack | Ill     | 2                       | Ill
4  | 2018-03   | 900      | Jack | Healthy | 1                       | Ill
5  | 2017-02   | 901      | Bill | Healthy | -                       | -
6  | 2017-03   | 901      | Bill | Healthy | 1                       | Healthy 
7  | 2017-05   | 901      | Bill | Ill     | 2                       | Healthy

有什么建议/提示吗？我试图用 CTE 和自加入来做到这一点，但两者都失败了。

标签： sql-server

解决方案

与单独使用年份和月份相比，使用完整日期要容易得多。你应该做的第一件事是从你的年+月生成一个完整的日期。然后只需与上个月自行加入，具体取决于最后一次检查。

;WITH DataWithDates AS
(
    SELECT
        T.ID,
        MonthDate = CONVERT(DATE, T.MonthDate + '-01'),
        T.PersonID,
        T.Name,
        T.Status,
        T.MonthsAgoSinceLastCheck
    FROM
        YourTable AS T
)
SELECT
    D.ID,
    D.MonthDate,
    D.PersonID,
    D.Name,
    D.Status,
    D.MonthsAgoSinceLastCheck,
    PreviousStatus = N.Status
FROM
    DataWithDates AS D
    LEFT JOIN DataWithDates AS N ON
        D.PersonID = N.PersonID AND
        N.MonthDate = DATEADD(MONTH, -1 * D.MonthsAgoSinceLastCheck, D.MonthDate)

我假设您MonthDate的所有行都有值，否则转换将失败。我还假设您的-值MonthsAgoSinceLastCheck实际上是NULL.

推荐阅读