调整开始和结束日期sql [英] Adjusting start and end dates sql

问题描述

给定 MS SQL Server 2016 中的数据集

Given a data set in MS SQL Server 2016

StoreID PurchaseID ShopID LocationID Starttime          Endtime
1020    20200102    9856  0010    2020-01-08 09:08:53  2020-01-08 09:11:52
1021    20200102    9856  0020    2020-01-08 09:09:48  2020-01-08 09:11:52

这里的 StoreID 是主键.我正在寻找一个查询，它将第一条记录结束时间的值更改为下一个第二条记录的开始时间中存在的值.准确地说，我需要查找发生在同一天的 PurchaseID & 记录.shopkeeperID 组合，其中位置 id 不同，然后抓取后面记录的开始时间并更新前一行结束时间中的值.

Here the StoreID is primary key. I'm looking for a query that will change the value of the first record end time to the value present in the starttime of next second record. To be precise I need to look for records that happened on same day for PurchaseID & shopkeeperID combination where the location id is different for both and then grab the starttime of later record and update the value in the prior row endtime.

注意:这里我只给出了两个样本大小，但在我的数据集中，我有超过 2 个以上场景.

Note: Here I gave sample size of just two but in my dataset I have more than 2 with above scenarios.

我的结果集应该是:

StoreID PurchaseID  ShopkID LocationID Starttime         Endtime
1020    20200102    9856    0010  2020-01-08 09:08:53  2020-01-08 09:09:48
1021    20200102    9856    0020  2020-01-08 09:09:48  2020-01-08 09:11:52

推荐答案

一种方法与此类似，尽管子选择的性能不如 SET SQL:

One approach would be something close to this, though a sub-select won't perform as well as SET SQL:

UPDATE 
    T
SET
    T.purchaseendtime = 
        (
            SELECT TOP 1 
                purchasestarttime 
            FROM 
                TABLE_NAME T2
            WHERE 
                T2.PurchaseStartTime >  T.PurchaseStartTime
                AND T2.PurchaseID = T.PurchaseID
                AND T2.ShopkeeperID = T.ShopkeeperID
                AND T2.LocationID <> T.LocationID
                AND Convert(date, T2.PurchaseStartTime) = Convert(date, T.PurchaseStartTime)
            ORDER BY
                T2.PurchaseStarttime
        )
FROM 
    TABLE_NAME T

这篇关于调整开始和结束日期sql的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！