时间戳

首页 > 解决方案 > 检索给定用户开始和结束期间日期的最新更改

tsql - 检索给定用户开始和结束期间日期的最新更改

问题描述

我真的很感激这个查询的任何帮助。

我有 2 张桌子：

以下是表格脚本和示例数据。

CREATE TABLE Audit (
    AID INTEGER NOT NULL PRIMARY KEY
    ,PropertyID INTEGER NOT NULL
    ,StatusID INTEGER NOT NULL
    ,LastChangedBy VARCHAR(20) NOT NULL
    ,LastChanged VARCHAR(23) NOT NULL
    )

INSERT INTO Audit (
    AID
    ,PropertyID
    ,StatusID
    ,LastChangedBy
    ,LastChanged
    )
VALUES ( 
    44363
    ,65532
    ,2
    ,'Agent009'
    ,'2021-02-18 12:17:17.280'
    );

INSERT INTO Audit (
    AID
    ,PropertyID
    ,StatusID
    ,LastChangedBy
    ,LastChanged
    )
VALUES (
    44362
    ,65531
    ,7
    ,'Agent009'
    ,'2021-02-18 12:17:17.280'
    );

INSERT INTO Audit (
    AID
    ,PropertyID
    ,StatusID
    ,LastChangedBy
    ,LastChanged
    )
VALUES (
    44361
    ,65530
    ,5
    ,'Agent007'
    ,'2021-02-28 11:10:43.587'
    );

INSERT INTO Audit (
    AID
    ,PropertyID
    ,StatusID
    ,LastChangedBy
    ,LastChanged
    )
VALUES (
    44360
    ,65530
    ,3
    ,'Agent009'
    ,'2021-02-18 11:37:43.390'
    );

INSERT INTO Audit (
    AID
    ,PropertyID
    ,StatusID
    ,LastChangedBy
    ,LastChanged
    )
VALUES (
    44359
    ,65530
    ,3
    ,'Agent009'
    ,'2021-02-18 11:37:29.967'
    );

INSERT INTO Audit (
    AID
    ,PropertyID
    ,StatusID
    ,LastChangedBy
    ,LastChanged
    )
VALUES (
    44358
    ,65531
    ,2
    ,'Agent009'
    ,'2021-02-18 11:05:07.527'
    );

CREATE TABLE PStatus (
     ID INTEGER NOT NULL PRIMARY KEY
    ,StatusName VARCHAR(20) NOT NULL
    ,LastChangedBy VARCHAR(20) NOT NULL
    ,LastChanged VARCHAR(23) NOT NULL
    );

INSERT INTO PStatus (
    ID
    ,StatusName
    ,LastChangedBy
    ,LastChanged
    )
VALUES (
    1
    ,'REJECTED'
    ,'dbo'
    ,'2013-05-28 17:02:42.977'
    );

INSERT INTO PStatus (
    ID
    ,StatusName
    ,LastChangedBy
    ,LastChanged
    )
VALUES (
    2
    ,'NEW PROP'
    ,'dbo'
    ,'2013-05-28 17:02:42.977'
    );

INSERT INTO PStatus (
    ID
    ,StatusName
    ,LastChangedBy
    ,LastChanged
    )
VALUES (
    3
    ,'ACTIVE PROP'
    ,'dbo'
    ,'2013-10-15 12:41:14.280'
    );

INSERT INTO PStatus (
    ID
    ,StatusName
    ,LastChangedBy
    ,LastChanged
    )
VALUES (
    4
    ,'MONITOR'
    ,'dbo'
    ,'2013-10-15 12:41:14.280'
    );

INSERT INTO PStatus (
    ID
    ,StatusName
    ,LastChangedBy
    ,LastChanged
    )
VALUES (
    5
    ,'DEAl AGR'
    ,'dbo'
    ,'2013-10-15 12:41:14.280'
    );

INSERT INTO PStatus (
    ID
    ,StatusName
    ,LastChangedBy
    ,LastChanged
    )
VALUES (
    6
    ,'CONTRACTS EXCH'
    ,'dbo'
    ,'2013-10-15 12:41:14.280'
    );

INSERT INTO PStatus (
    ID
    ,StatusName
    ,LastChangedBy
    ,LastChanged
    )
VALUES (
    7
    ,'COMPLETED'
    ,'dbo'
    ,'2013-10-15 12:41:14.280'
    );

INSERT INTO PStatus (
    ID
    ,StatusName
    ,LastChangedBy
    ,LastChanged
    )
VALUES (
    8
    ,'ABORTED'
    ,'dbo'
    ,'2013-10-15 12:41:14.280'
    );

DB Fiddle 上的演示：创建表和输出

这就是我想要达到的目标。

最终输出：

