T-SQL - Tracking Events Over Time
I have some data that has dates associated with it ValidFrom
and ValidTo
. In simple words:
MembershipId | ValidFromDate | ValidToDate
==========================================
0001 | 1997-01-01 | 2006-05-09
0002 | 1997-01-01 | 2017-05-12
0003 | 2005-06-02 | 2009-02-07
This table has a nonclustered index that includes two dates as key values.
I also have a Date dimension table that spans every date from 1900
to 2999
.
I am trying to figure out how I can select a date range from the Date dimension table (let's say 2016-01-01
- 2016-12-31
) and then determine, for each date, how much membership was valid on that date.
The code below gets the job done, but performance is not great and I was wondering if anyone has any recommendations for improving this path?
SELECT
d.DateKey
,(SELECT COUNT(*) FROM Memberships AS m
WHERE d.DateKey between m.ValidFromDateKey and m.ValidToDateKey
) AS MembershipCount
FROM
DIM.[Date] AS d
WHERE
d.CalendarYear = 2016
Thanks in advance for any suggestions!
source to share
The logic in your SQL is mostly correct, you just implemented it poorly as SQL loves to do things. Starting with your table Dates
, as you already did, instead of doing a subselection for each row of data, change your logic to join
and you are there:
select d.DateKey
,count(m.MembershipID) as MembershipCount
from DIM.[Date] as d
left join Memberships as m
on(d.DateKey between m.ValidFromDateKey and m.ValidToDateKey)
where d.CalendarYear = 2016
group by d.DateKey
order by d.DateKey;
What you might want to be careful about is determining which memberships should be accounted for each day. For example, if your date is equal 2006-05-09
, should the MemberhipID be included 0001
at the end of that day?
The question is, do you count the number of members that were active at any given moment throughout the day, or only those that were active at a specific time, say the beginning or end of the day?
Then repeat this thought process for your meanings ValidFromDate
.
source to share