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]

(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')

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

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