We need to know when a service code has been used for a person for N consecutive months.
Please see N consecutive rows, Part 1 of 4 for the problem statement.
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.
This was first published in September 2004