Adjusting the end dates

Question

I wanted to manipulate the end date to next record startdate or records that happened on same day for shopID and then grab the starttime of later record and update the value in the prior row endtime if the starttime prior to next row start time and end date crosses the next record start date.

For example (ShopId: 9856) end date of first record is '2020-01-08 09:18:52' which is crosses the start time of next record '2020-01-08 09:09:48'. Here my logic need to look for such instances happen on same day and manipulate the enddate of first record to next record startdate

CREATE TABLE [dbo].TestTab1 ON [PRIMARY]

INSERT INTO [TestTab1]
VALUES
(1020,20200102,9856,0010,'2020-01-08 09:08:53','2020-01-08 09:18:52'),
(1021,20200102,9856,0020,'2020-01-08 09:09:48','2020-01-08 09:11:52'),
(1022,20200102,9856,0030,'2020-01-08 09:12:53','2020-01-08 09:14:52'),
(1023,20200102,9856,0040,'2020-01-08 09:16:48','2020-01-08 09:18:52')

Final Result set:

StoreID PurchaseID ShopID LocationID starttime Endtime
1020 20200102 9856 10 2020-01-08 09:08:53 2020-01-08 09:09:48
1021 20200103 9856 20 2020-01-08 09:09:48 2020-01-08 09:11:52
1022 20200102 9856 30 2020-01-08 09:12:53 2020-01-08 09:14:52
1023 20200104 9856 40 2020-01-08 09:16:48 2020-01-08 09:18:52

Answer 1

 select StoreID,PurchaseID,ShopID,LocationID,starttime,
  Case when lead(starttime) Over(partition by ShopID order by starttime ) < Endtime 
  and datediff(day,starttime, Endtime)= 0 then 
 lead(starttime) Over(partition by ShopID order by starttime ) else Endtime end Endtime
 from [dbo].[TestTab1]

Answer 2

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:18:52'),
(1021,20200102,9856,0020,'2020-01-08 09:09:48','2020-01-08 09:11:52'),
(1022,20200102,9856,0030,'2020-01-08 09:12:53','2020-01-08 09:14:52'),
(1023,20200102,9856,0040,'2020-01-08 09:16:48','2020-01-08 09:18:52')

select StoreID,PurchaseID,ShopID,LocationID,starttime,
 Case when lead(starttime) Over(partition by ShopID order by starttime ) < Endtime then 
lead(starttime) Over(partition by ShopID order by starttime ) else Endtime end Endtime
from [dbo].[TestTab1]
/*
Final Result set:

StoreID PurchaseID ShopID LocationID starttime Endtime
1020 20200102 9856 10 2020-01-08 09:08:53 2020-01-08 09:09:48
1021 20200103 9856 20 2020-01-08 09:09:48 2020-01-08 09:11:52
1022 20200102 9856 30 2020-01-08 09:12:53 2020-01-08 09:14:52
1023 20200104 9856 40 2020-01-08 09:16:48 2020-01-08 09:18:52

*/

drop   TABLE  if exists [TestTab1]  

Answer 3

@Jingyang Li Thank for the answer! Your code works for most of the scenarios.

I would like this change to happen only on the records that occurred on same day. To be precise the records with same startdates but overlapping the enddates excluding the timestamp. Please suggest.

Answer 4

You already have a thread on the same question? Why start another? Here is my solution once more:

; WITH numbering AS (
      SELECT *, row_number() OVER(PARTITION BY ShopID, PurchaseID ORDER BY starttime) AS rowno
       FROM TestTab1
 )
 UPDATE a
 SET    a.Endtime = b.starttime
 FROM   numbering a
 JOIN   numbering b ON a.ShopID = b.ShopID
                   AND a.PurchaseID = b.PurchaseID
                   AND a.rowno = b.rowno -1
 WHERE  a.LocationID <> b.LocationID
   AND  convert(date, a.Endtime) = convert(date, b.starttime)

Answer 5

@Erland Sommarskog Sorry for starting the new thread. I had start new because the grain has been changed compared to old one and wanted to start new one to keep this is in top few. Above logic worked for previous item and thanks again for sending above one. I unable to use this for new grain of the table logic.