SQL Server SQL query to get duration of datetime ranges minus break time ranges?

2sbarzqh 于 2023-10-15 发布在其他

关注(0)|答案(1)|浏览(94)

In SQL Server 2019, I have a table Runs that contains time ranges with start datetime column in UTC and an end datetime column in UTC.

I have another table, BreakSchedule , of break time ranges in the format start time local to end time local.

Given a time zone as a SQL parameter, I want to select multiple rows from the first table (Runs) and get the total duration in minutes from the start UTC datetime column to the end UTC datetime column MINUS any break times whose time zone-adjusted times fall within the range for that particular time range.

Here is an example:

create table Runs 
(
    id int identity(1,1) primary key,
    dtStartTimeUtc datetime not null,
    dtEndTimeUtc datetime not null,
    nExpectedMinutes int not null
);

create table BreakSchedule 
(
    tStartTimeLocal time not null,
    tEndTimeLocal time not null
);

insert into BreakSchedule (tStartTimeLocal, tEndTimeLocal)
values ('08:30:00', '08:40:00'), ('09:30:00', '10:30:00'), 
       ('17:00:00', '18:00:00'), ('23:30:00', '00:30:00');

/*
  Equivalent in UTC if local time zone is Central Standard Time
  13:30-13:40, 14:30-15:30, 22:00-23:00, 04:30-05:30
*/

insert into Runs (dtStartTimeUtc, dtEndTimeUtc, nExpectedMinutes)
values
  ('2023-10-02 12:00:00', '2023-10-02 13:30:00', 90),
  ('2023-10-02 13:00:00', '2023-10-02 13:35:00', 30),
  ('2023-10-02 13:35:00', '2023-10-02 14:00:00', 20),
  ('2023-10-02 14:15:00', '2023-10-03 05:15:00', 735),
  ('2023-10-02 14:30:00', '2023-10-02 15:00:00', 0)

Starting point for query I can't figure out how to finish:

declare @tz varchar(100) = 'Central Standard Time';

select 
    *, 'should be same value as nExpectedMinutes' [actualminutes]
from  
    Runs

The above code also in SQL fiddle here: http://sqlfiddle.com/#!18/42fdd/11

Can anyone help with this?

sql-server

来源：https://stackoverflow.com/questions/77224925/sql-query-to-get-duration-of-datetime-ranges-minus-break-time-ranges

1条答案

按热度按时间

myss37ts1#

DECLARE @Runs TABLE (id int identity(1,1) primary key, dtStartTimeUtc datetimeoffset not null, dtEndTimeUtc datetimeoffset not null, nExpectedMinutes int not null);
DECLARE @BreakSchedule TABLE (tStartTimeLocal time not null, tEndTimeLocal time not null);
insert into @BreakSchedule (tStartTimeLocal, tEndTimeLocal) values 
('08:30:00', '08:40:00'), ('09:30:00', '10:30:00'), ('17:00:00', '18:00:00'), ('23:30:00', '00:30:00');

/*
  Equivalent in UTC if local time zone is Central Standard Time
  13:30-13:40, 14:30-15:30, 22:00-23:00, 04:30-05:30
*/

insert into @Runs (dtStartTimeUtc, dtEndTimeUtc, nExpectedMinutes) values
('2023-10-02 12:00:00', '2023-10-02 13:30:00', 90),  ('2023-10-02 13:00:00', '2023-10-02 13:35:00', 30),  ('2023-10-02 13:35:00', '2023-10-02 14:00:00', 20),  ('2023-10-02 14:15:00', '2023-10-03 05:15:00', 735),  ('2023-10-02 14:30:00', '2023-10-02 15:00:00', 0);

DECLARE @tz NVARCHAR(50) = 'Central Standard Time'

SELECT r.id, r.dtStartTimeUtc, r.dtEndTimeUtc, r.nExpectedMinutes, SUM(DATEDIFF(MINUTE,b.tStartTimeLocal, b.tEndTimeLocal)) AS breakMinutes
  FROM @Runs r
    LEFT OUTER JOIN @BreakSchedule b
      ON b.tStartTimeLocal BETWEEN CAST(r.dtStartTimeUtc AT TIME ZONE @tz AS TIME) AND CAST(r.dtEndTimeUtc AT TIME ZONE @tz  AS TIME)
 GROUP BY r.id, r.dtStartTimeUtc, r.dtEndTimeUtc, r.nExpectedMinutes;

Thanks for providing the DDL/DML! I modified your datatypes to change the DATETIME in Runs to DATETIMEOFFSET . This could be done as a cast inline, if you can't modify the columns.

With a DATETIMEOFFSET we can make use of the AT TIME ZONE modifier to adjust the time zone of the datetime, using the standard time zone names.

From there, we can join the BreakSchedule table, with an inline cast to TIME and collect up the break minutes. I summed the break minutes in the event a run spanned more than one of them.
| id | dtStartTimeUtc | dtEndTimeUtc | nExpectedMinutes | breakMinutes |
| ------------ | ------------ | ------------ | ------------ | ------------ |
| 1 | 2023-10-02 12:00:00.0000000 +00:00 | 2023-10-02 13:30:00.0000000 +00:00 | 90 | 10 |
| 2 | 2023-10-02 13:00:00.0000000 +00:00 | 2023-10-02 13:35:00.0000000 +00:00 | 30 | 10 |
| 3 | 2023-10-02 13:35:00.0000000 +00:00 | 2023-10-02 14:00:00.0000000 +00:00 | 20 | |
| 4 | 2023-10-02 14:15:00.0000000 +00:00 | 2023-10-03 05:15:00.0000000 +00:00 | 735 | |
| 5 | 2023-10-02 14:30:00.0000000 +00:00 | 2023-10-02 15:00:00.0000000 +00:00 | 0 | 60 |

DATETIMEOFFSEThttps://learn.microsoft.com/en-us/sql/t-sql/data-types/datetimeoffset-transact-sql?view=sql-server-ver16

AT TIME ZONEhttps://learn.microsoft.com/en-us/sql/t-sql/queries/at-time-zone-transact-sql?view=sql-server-ver16

赞(0）回复(0）举报 2023-10-15