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

score 1 · Answer 1 · answered Sep 24 '20 at 16:48

You can use lead() and conditional logic:

select storeid, purchaseid, shopid, locationid, starttime,
    case when endtime > lead(starttime) over(partition by shopid, convert(date, starttime) order by starttime)
        then lead(starttime) over(partition by shopid, convert(date, starttime) order by starttime)
        else endtime
    end endtime
from testtab1

Moving the window calculation to a subquery makes the query a bit more readable:

select storeid, purchaseid, shopid, locationid, starttime,
    case when endtime > lead_starttime then lead_starttime else endtime end endtime
from (
    select t.*, lead(starttime) over(partition by shopid, convert(date, starttime) order by starttime) lead_starttime
    from testtab1 t
) t

Demo on DB Fiddle:

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-08 09:12:53.000 | 2020-01-08 09:14:52.000
   1023 |   20200102 |   9856 |         40 | 2020-01-08 09:16:48.000 | 2020-01-08 09:18:52.000

Thanks for sending the detailed logic. I have few edges cases where the enddatetimes are null and in this scenario I would like the value to default to end of business day datetime by looking at starttime of that respective transaction. Thanks in advance. — John, Sep 25 '20 at 15:41

Thom A · Answer 2 · 2020-09-25T16:14:19.497

0

This is effectively the same solution as GMB, but they were quicker at written an answer than I, and I use a CTE as I feel it's more readable over multiple LEADs:

WITH CTE AS(
    SELECT TT.StoreID,
           TT.PurchaseID,
           TT.ShopID,
           TT.LocationID,
           TT.starttime,
           TT.Endtime,
           LEAD(TT.startTime,1,Endtime) OVER (ORDER BY StartTime ASC) AS NextstartTime
    FROM dbo.TestTab1 TT)
SELECT C.StoreID,
       C.PurchaseID,
       C.ShopID,
       C.LocationID,
       C.starttime,
       CASE WHEN C.Endtime > C.NextstartTime THEN C.NextstartTime ELSE ISNULL(C.Endtime,DATEADD(MILLSECOND,-3,DATEADD(DAY,1,CONVERT(datetime,CONVERT(date,C.starttime))))) END AS EndTime
FROM CTE C;

edited Sep 25 '20 at 16:14

answered Sep 24 '20 at 16:52

Thom A

88,727
11
45
75

Thanks for sending the detailed logic. I have few edges cases where the enddatetimes are null and in this scenario I would like the value to default to end of business day datetime by looking at starttime of that respective transaction. Thanks in advance. – John Sep 25 '20 at 15:42
You should include that logic in your question, @John , along with examples. Also, when is the end of the business date? `23:59.59.9999999`? `17:00:00`? Does it vary by day? – Thom A Sep 25 '20 at 15:48
End of the business date here would be "23:59.59.9999999". Thanks! – John Sep 25 '20 at 16:09
It's a guess, due to no new sample data, but updated, @John . – Thom A Sep 25 '20 at 16:14
.... Wait, @John, you state above that "where the enddatetimes are null" but you **explicitly** define your column as `NOT NULL`. It *can't* have `NULL` values... – Thom A Sep 25 '20 at 16:16

Adjusting the end dates

2 Answers2