CODEWITHSUNDEEP
sqlsql-servert-sqldatetimewindow-functions
John
John

Reputation: 47

Adjusting the end dates

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

Upvotes: 1

Views: 58

Answers (2)

Thom A
Thom A

Reputation: 95830

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;

Upvotes: 0

GMB
GMB

Reputation: 222582

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

Upvotes: 1

Related Questions