Problem solve Get help with specific problems with your technologies, process and projects.

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.

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 last published in September 2004

Dig Deeper on Oracle and SQL

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.