Returning a List and a Range Count

I created a table called MonthRanges that includes two columns named monthfrom and monthto, as Listing 8 shows. From the MonthRanges table, I want to return the list of all months and the number of month ranges they fall in. For example, January falls in 3 ranges: (1, 2), (1, 3), (1, 4). February falls in 6 ranges: (1, 2), (1, 3), (1, 4), (2, 4), (2, 6), (2, 8), and so on. How can I return that data?

The trick here is to use an auxiliary table that contains all possible months. The auxiliary table can be either a real table that you create and populate with the 12 possible months or a derived table that you create on the fly. Then, you wrap the query with parentheses and assign aliases to both the auxiliary table (Months) and to the column that holds the month values (m), as callout A in Listing 9, page 16, shows. You can now join the Months table to the MonthRanges table based on the following join condition:

ON m BETWEEN monthfrom AND monthto

You group the results by month (m) and return both the month and COUNT(*), which means the number of month ranges each month falls into. Figure 3 shows the output that Listing 9 generates.