I need help with my query to calculate the time until midnight between two date and time columns break down by day

This is the main table:

My query:

 
SELECT
    start_time.ID,
    start_time.Date_Time AS Start_time,
    end_time.Date_Time AS End_time,
    DATEDIFF(minute, start_time.Date_Time, end_time.Date_Time) AS DateDiff
FROM 
    Main 
 

what I need is similar to this:

how i can do that?

CodePudding user response：

You can loop through the times, always adding the time untill midnight, untill your 'start_time 1 day' is bigger than your end_time.

The below code can be run directly in SQL (mind the date notation, my SQL is in united states notation, so if yours is in Europe this will give you back results for 3 months instead of 3 days);

DECLARE @start_time datetime2 = '01/01/2022 13:00:00';
DECLARE @end_time datetime2 = '03/01/2022 14:00:00';
DECLARE @daily_end_time datetime2=NULL;
DECLARE @Table Table (start_time datetime2, end_time datetime2, diff nvarchar(8));
DECLARE @diff_minutes_start int = DATEDIFF(MINUTE,@start_time,DateDiff(day,0,dateadd(day,1,@start_time)));
DECLARE @diff_minutes_end int = DATEDIFF(minute,@end_time,DateDiff(day,0,dateadd(day,1,@end_time)))
SET @daily_end_time = DATEADD(mi,@diff_minutes_start,@start_time)

WHILE @daily_end_time < @end_time
BEGIN
    INSERT INTO @Table (start_time,end_time,diff) 
    VALUES (
    @start_time,
    CASE WHEN DATEADD(day,1,@daily_end_time) > @end_time THEN @end_time ELSE 
@daily_end_time END,
CASE WHEN DATEADD(day,1,@daily_end_time) > @end_time THEN @diff_minutes_end ELSE 
@diff_minutes_start END )
  SET @daily_end_time = DATEADD(mi,@diff_minutes_start,@start_time)
SET @start_time = DATEADD(mi,1,@daily_end_time);
select @diff_minutes_start  = 


DATEDIFF(MINUTE,@start_time,DateDiff(day,0,dateadd(day,1,@start_time)));
    select @diff_minutes_end  = DATEDIFF(minute,@end_time,DateDiff(day,0,dateadd(day,1,@end_time)))
    END
    SELECT * FROM @Table

And the results:

CodePudding user response：

You may use a recursive CTE as the following:

With CTE As
(
  Select ID, Start_Time, End_time, DATEADD(Second, -1, DATEADD(Day, DATEDIFF(Day,0, Start_Time), 1)) et
  From main
  Union All
  Select C.ID, DATEADD(Second, 1, C.et), C.End_time, DATEADD(Day, 1, C.et)
  From CTE C Join main T
  On C.ID = T.ID
  Where DATEADD(Second, 1, C.et) <= C.End_time 
 )
Select ID, Start_Time, 
       Case When End_Time <= et Then End_Time Else et End As End_Time,
       DATEDIFF(Minute, Start_Time, DATEADD(Second, 1, Case When End_Time <= et Then End_Time Else et End)) As [DateDiff]
From CTE
Order By ID, Start_Time

See a demo with extended data sample from db<>fiddle.