N consecutive rows, Part 2 of 4

First, let's have some more test data. The original rows given in the problem statement are the ones in bold.

Name SC   UseDate
Joe  F50  2004-04-01
Joe  F50  2004-05-01
Joe  F50  2004-11-01
Joe  F50  2004-12-01
Joe  F50  2005-01-03
Joe  F55  2004-07-01
Joe  F55  2004-08-05
Tom  F50  2004-01-01
Tom  F50  2004-02-28
Tom  F50  2004-03-01
Tom  F50  2004-04-04

Here's your original ServiceCodes table, with minor changes (to reduce the number of consecutive months, otherwise the sample data would be have to be larger, and this is already a multi-part answer):

SC  consecmths
F50  3
F55  2

Finally, to complete our preparations, here's an inner join to match the ServiceCodes table to the PersonUsage table, so that the number of consecutive months is available on each PersonUsage row:

select PersonUsage.Name 
     , PersonUsage.SC 
     , ServiceCodes.consecmths    as con
     , PersonUsage.UseDate
  from ServiceCodes
inner 
  join PersonUsage 
    on ServiceCodes.SC = PersonUsage.SC 
order by 1,2,4

This query produces the following results:

Name SC  con  UseDate
Joe  F50  3  2004-04-01
Joe  F50  3  2004-05-01
Joe  F50  3  2004-11-01
Joe  F50  3  2004-12-01
Joe  F50  3  2005-01-03
Joe  F55  2  2004-07-01
Joe  F55  2  2004-08-05
Tom  F50  3  2004-01-01
Tom  F50  3  2004-02-28
Tom  F50  3  2004-03-01
Tom  F50  3  2004-04-04

Tech note: Microsoft Access sometimes throws up "Join condition not supported" errors, but you can often find a workaround. In this case, it is necessary to save the above query as SC_PersonUsage_q. But that's okay, because it lets us focus on the theta join.

select X.Name 
     , X.SC 
     , X.consecmths    as con
     , X.UseDate
     , year(X.UseDate)*12 + month(X.UseDate)  as Xm
     , Y.UseDate
     , year(Y.UseDate)*12 + month(Y.UseDate)  as Ym
     , ( year(X.UseDate)*12 + month(X.UseDate) 
       - year(Y.UseDate)*12 - month(Y.UseDate) )  as diff
  from SC_PersonUsage_q as X
inner 
  join PersonUsage as Y
    on X.Name = Y.Name
   and X.SC = Y.SC 
   and ( year(X.UseDate)*12 + month(X.UseDate) 
       - year(Y.UseDate)*12 - month(Y.UseDate) ) >= 0
order 
    by 1,2,4,6

Whew! Well, let's take it a bit at a time.

First, keep in mind the inner join of ServiceCodes to PersonUsage that occurs within the SC_PersonUsage_q query, for the purpose of making the consecutive months value available in the SELECT list.

Next, look at the join of SC_PersonUsage_q with the PersonUsage table. This is essentially a self-join of the PersonUsage table, hence the X and Y aliases. You can see these aliases being used in the SELECT list, to calculate "month number" values Xm and Ym for the X and Y dates, and a "difference" value between the X and Y month numbers. You'll see why in a moment.

The PersonUsage self-join is on matching Name and SC, and then a condition that the "difference" number be greater than or equal to zero. Because of this inequality, this is an example of a theta join. (Each Name/SC combination has a "triangle" like in the earlier illustration.)

This will be a lot easier to understand once we examine the data returned by this query.

Please proceed to N consecutive rows, Part 3 of 4.