Question: I would like to have a query for below scenario.
I have a table with event and occurence time as below:

```
EventName     OccuredAtDatetime
EventA        2019-07-03 00:14:01
EventA        2019-07-03 01:14:01
EventA        2019-07-03 01:34:01
EventB        2019-07-03 01:13:03

```

I would like to know whether a event has occured within a time interval (30 minutes in this example) , say starttime as 2019-07-03 00:00:00 and endtime as 2019-07-03 02:00:00, the interval would be like

```
IntervalName     StartTime                EndTime
Interval1       2019-07-03 00:00:00       2019-07-03 00:30:00
Interval2       2019-07-03 00:30:00       2019-07-03 01:00:00
Interval3       2019-07-03 01:00:00       2019-07-03 01:30:00
Interval4       2019-07-03 01:30:00       2019-07-03 02:00:00

```

So, the output would be like,

```
EventName     Interval1    Interval2       Interval3        Interval4
EventA        Yes          No              Yes              Yes
EventB        No           No              Yes              No

```

This is also my first post on stackoverflow so please let me know if the data are not in the preferred format or if there any additional questions. Thanks!

Answer: 
Assuming your tables are called Events and Intervals, this will do:

```
with cte as (
select e.EventName,
    i.IntervalName
from Events as e
    cross apply Intervals as i 
where e.OccuredAtDatetime>= i.StartTime
  and e.OccuredAtDatetime < i.EndTime
)
select  e.EventName,
        max(IIF(c.IntervalName='Interval1','Yes','No')) as Interval1,
        max(IIF(c.IntervalName='Interval2','Yes','No')) as Interval2,
        max(IIF(c.IntervalName='Interval3','Yes','No')) as Interval3,
        max(IIF(c.IntervalName='Interval4','Yes','No')) as Interval4
from events as e
    left outer join cte as c ON e.EventName = c.EventName
group by e.EventName

```

Basically the cte is finding all the instances that occurred, and the second select is just for formatting according to what you asked for.
You can achieve this by executing a dynamic SQL query.

**Query**

```
declare @sql as varchar(max);
select @sql = 'select [t1].[EventName], ' + stuff((
        select ', max(case when [IntervalName] = ' + char(39) + [IntervalName] + char(39)
        + ' and [t1].OccuredAtDatetime between [t2].[StartTime] and [t2].[EndTime] then '
        + char(39) + 'Yes' + char(39) + ' else ' + char(39) + 'No' + char(39) 
        + ' end) as [' + [IntervalName] + ']'
        from [intervals]
        for xml path('')
    ),
    1, 1, ''
)
+ ' from [events] as [t1], [intervals] as [t2]'
+ ' group by [t1].[EventName];';

exec(@sql);

```

**[Find demo here](https://rextester.com/TPRX23331)**