Ask the Expert

N consecutive rows, Part 3 of 4

We need to know when a service code has been used for a person for N consecutive months.

    Requires Free Membership to View

Please see N consecutive rows, Part 2 of 4 for the explanation of the theta self-join which produced the following results:

Name SC con X.UseDate Xm Y.UseDate Ym diff 
 Joe F50 3 2004-04-01 24052 2004-04-01 24052 0 
 Joe F50 3 2004-05-01 24053 2004-04-01 24052 1 Joe F50 3 2004-05-01 24053 2004-05-01 24053 0 

 Joe F50 3 2004-11-01 24059 2004-04-01 24052 7 Joe F50 3 2004-11-01 24059 2004-05-01 24053 6 Joe F50 3 2004-11-01 24059 2004-11-01 24059 0 
 Joe F50 3 2004-12-01 24060 2004-04-01 24052 8 Joe F50 3 2004-12-01 24060 2004-05-01 24053 7 Joe F50 3 2004-12-01 24060 2004-11-01 24059 1 Joe F50 3 2004-12-01 24060 2004-12-01 24060 0 
 Joe F50 3 2005-01-03 24061 2004-04-01 24052 9 Joe F50 3 2005-01-03 24061 2004-05-01 24053 8 Joe F50 3 2005-01-03 24061 2004-11-01 24059 2 Joe F50 3 2005-01-03 24061 2004-12-01 24060 1 Joe F50 3 2005-01-03 24061 2005-01-03 24061 0 
 Joe F55 2 2004-07-01 24055 2004-07-01 24055 0 
 Joe F55 2 2004-08-05 24056 2004-07-01 24055 1 Joe F55 2 2004-08-05 24056 2004-08-05 24056 0 

 Tom F50 3 2004-01-01 24049 2004-01-01 24049 0 
 Tom F50 3 2004-02-28 24050 2004-01-01 24049 1 Tom F50 3 2004-02-28 24050 2004-02-28 24050 0 

 Tom F50 3 2004-03-01 24051 2004-01-01 24049 2 Tom F50 3 2004-03-01 24051 2004-02-28 24050 1 Tom F50 3 2004-03-01 24051 2004-03-01 24051 0 
 Tom F50 3 2004-04-04 24052 2004-01-01 24049 3 Tom F50 3 2004-04-04 24052 2004-02-28 24050 2 Tom F50 3 2004-04-04 24052 2004-03-01 24051 1 Tom F50 3 2004-04-04 24052 2004-04-04 24052 0

Please examine this data carefully, until you are clear on how the self-join works. Note that because it's a theta join, each X date is joined to one or more Y dates.

The rows are spaced out a bit, so that all the rows for the same X date are grouped together. Look at Xm, the month number for this date, and compare it to the Ym's for the rows in each grouping. Observe how the difference number shows the difference in the month numbers between the Xm and Ym for each row.

Now that we've seen the rows produced by the theta join, let's start to refine the query to meet the original requirements.

The theta join started with a condition that the difference be greater than or equal to zero. Now we will add another condition, that the difference be less than the number of required consecutive months, which came from the ServiceCodes table. So the join conditions are:

 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 and ( year(X.UseDate)*12 + month(X.UseDate) - year(Y.UseDate)*12 - month(Y.UseDate) ) < X.con

This is still a theta join, but the triangles are "cut off" like the diagonal stripe in the diagram we saw earlier. You can visualize this by going through the results above and "throwing away" any row where the difference in the last column is not less than the con column representing required number of consecutive months.

We're almost done. The last step is to consolidate the remaining rows with a GROUP BY to produce the final results.

Please proceed to N consecutive rows, Part 4 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: