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

N consecutive rows, Part 1 of 4

We need to know when a service code has been used for a person for N consecutive months. The day part of the date...

may not be the same from month to month (eg. 7/1 and 8/5), so we're not sure if DateAdd will work. Also the N part of the N consecutive months is different for each service code (e.g. some are every four months, some are every six months). We have a table of entries with Name, SC, and UseDate, which looks like:

Joe F55 7/1/04 Joe F55 8/5/04

This would be two consecutive months. We also have a table of ServiceCodes and the number of consecutive months to look for, which looks like:

F55 6 F50 4

We are working in Access, and need to make a report listing names and codes that exceed the number of consecutive months. We want to base the report on a query, but are not sure how to write the query.


Great question!

The following solution involves a theta self-join. If that sounds strange, we will break it down, take it slowly, and see how it works from the ground up.

Before getting to the SQL, let's do a quick review of joins. Consider the numbers 1 through 9. Join these numbers to themselves, and you get 9 * 9 = 81 different combinations. That's the typical cross join we're all familiar with. It's also a self-join. In fact, a self-join can be a cross join, inner join, or any other type of join. In this case, we're interested primarily in a certain type of inner join.

In the following diagram, there's an X where the value down the left is equal to the value across the top. This illustrates an equi-join:

 1 2 3 4 5 6 7 8 9 1 X . . . . . . . . 2 . X . . . . . . . 3 . . X . . . . . . 4 . . . X . . . . . 5 . . . . X . . . . 6 . . . . . X . . . 7 . . . . . . X . . 8 . . . . . . . X . 9 . . . . . . . . x

An equi-join is a special type of inner join. It uses equality of values as the join condition. The cross join, by comparison, is also a type of inner join, but it has no join condition, so every value is joined with every value, and there would be an X in every spot in that diagram.

A theta join uses some other condition, not equality, in order to join rows. In the following diagram, there's an X where the value down the left is equal to or greater than the value across the top. This "triangle" appearance illustrates a theta join:

 1 2 3 4 5 6 7 8 9 1 X . . . . . . . . 2 X X . . . . . . . 3 X X X . . . . . . 4 X X X X . . . . . 5 X X X X X . . . . 6 X X X X X X . . . 7 X X X X X X X . . 8 X X X X X X X X . 9 X X X X X X X X X

In the following diagram, there's an X where the value down the left is equal to or greater than the value across the top, but not greater than 2 greater. The triangle has been "cut off" and so this illustrates a different theta join:

 1 2 3 4 5 6 7 8 9 1 X . . . . . . . . 2 X X . . . . . . . 3 X X X . . . . . . 4 . X X X . . . . . 5 . . X X X . . . . 6 . . . X X X . . . 7 . . . . X X X . . 8 . . . . . X X X . 9 . . . . . . X X X

In general, there are a zillion ways you can construct a theta join. We'll be using a version of the theta join in the last diagram.

Please proceed to N consecutive rows, Part 2 of 4.


This was last published in September 2004

Dig Deeper on Oracle and SQL

PRO+

Content

Find more PRO+ content and other member only offers, here.

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.

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

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close