We need to know when a service code has been used for a person for N consecutive months.
By submitting your email address, you agree to receive emails regarding relevant topic offers from TechTarget and its partners. You can withdraw your consent at any time. Contact TechTarget at 275 Grove Street, Newton, MA.
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.
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.