+---------+---------------+----------------+-----------+-----------------------------+
| PRD Ref | Latest_Status | Opening_Status | lastchangedby |        lastchanged      |
+---------+---------------+----------------+---------------+-------------------------+
|   65530 | DEA AGR       | ACTIVE PROP    | Agent007      | 2021-02-28 11:10:43.587 |
+---------+---------------+----------------+---------------+-------------------------+
|   65531 | COMPLETED     | NEW PROP       | Agent009      | 2021-02-18 12:17:17.280 |
+---------+---------------+----------------+---------------+-------------------------+
|   65532 | NEW PROP      | *null*         | Agent009      | 2021-02-18 12:17:17.280 |
+---------+---------------+----------------+---------------+-------------------------+

这是我尝试过的。

select pa.propertyid as [PRD Ref], 
    max(case when pa.rn_desc = 1 then s.statusname     end) last_status,
    max(case when pa.rn_asc  = 1 then s.statusname     end) opening_status,
    max(case when pa.rn_desc = 1 then pa.lastchangedby end) lastchangedby,
    max(case when pa.rn_desc = 1 then pa.lastchanged   end) lastchanged
from (
    select pa.*, 
        row_number() over(partition by propertyid order by lastchanged) rn_asc,
        row_number() over(partition by propertyid order by lastchanged desc) rn_desc
    from audit pa
) pa
inner join pstatus s on s.id = pa.statusid
where 1 in (rn_asc, rn_desc)
and pa.LastChanged BETWEEN '2021-01-20' AND '2021-02-20'
group by pa.propertyid

+---------+---------------+----------------+-----------+-----------------------------+
| PRD Ref | Latest_Status | Opening_Status | lastchangedby |        lastchanged      |
+---------+---------------+----------------+-----------+-----------------------------+
|   65530 | *null  *      | ACTIVE PROP    | *null*        | *null*                  |
+---------+---------------+----------------+-----------+-----------------------------+
|   65531 | COMPLETED     | NEW PROP       | Agent009      | 2021-02-18 12:17:17.280 |
+---------+---------------+----------------+-----------+-----------------------------+
|   65532 | NEW PROP      | *null*         | Agent009      | 2021-02-18 12:17:17.280 |
+---------+---------------+----------------+-----------+-----------------------------+

标签： tsql

解决方案

请检查以下 T-SQL 代码是否对您有帮助。

    DECLARE @StartDate DATETIME = '2021-02-15 00:00:00.000'
    DECLARE @EndDate DATETIME = '2021-02-28 23:30:00.000'

    ;WITH CTE_Audit
    AS
    (
        SELECT pa.PropertyID, COUNT(pa.PropertyID) AS TotalAuditRowCnt, Max(pa.LastChanged) as LastChanged
        FROM 
            [Audit](NOLOCK) pa
        
        GROUP BY pa.PropertyID      

    ),
    CTE_SecondMostRecent
    AS
    (
        SELECT os.PropertyID, os.opening_status
        FROM
        (
            SELECT pa.PropertyID, ps.StatusName as opening_status,  
            RANK() OVER(PARTITION BY pa.PropertyID ORDER BY pa.LastChanged DESC) rnk
            FROM 
                [Audit](NOLOCK) pa
                INNER JOIN  PStatus(NOLOCK) ps on pa.StatusID = ps.ID
            WHERE 
                pa.LastChanged BETWEEN @StartDate AND @EndDate

        )os 
        Where os.rnk = 2 /* Get Second Most Recent Records */
    ),
    CTE_FirstMostRecent
    AS
    (
        SELECT ls.PropertyID, ls.last_status, ls.LastChangedBy, ls.LastChanged
        FROM
        (
            SELECT pa.PropertyID, ps.StatusName as last_status, pa.lastchangedby, pa.lastchanged,  
            RANK() OVER(PARTITION BY pa.PropertyID ORDER BY pa.LastChanged DESC) rnk
            FROM 
                [Audit](NOLOCK) pa
                INNER JOIN  PStatus(NOLOCK) ps on pa.StatusID = ps.ID
            WHERE 
                pa.LastChanged BETWEEN @StartDate AND @EndDate

        )ls 
        Where ls.rnk = 1 /* Get First Most Recent Records */
    )

    SELECT 
          a.propertyid as [PRD Ref]
        , c.last_status as [Latest_Status]  
        , CASE WHEN a.TotalAuditRowCnt > 1 THEN b.opening_status ELSE '*null*' END as [Opening_Status]  
        , c.lastchangedby as [lastchangedby]
        , c.lastchanged as [lastchanged] 
    FROM 
        CTE_Audit a     
        LEFT JOIN CTE_SecondMostRecent b ON a.PropertyID = b.PropertyID     
        LEFT JOIN CTE_FirstMostRecent c ON a.PropertyID = c.PropertyID
    ORDER BY 
        a.LastChanged DESC

推荐阅读