Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- declare @TimeRanges as TABLE (SessionStart datetime, SessionEnd datetime);
- with TimeRanges as (
- select @Start as StartTime, @Start + @TimeRange as EndTime
- union all
- select StartTime + @TimeRange, EndTime + @TimeRange
- from TimeRanges
- where EndTime < @Finish )
- ;with cte as
- (
- select SessionStartTime as changetime,1 as CC from Calls
- union all
- select SessionCloseTime,-1 from Calls
- )
- select top 1 changetime,rt from
- (
- select * from cte
- cross apply
- (select SUM(cc) as rt from cte c where c.changetime<=cte.changetime) rt
- ) v
- order by rt desc
- @Start datetime,
- @Finish datetime,
- @TimeRange time
- AS
- BEGIN
- SET NOCOUNT ON;
- declare @res int SET @res = 0
- declare @TimeRanges as TABLE (SessionStart datetime, SessionEnd datetime);
- with TimeRanges as
- ( select @Start as StartTime, @Start + @TimeRange as EndTime
- union all
- select StartTime + @TimeRange, EndTime + @TimeRange
- from TimeRanges
- where EndTime < @Finish ),
- cte as
- (
- select SessionStart as changetime,1 as CC from TimeRanges
- union all
- select SessionEnd,-1 from TimeRanges
- )
- select top 1 changetime,rt from
- (
- select * from cte
- cross apply
- (select SUM(cc) as rt from cte c where c.changetime<=cte.changetime) rt
- ) v
- order by rt desc
- select StartTime, EndTime,cte.rt
- from TimeRanges as TR left outer join
- dbo.Test as Test on TR.StartTime <= Test.SessionStartTime
- and Test.SessionCloseTime < TR.EndTime
- where Test.ScenarioID = 24
- group by TR.StartTime, TR.EndTime,cte.rt
- END
- with TimeRanges as
- ( select @Start as StartTime, @Start + @TimeRange as EndTime --StartTime and EndTime
- union all
- select StartTime + @TimeRange, EndTime + @TimeRange
- from TimeRanges
- where EndTime < @Finish ),
- cte as
- (
- select StartTime as changetime,1 as CC from TimeRanges --StartTime, not SessionStart
- union all
- select EndTime,-1 from TimeRanges --EndTime
- )
- select top 1 changetime,rt from
- (
- select * from cte
- cross apply
- (select SUM(cc) as rt from cte c where c.changetime<=cte.changetime) rt
- ) v
- order by rt desc
- with TimeRanges as
- ( select @Start as StartTime, @Start + @TimeRange as EndTime --StartTime and EndTime
- union all
- select StartTime + @TimeRange, EndTime + @TimeRange
- from TimeRanges
- where EndTime < @Finish )
- select StartTime, EndTime,cte.rt
- from TimeRanges as TR left outer join
- dbo.Test as Test on TR.StartTime <= Test.SessionStartTime
- and Test.SessionCloseTime < TR.EndTime
- where Test.ScenarioID = 24
- group by TR.StartTime, TR.EndTime,cte.rt
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement