SQL generating table that shows date span between a defined set of events

Question

I'm looking for a way to extract a table that shows the dates between a range of defined events. I have been thinking about Partition By combined with a control table to do the job.

I have the following table with all transactions

Now besides that, there is a control table where end users can define what ranges they want to calculate the date ranges between. It looks like this

Now question is whether it could be possible to have a smotth and fast SQL statement that through combining those two tables can produce the follwoing input. The output holds the then found combinations of fromdate and todate for each employee and area using control table to eventually calculate the DATEDIFF between the events as shown below:

    CREATE TABLE [dbo].[Fact](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [PersonID] [nvarchar](255) NULL,
    [PersonName] [nvarchar](255) NULL,
    [Area] [nvarchar](255) NULL,
    [EventID] [nvarchar](255) NULL,
    [Date] [date] NULL
    )
GO

    CREATE TABLE [dbo].[ControlTable](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [EventSequenceID] [nvarchar](255) NULL,
    [EventSequenceName] [nvarchar](255) NULL,
    [FromEvent] [nvarchar](255) NULL,
    [ToEvent] [nvarchar](255) NULL
)
GO

INSERT [dbo].[ControlTable] ([EventSequenceID], [EventSequenceName], [FromEvent], [ToEvent]) VALUES (N'1', N'Event E-> H', N'Event E', N'Event H')
GO
INSERT [dbo].[ControlTable] ([EventSequenceID], [EventSequenceName], [FromEvent], [ToEvent]) VALUES (N'2', N'Event K to H', N'Event K', N'Event H')
GO
INSERT [dbo].[ControlTable] ([EventSequenceID], [EventSequenceName], [FromEvent], [ToEvent]) VALUES (N'3', N'Event E to Event T', N'Event E', N'Event T')
GO

INSERT [dbo].[Fact] ([PersonID], [PersonName], [Area], [EventID], [Date]) VALUES (N'1', N'Peter', N'Area X', N'Event E', CAST(N'2000-01-01' AS Date))
GO
INSERT [dbo].[Fact] ([PersonID], [PersonName], [Area], [EventID], [Date]) VALUES (N'1', N'Peter', N'Area X', N'Event B', CAST(N'2000-01-03' AS Date))
GO
INSERT [dbo].[Fact] ([PersonID], [PersonName], [Area], [EventID], [Date]) VALUES (N'1', N'Peter', N'Area X', N'Event K', CAST(N'2000-01-06' AS Date))
GO
INSERT [dbo].[Fact] ([PersonID], [PersonName], [Area], [EventID], [Date]) VALUES (N'1', N'Peter', N'Area X', N'Event A', CAST(N'2000-01-09' AS Date))
GO
INSERT [dbo].[Fact] ([PersonID], [PersonName], [Area], [EventID], [Date]) VALUES (N'1', N'Peter', N'Area X', N'Event H', CAST(N'2000-01-12' AS Date))
GO
INSERT [dbo].[Fact] ([PersonID], [PersonName], [Area], [EventID], [Date]) VALUES (N'2', N'Lis', N'Area X', N'Event E', CAST(N'2000-01-01' AS Date))
GO
INSERT [dbo].[Fact] ([PersonID], [PersonName], [Area], [EventID], [Date]) VALUES (N'2', N'Lis', N'Area X', N'Event K', CAST(N'2000-01-05' AS Date))
GO
INSERT [dbo].[Fact] ([PersonID], [PersonName], [Area], [EventID], [Date]) VALUES (N'2', N'Lis', N'Area X', N'Event C', CAST(N'2000-01-06' AS Date))
GO
INSERT [dbo].[Fact] ([PersonID], [PersonName], [Area], [EventID], [Date]) VALUES (N'2', N'Lis', N'Area X', N'Event H', CAST(N'2000-01-11' AS Date))
GO
INSERT [dbo].[Fact] ([PersonID], [PersonName], [Area], [EventID], [Date]) VALUES (N'2', N'Lis', N'Area X', N'Event T', CAST(N'2000-01-28' AS Date))
GO
INSERT [dbo].[Fact] ([PersonID], [PersonName], [Area], [EventID], [Date]) VALUES (N'2', N'Lis', N'Area B', N'Event E', CAST(N'2000-03-10' AS Date))
GO
INSERT [dbo].[Fact] ([PersonID], [PersonName], [Area], [EventID], [Date]) VALUES (N'2', N'Lis', N'Area B', N'Event G', CAST(N'2000-03-14' AS Date))
GO
INSERT [dbo].[Fact] ([PersonID], [PersonName], [Area], [EventID], [Date]) VALUES (N'2', N'Lis', N'Area B', N'Event H', CAST(N'2000-03-20' AS Date))
GO

There might be multiple events within the same person/area, so ex. adding the follwoing row means that there are two "Event H" for Peter and Area X

  INSERT [dbo].[Fact] ([PersonID], [PersonName], [Area], [EventID], [Date]) VALUES (N'1', N'Peter', N'Area X', N'Event H', CAST(N'2000-01-24' AS Date))

So as an end result the output could then include the rank of the from/to event like

Answer 1

Assuming that the same event never appears twice for the same person/area tuple, you could join the table and do conditional aggregation:

select
    ct.eventSequenceId,
    ct.eventSequenceName,
    f.personID,
    f.personName,
    f.area,
    min(case when f.eventID = ct.fromEvent then f.date end) fromDate,
    min(case when f.eventID = ct.toEvent then f.date end) toDate,
    datediff(
        day, 
        min(case when f.eventID = ct.fromEvent then f.date end),
        min(case when f.eventID = ct.toEvent then f.date end)
    ) dateDifference
from fact f
inner join controltable ct 
    on f.eventID in (ct.fromEvent, ct.toEvent)
group by
    ct.eventSequenceId,
    ct.eventSequenceName,
    f.personID,
    f.personName,
    f.area

having count(*) > 1 order by f.personID, ct.eventSequenceId

The having clause filters out partial matches (ie one event was found but not the other one).

Demo on DB Fiddle:

eventSequenceId | eventSequenceName  | personID | personName | area   | fromDate   | toDate     | dateDifference
:-------------- | :----------------- | :------- | :--------- | :----- | :--------- | :--------- | -------------:
1               | Event E-> H        | 1        | Peter      | Area X | 2000-01-01 | 2000-01-12 |             11
2               | Event K to H       | 1        | Peter      | Area X | 2000-01-06 | 2000-01-12 |              6
1               | Event E-> H        | 2        | Lis        | Area B | 2000-03-10 | 2000-03-20 |             10
1               | Event E-> H        | 2        | Lis        | Area X | 2000-01-01 | 2000-01-11 |             10
2               | Event K to H       | 2        | Lis        | Area X | 2000-01-05 | 2000-01-11 |              6
3               | Event E to Event T | 2        | Lis        | Area X | 2000-01-01 | 2000-01-28 |             27