We need to know when a service code has been used for a person for N consecutive months. The day part of the date...
By submitting your personal information, you agree that TechTarget and its partners may contact you regarding relevant content, products and special offers.
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.
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.
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.