Returning a List and a Range Count

How would you return a list and a range count in one query? Compare your idea with Itzik Ben-Gan’s creative solution.

Itzik Ben-Gan

April 30, 2002

1 Min Read
ITPro Today logo in a gray background | ITPro Today


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.

Sign up for the ITPro Today newsletter
Stay on top of the IT universe with commentary, news analysis, how-to's, and tips delivered to your inbox daily.

You May Also Like