Joining Against Derived Table

Question

I'm not sure of the terminology here, so let me give an example. I have this query:

SELECT * FROM Events
--------------------

Id  Name     StartPeriodId  EndPeriodId
1   MyEvent  34             32

In here, the PeriodIds specify how long the event lasts for, think of it as weeks of the year specified in another table if that helps. Notice that the EndPeriodId is not necessarily sequentially after the StartPeriodId. So I could do this:

SELECT * FROM Periods WHERE Id = 34
-----------------------------------

Id StartDate   EndDate
34 2009-06-01  2009-08-01

Please do not dwell on this structure, as it's only an example and not how it actually works. What I need to do is come up with this result set:

Id Name    PeriodId 
1  MyEvent 34
1  MyEvent 33
1  MyEvent 32

In other words, I need to select an event row for each period in which the event exists. I can calculate the Period information (32, 33, 34) easily, but my problem lies in pulling it out in a single query.

This is in SQL Server 2008.

Answer 1

I may be mistaken, and I can't test it right now because there's no SQL Server available right now, but wouldn't that simply be:

SELECT      Events.Id, Events.Name, Periods.PeriodId
FROM        Periods
INNER JOIN  Events
ON          Periods.ID BETWEEN Events.StartPeriodId AND Events.EndPeriodId

Answer 2

I'm assuming that you want a listing of all periods that fall between the dates for the periods specified by start/end period id's.

With CTE_PeriodDate (ID, MaxDate, MinDate)
as (
Select Id, Max(Dates) MaxDate, MinDate=Min(Dates) from (
Select e.ID, StartDate as Dates from Events e 
Inner join Periods P on P.ID=StartPeriodID
Union All 
Select e.ID, EndDate from Events e
Inner join Periods P on P.ID=StartPeriodID 
Union All 
Select e.ID, StartDate from Events e
Inner join Periods P on P.ID=EndPeriodID
Union All 
Select e.ID, EndDate from Events e
Inner join Periods P on P.ID=EndPeriodID ) as A
group by ID)
Select E.Name, P.ID from CTE_PeriodDate CTE
Inner Join Periods p on 
(P.StartDate>=MinDate and P.StartDate<=MaxDate) 
and (p.EndDate<=MaxDate and P.EndDate>=MinDate) 
Inner Join Events E on E.ID=CTE.ID

It's not the best way to do this, but it does work. It get's the min and max date ranges for the periods specified on the event. Using these two date it joins with the periods table on values inside the range between the two.

Kris