End date Manipulation

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. The key columns to use will be shopid, starttime,endtime.

Note: 1) For example (PurchaseID:20200102) 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 2) 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. 3) There are scenarios where both records start at same time example store id 1023, 1024. In this scenario I would like the end date one record manipulated to next record. To be precise the startdate and enddate will be same for one record as shown in final result set. 4) 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.

    CREATE TABLE [dbo].[TestTab1](
    StoreID [int] NOT NULL,
    PurchaseID [int] NOT NULL,
    LocationID [int] NOT NULL,
    starttime [datetime] NOT NULL,
    Endtime [datetime] NOT NULL,
    ) ON [PRIMARY]
    
    INSERT INTO [TestTab1]
    VALUES
    (1020,20200102,10,'2020-01-08 09:08:53','2020-01-08 09:18:52'),
    (1021,20200102,20,'2020-01-08 09:09:48','2020-01-08 09:11:52'),
    (1022,20200102,30,'2020-01-08 09:12:53','2020-01-08 09:14:52'),
    (1023,20200104,40,'2020-10-08 09:16:48','2020-10-08 09:48:21'),
    (1024,20200104,60,'2020-10-08 09:16:48','2020-10-08 09:20:22')
    
    Final Result set:
    
    StoreID PurchaseID  LocationID starttime Endtime
    1020    20200102     10 2020-01-08 09:08:53 2020-01-08 09:09:48
    1021    20200102     20 2020-01-08 09:09:48 2020-01-08 09:11:52
    1022    20200102     30 2020-01-08 09:12:53 2020-01-08 09:14:52
    1023    20200104     40 2020-10-08 09:16:48 2020-10-08 09:16:48
    1024    20200104     60 2020-10-08 09:16:48 2020-01-08 09:20:22

Thanks in advance!

In scenario 3 (store id 1023, 1024) I think the data in final record set don't match your requirements. — whynot, Oct 13 '20 at 05:25
Do you want to adjust endtime only within a PurchaseID or also over the PurchaseIDs within a StoreID? Can a PurchaseID start in one day and end on the next day? Why every recordset fo a PurchaseID is related to a different StoreId? Please precise and provide more supporing test data. — whynot, Oct 13 '20 at 05:50

score 0 · Answer 1 · answered Oct 11 '20 at 21:38

0

If I follow you correctly, you can use lead() and condtional logic:

select t.*,
    case when endtime > lead(starttime) over(partition by purchaseid, convert(date, starttime) order by shopid)
        then lead(starttime) over(partition by purchaseid, convert(date, starttime) order by shopid)
        else endtime
    end as new_endtime
from mytable t

answered Oct 11 '20 at 21:38

GMB

216,147
25
84
135

I altered my question. Here I need to look for purchase id and the events that happened on same day and 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. – John Oct 12 '20 at 00:16
@John: then I think this query should do what you want - assuming that `shop_id` defines the ordering of the rows. – GMB Oct 12 '20 at 21:40

End date Manipulation

1 Answers1