CREATE PROCEDURE [dbo].[BillingCalculateTime] @CalcType int, @UserID int, @ProjectID int, @date1 datetime, @date2 datetime, @Time int = null output, @ReturnTable bit = 1AS-- допустимые типы расчета (@CalcType)-- 1 - время сессий (с учетом идущих в данный момент)-- 2 - время интервью (с учетом идущих в данный момент без проверки на "полное" интервью)-- 3 - время полных интервью (только те, что проходили в указанный промежуток времени)-- проверяем чтобы даты не были больше текущейif (@date2 > GETDATE()) set @date2 = GETDATE()if (@date2 < @date1)begin set @time = null returnend-- временная табличка для результатовcreate table #bct_results ([PeriodDT1] datetime, [PeriodDT2] datetime)-- временная табличка для подходящих сессийcreate table #bct_sessions ([ID] int identity(1,1) primary key, [PeriodDT1] datetime, [PeriodDT2] datetime)-- кол-во записей в табличке с сессиямиdeclare @sessions_count intset @sessions_count = 0-- текущая позиция в табличке с сессиямиdeclare @session_n intset @session_n = 0-- выбираем все сессии, которые пересекаются с заданным диапазоном-- если сессия ещё не закрыта - тоже считаемif (@CalcType = 1) begin insert into #bct_sessions select [PeriodDT1], COALESCE([PeriodDT2], @date2) from [Billing] with (nolock) where (@userid is null or [UserID] = @userid) and (@projectid is null or [ProjectID] = @projectid) and [EventTypeID] = 10 and ([PeriodDT1] < @date2 and ([PeriodDT2] is null or [PeriodDT2] > @date1)) order by [PeriodDT1] set @sessions_count = @@ROWCOUNT end-- выбираем все интервью, проходившие в указанный диапазонif (@CalcType = 2) begin insert into #bct_sessions select [PeriodDT1], COALESCE([PeriodDT2], @date2) from [Billing] with (nolock) where (@userid is null or [UserID] = @userid) and (@projectid is null or [ProjectID] = @projectid) and [EventTypeID] = 20 and ([PeriodDT1] < @date2 and ([PeriodDT2] is null or [PeriodDT2] > @date1)) order by [PeriodDT1] set @sessions_count = @@ROWCOUNT end-- выбираем все полные интервью, проходившие в указанный диапазонif (@CalcType = 3) begin insert into #bct_sessions select [PeriodDT1], [PeriodDT2] from [Billing] with (nolock) where (@userid is null or [UserID] = @userid) and (@projectid is null or [ProjectID] = @projectid) and [EventTypeID] = 20 and ([PeriodDT1] < @date2 and ([PeriodDT2] is not null and [PeriodDT2] > @date1)) and ([iv_IsSuccess] = 1) order by [PeriodDT1] set @sessions_count = @@ROWCOUNT endset nocount on-- рабочий отрезокdeclare @d1 datetimedeclare @d2 datetime-- обрабатываем все сессии последовательноwhile 1 = 1begin -- если ещё нет, добываем отрезок if (@d1 is null) begin if (@session_n < @sessions_count) begin set @session_n = @session_n + 1 select @d1 = [PeriodDT1], @d2 =[PeriodDT2] from #bct_sessions where [ID] = @session_n end else break --print 'GET: ' + convert(varchar(50), @d1, 21) + ' -> ' + convert(varchar(50), @d2, 21) end -- корректируем отрезок по заданным границам if (@d1 < @date1) set @d1 = @date1 if (@d2 > @date2) set @d2 = @date2 -- если даты перевернулись - нет пересечения (очень странно...) if (@d1 > @d2) begin set @d1 = null set @d2 = null continue end -- добываем следующий отрезок -- т.к. выборка сортирована по дате начала, то считаем что дата начала -- следующего отрезка всегда >= дате начала текущего declare @dn1 datetime declare @dn2 datetime if (@session_n < @sessions_count) begin set @session_n = @session_n + 1 select @dn1 = [PeriodDT1], @dn2 = [PeriodDT2] from #bct_sessions where [ID] = @session_n end else begin -- больше ничего нет, вставляем текущий отрезок (если надо) и завершаем проход if (@d1 < @d2) begin --print 'PUT: ' + convert(varchar(50), @d1, 21) + ' -> ' + convert(varchar(50), @d2, 21) insert into #bct_results ([PeriodDT1], [PeriodDT2]) values (@d1, @d2) end break end --print 'GET: ' + convert(varchar(50), @dn1, 21) + ' -> ' + convert(varchar(50), @dn2, 21) -- если текущий и следующий пересекаются - урезаем текущий до начала следующего if (@dn1 < @d2) begin -- если следующий весь лежит внутри текущего - расширяем его if (@d2 > @dn2) set @dn2 = @d2 set @d2 = @dn1 -- на всякий случай проверяем что не ушли за границы (очень странно...) if (@d2 > @date2) set @d2 = @date2 end -- добавляем отрезок, если надо if (@d1 < @d2) if (@dn1 > @d2) begin --print 'PUT: ' + convert(varchar(50), @d1, 21) + ' -> ' + convert(varchar(50), @d2, 21) insert into #bct_results ([PeriodDT1], [PeriodDT2]) values (@d1, @d2) end else -- объединяем set @dn1 = @d1 -- переключаемся на следующий set @d1 = @dn1 set @d2 = @dn2end--print ''-- считаем рабочее времяselect @time = sum(datediff(second, [PeriodDT1], [PeriodDT2])) from #bct_results-- если надо - возвращаем таблицуif (@ReturnTable = 1) select * from #bct_resultsdrop table #bct_sessionsdrop table #bct_resultsset nocount off