TheOne__
TheOne__

Reputation: 426

Round time to half hour intervals and sum it

I'm storing time as an int as seen in the Time column below.

I can convert to datetime or to seconds using already defined functions but I need to sum it after rounding to 30 minutes.

-----------------------------------------------------------------
Time       | datetime                | desired output as datetime
-----------------------------------------------------------------
11600      | 2021-05-05 01:16:00.000 |  2021-05-05 01:30:00.000
200        | 2021-05-19 00:02:00.000 |  2021-05-19 00:30:00.000
100        | 2021-05-10 00:01:00.000 |  2021-05-10 00:30:00.000
23000      | 2021-05-21 02:30:00.000 |  2021-05-21 02:30:00.000
-----------------------------------------------------------------

What I manage so far is convert int to datetime and then on condition round to 30 minutes (desired output column above) but I still need to sum it and can't like this:

;with t as(
SELECT 
[dbo].[sys_time_from_int](DATE,TIME_AWAY) DATE_AUX
FROM [time_sheet] x1
)
SELECT 
  CASE 
  WHEN datepart(MINUTE, DATE_AUX) = 30 or  datepart(MINUTE, DATE_AUX) = 00  
  THEN  DATEADD( minute, ( DATEDIFF( minute, 0, DATEADD( second, ( 30 * 60 ) / 2, DATE_AUX) ) / 30 ) * 30, 0 )
  ELSE  DATEADD( minute, ( DATEDIFF( minute, 0, DATEADD( minute,   30           , DATE_AUX) ) / 30 ) * 30, 0 )
 end as TIME_AUX
from t

the function seen takes (2021-05-05, 11600) and converts it to datetime. I can also use c#. any tips would be greatly appreciated.

Upvotes: 1

Views: 290

Answers (1)

John Cappelletti
John Cappelletti

Reputation: 81930

Seems like you are aggressively rounding up (hence the +29).

Perhaps this will help

Example

Declare @YourTable Table ([Time] int,[datetime] datetime)  
Insert Into @YourTable Values 
 (11600,'2021-05-05 01:16:00.000')
,(200,'2021-05-19 00:02:00.000')
,(100,'2021-05-10 00:01:00.000')
,(23000,'2021-05-21 02:30:00.000')
 
Select * 
      ,NewValue = dateadd(minute,((datediff(minute,0,[datetime])+29)  / 30) * 30,0)
 From @YourTable

Results

Time    datetime                    NewValue
11600   2021-05-05 01:16:00.000     2021-05-05 01:30:00.000
200     2021-05-19 00:02:00.000     2021-05-19 00:30:00.000
100     2021-05-10 00:01:00.000     2021-05-10 00:30:00.000
23000   2021-05-21 02:30:00.000     2021-05-21 02:30:00.000

Update with Aggregate

 Select sum((datepart(hour,NewValue)*60) + datepart(minute,NewValue)) / 60.0
  From  @YourTable
  Cross Apply (values ( ( dateadd(minute,((datediff(minute,0,[datetime])+29)  / 30) * 30,0) ) ) ) b(NewValue)

Upvotes: 1

Related Questions