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

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.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchHRSoftware

Close