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

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!

First you need table of numbers see https://stackoverflow.com/questions/58023016/split-date-range-from-a-single-row-into-months-that-are-displayed-in-multiple-ro for example. Next you need dynamic pivot (a lot of answers here) as the number of output columns is varying. — Serg, Sep 21 '19 at 06:56

score 1 · Answer 1 · answered Sep 21 '19 at 06:37

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.

Actually, all I have is Events table. "Interval" table is supposed to be auto-created based on the interval (15minute,30minute,1 hour,etc) . Thanks — Ashish Bhagasra, Sep 21 '19 at 06:47

score 0 · Answer 2 · answered Sep 21 '19 at 07:47

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

this is exactly what I was looking for. Output has dynamic columns now. G8 — Ashish Bhagasra, Sep 21 '19 at 08:24

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

2 Answers2