Ask the Expert

N consecutive rows, Part 2 of 4

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.


    Requires Free Membership to View

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

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to: