Creating and Managing SQL Scripts - Microsoft Business Intelligence Tools for Excel Analysts - page 208

Figure 10-12: Members table.
If you think about the data in the Members table, you can deduce that you don't have an entry for
each member for each day. Therefore, your query needs to simulate that action. To accomplish that,
you have to add a CalendarDate table that has an entry for every day that you want to produce the
report for (see Figure 10-13). This table can be created manually in Excel and copied into SQL Server.
Figure 10-13: CalendarDate table.
When you have the tables set up, you can write the query to produce the Daily Active Member
Report (shown in Listing 10-7).
Listing 10-7 A Daily Active Member Report
Select c.CalendarDate, Count(*) DailyActiveMembersCount
From Member m
Join CalendarDate c
On c.CalendarDate between m.StartDate
and Case
When m.Cancelled=1
Then m.CancelDate
Else '12/31/9999'
End
Group By c.CalendarDate
Order By c.CalendarDate
The query in Listing 10-7 is a Between Join query that creates additional rows for every row in the
CalendarDate table.