How to get activity status that occurred within a interval during start and stop datetime(SQL)(t-SQL

0

Please login or register to vote for this query.

(click on this box to dismiss)

The Great Outdoors

Q&A for people who love being outdoors enjoying nature and wilderness, and learning about the required skills and equipment

declare @events as table(
  [EventName] varchar(100),
  [OccuredAtDatetime] datetime
);
insert into @events
select 'EventA', '2019-07-03 00:14:01' union all
select 'EventA', '2019-07-03 01:14:01' union all
select 'EventA', '2019-07-03 01:34:01' union all
select 'EventB', '2019-07-03 01:13:03';

declare @interval as table(
  [IntervalName] varchar(100),
  [StartTime] datetime,
  [EndTime] datetime
);

insert into @interval
select 'Interval1', '2019-07-03 00:00:00', '2019-07-03 00:30:00' union all
select 'Interval2', '2019-07-03 00:30:00', '2019-07-03 01:00:00' union all
select 'Interval3', '2019-07-03 01:00:00', '2019-07-03 01:30:00' union all
select 'Interval4', '2019-07-03 01:30:00', '2019-07-03 02:00:00';


--select * from @events;
--select * from @interval;

select [t1].[EventName], 
max(case when [IntervalName] = 'Interval1' and 
[t1].OccuredAtDatetime between [t2].[StartTime] and [t2].[EndTime] then 'Yes' 
else 'No' end) as [Interval1],
max(case when [IntervalName] = 'Interval2' and 
[t1].OccuredAtDatetime between [t2].[StartTime] and [t2].[EndTime] then 'Yes' 
else 'No' end) as [Interval2],
max(case when [IntervalName] = 'Interval3' and 
[t1].OccuredAtDatetime between [t2].[StartTime] and [t2].[EndTime] then 'Yes' 
else 'No' end) as [Interval3],
max(case when [IntervalName] = 'Interval4' and 
[t1].OccuredAtDatetime between [t2].[StartTime] and [t2].[EndTime] then 'Yes' 
else 'No' end) as [Interval4]
from @events as [t1], @interval as [t2]
where 1 = 1
group by [t1].[EventName];


--declare @sql as varchar(max);
--select @sql = stuff((

Enter Parameters

Options:
Switch sites:
loading Hold tight while we fetch your results