N consecutive rows, Part 2 of 4

Digg This!     StumbleUpon     Del.icio.us

RELATED RESOURCES 2020software.com, trial software downloads for accounting software, ERP software, CRM software and business software systems Search Bitpipe.com for the latest white papers and business webcasts Whatis.com, the online computer dictionary

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.