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

问题描述

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
 1022    20200102    9856  0030    2020-01-09 09:08:53  2020-01-09 09:12:52 
 1023    20200102    9856  0040    2020-01-10 09:09:48  2020-01-10 09:13: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.

我希望此更改仅更新为仅发生在该特定日期的记录.逻辑不应该更新所有不在同一天发生的先前记录结束日期.准确地说，我希望这个逻辑只更新那些在同一天生成的具有不同 LocationID 的实例.

I would like this change to get updated to only records that occurred in that particular day. Logic should not update all the prior records end date which doesnt occur on same day. To be precise I would like this logic to get updated only those instances that are generated on same day with different LocationID.

CREATE TABLE [dbo].[TestTab1](
StoreID [int] NOT NULL,
PurchaseID [int] NOT NULL,
ShopID [int] NOT NULL,
LocationID [int] NOT NULL,
starttime [datetime] NOT NULL,
Endtime [datetime] NOT NULL,
) ON [PRIMARY]

INSERT INTO [TestTab1]
VALUES (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'),
(1022,20200102,9856,0030,'2020-01-09 09:08:53','2020-01-09 09:11:52'),
(1023,20200102,9856,0040,'2020-01-10 09:09:48','2020-01-10 09:11:52')

Existing Data:
StoreID PurchaseID ShopID LocationID starttime Endtime
1020 20200102 9856 10 2020-01-08 09:08:53.000 2020-01-08 09:11:52.000
1021 20200102 9856 20 2020-01-08 09:09:48.000 2020-01-08 09:11:52.000
1022 20200102 9856 30 2020-01-09 09:08:53.000 2020-01-09 09:12:52.000
1023 20200102 9856 40 2020-01-10 09:09:48.000 2020-01-10 09:13:52.000

Final Result set:

StoreID PurchaseID ShopID LocationID starttime Endtime
1020 20200102 9856 10 2020-01-08 09:08:53.000 2020-01-08 09:09:48.000
1021 20200102 9856 20 2020-01-08 09:09:48.000 2020-01-08 09:11:52.000
1022 20200102 9856 30 2020-01-09 09:08:53.000 2020-01-09 09:12:52.000
1023 20200102 9856 40 2020-01-10 09:09:48.000 2020-01-10 09:13:52.000

推荐答案

我认为这就是您要查找的内容，但是您预期输出的最后两行的 EndTimes 没有意义，因为它们不在原始数据中放.但是试一试，看看它是否能满足您的需求:

I think this is what you are looking for, but the last two rows of your expected output have EndTimes that make no sense as they are not in the original data set. But give this a go and see if it gets you what you need:

UPDATE TestTab1
    SET Endtime = T2.NewEndDate
FROM TestTab1 T1
INNER JOIN
    (
        SELECT *,
        LEAD(Starttime,1,endtime) OVER (PARTITION BY ShopID, PurchaseID ,CAST(StartTime as DATE) ORDER BY StartTime) NewEndDate
        from TestTab1
    ) T2 on T1.StoreID = t2.StoreID
WHERE T2.NewEndDate <> T2.Endtime

此查询仅考虑使用不同的位置.您可以根据您希望更新最早记录还是最新记录来调整最内层以使用 MIN 或 MAX StoreID:

This query takes into account using only different locations. You can adjust the inner most to use either the MIN or MAX StoreID depending on if you want the earliest or latest record to be updated:

UPDATE TestTab1
    SET Endtime = T2.NewEndDate
FROM TestTab1 T1
INNER JOIN
    (
        SELECT T1.*,
            LEAD(T1.Starttime,1,endtime) OVER (PARTITION BY T1.ShopID, T1.PurchaseID ,CAST(T1.StartTime as DATE) ORDER BY T1.StartTime) NewEndDate
        FROM TestTab1 T1 
        INNER JOIN
            (SELECT MIN(StoreID) StoreID, PurchaseID, ShopID, LocationID, MAX(StartTime) StartTime
                FROM TestTab1
                GROUP BY PurchaseID, ShopID, LocationID
            ) t3  on t3.StoreID = t1.StoreID
    ) T2 on T1.StoreID = t2.StoreID
WHERE T2.NewEndDate <> T2.Endtime 

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