Selecting midnight boundary from a table and calculating a new duration amount-CodePudding

I have the following data in a table,

Id	datefrom	dateto	duration
1	2022-05-04 23:59:50.300	2022-05-04 23:59:51.317	1016
2	2022-05-04 23:59:51.317	2022-05-04 23:59:59.410	8094
3	2022-05-04 23:59:59.410	2022-05-05 00:00:00.410	1000

The data is collected for each day and the last record on a day will go over to the next day (unless by pure chance it stops at exactly midnight).
I want to select the data above but re-calculated the last record to "2022-05-04 23:59:59.999" and then get the duration between "2022-05-04 23:59:59.410" and "2022-05-04 23:59:59.999".
So my result would look like,

Id	datefrom	dateto	duration
1	2022-05-04 23:59:50.300	2022-05-04 23:59:51.317	1016
2	2022-05-04 23:59:51.317	2022-05-04 23:59:59.410	8094
3	2022-05-04 23:59:59.410	2022-05-04 23:59:59.999	589

Is this possible in a select?

Thanks,

I can get the last record using,

SELECT MAX(Id) FROM (SELECT * FROM [StatusHistory]) as a

But I'm struggling to get a result of all the data with the last record manipulated.

CodePudding user response：

With some date calculations and a CASE.

However, I disagree with your desired results. The 589 should really be 590 ... or inclusive of the last millisecond up until midnight

Example

Declare @YourTable Table ([Id] int,[datefrom] datetime,[dateto] datetime,[duration] int)  
Insert Into @YourTable Values 
 (1,'2022-05-04 23:59:50.300','2022-05-04 23:59:51.317',1016)
,(2,'2022-05-04 23:59:51.317','2022-05-04 23:59:59.410',8094)
,(3,'2022-05-04 23:59:59.410','2022-05-05 00:00:00.410',1000)
 
Select *
      ,NewVal = datediff(MILLISECOND,datefrom,case when convert(date,dateto)=convert(date,datefrom) then dateto else convert(date,datefrom 1) end) 
 from  @YourTable

Results