CODEWITHSUNDEEP
sqlsql-server
mistaq
mistaq

Reputation: 385

SQL Server - getting the correct price for the date period of an activity

I have some activity occurrences with the date range they occur across:

ActivityOccurrence:

Each activity has a price which apply within an effective period:

EffectivePeriod

I'd like to link the Activity Occurrences with their correct rates. So:

Doing a standard JOIN gets both effective periods for all 3 activity occurrences which I don't want. Using StartDate >= ValidFrom is correct for the first activity occurrence, but not the second and third. Using StartDate <= ValidTo means the first one is wrong, but the second and third are correct. Switching StartDate to EndDate also has some issues.

SQLFiddle: http://sqlfiddle.com/#!18/576c6/6

I feel like I'm missing something and the answer is very simple but I can't figure out what it is.

Upvotes: 2

Views: 39

Answers (1)

George Menoutis
George Menoutis

Reputation: 7250

Are you trying to make sure that each ActivityOccurrence is joined with EACH EffectivePeriod that is temporally included in its date range?

What I use in such cases is make sure either start or end date of one table is between the start-end of the other:

SELECT ao.ActivityID, ao.StartDate, ao.EndDate, ep.Price
FROM ActivityOccurrence ao
JOIN EffectivePeriod ep ON ao.ActivityID = ep.ActivityID
AND 
(
    (ao.StartDate between ep.ValidFrom and ep.ValidTo)
    OR
    (ao.EndDate between ep.ValidFrom and ep.ValidTo)
)

Upvotes: 2

Related Questions