Вы можете использовать немного «рекурсии» (с CTE), чтобы построить расписание и присоединиться к нему с помощью source_data, чтобы найти все пересечения интервалов. В моем решении я не использовал поле «Продолжительность», потому что мне все равно приходилось вычислять длины интервалов (в секундах). CTE EventDays «создает» все дни (если запрос длится более 100 дней, вам придется изменить параметр MAXRECURSION или начать с CTE для больших интервалов, например, месяцев или даже лет), а CTH HalfHours создает все часы haf- интервалы для каждого из дней, чтобы построить расписание:
DECLARE @FirstDay smalldatetime = '20180711',
@LastDay smalldatetime = '20180711';
WITH
EventDays (EventDay, NextDay, MaxDay) AS (
SELECT @FirstDay, DATEADD(day, 1, @FirstDay), @LastDay
UNION ALL
SELECT NextDay, DATEADD(day, 1, NextDay), MaxDay
FROM EventDays WHERE NextDay <= MaxDay
),
HalfHours (StartTime, EndTime, MaxTime) AS (
SELECT EventDay, DATEADD(minute, 30, EventDay), NextDay
FROM EventDays
UNION ALL
SELECT EndTime, DATEADD(minute, 30, EndTime), MaxTime
FROM HalfHours WHERE EndTime < MaxTime
)
SELECT ResourceID, EventType, isec.StartTime, isec.EndTime,
DATEDIFF(second, isec.StartTime, isec.EndTime) AS Duration
FROM source_data sd
INNER JOIN HalfHours hh
ON sd.EventEndDatetime > hh.StartTime AND sd.EventStartDatetime < hh.EndTime
CROSS APPLY (VALUES ( -- intersection StartTime and EndTime
CASE
WHEN sd.EventStartDatetime > hh.StartTime THEN sd.EventStartDatetime
ELSE hh.StartTime
END,
CASE
WHEN sd.EventEndDatetime < hh.EndTime THEN sd.EventEndDatetime
ELSE hh.EndTime
END)
) isec (StartTime, EndTime)
ORDER BY isec.StartTime;
Вы передаете значения как даты. Вам не нужно конвертировать их:
CREATE DEFINER=`*****`@`localhost` PROCEDURE `Payable_Total_Net`(
IN in_startDate DATE,
IN in_endDate DATE,
OUT out_totalNet DECIMAL(19,4)
)
BEGIN
SELECT COALESCE(SUM(p.netamt), 0) INTO out_totalNet
FROM Payables p
WHERE p.purdate >= in_startDate AND
p.purdate <= in_endDate;
END;