Q

N consecutive rows, Part 4 of 4

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

Please see N consecutive rows, Part 3 of 4 for the explanation of the development of the theta self-join to this point.

Now the magic happens. We add a GROUP BY to the query, and group on Name, SC, and X date. This corresponds to the spacing we've been using in the display of query results. We simply count the number of rows in each group, and this count must be equal to the desired number of consecutive months for that SC.

Perhaps it will be easier to see if we just highlight the data in the detail rows prior to grouping.

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-11-01 24059 0 
  
 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-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-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

Here, the bolded data corresponds to what will come out of the query after the GROUP BY and HAVING are applied.

select X.Name , X.SC , X.con , X.UseDate 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 and ( year(X.UseDate)*12 + month(X.UseDate) - year(Y.UseDate)*12 - month(Y.UseDate) ) < X.con group by X.Name , X.SC , X.con , X.UseDate having count(*) = X.con order by 1,2,4

The results are:

Name SC con X.UseDate Joe F50 3 2005-01-03 Joe F55 2 2004-08-05 Tom F50 3 2004-03-01 Tom F50 3 2004-04-04

Notice how Tom's F50 was flagged in two consecutive months. Tom used F50 for January through April, so it was flagged in March and then again in April. The theta join reports every date whenever it corresponds to the correct number of consecutive months for that date, for that service code.

Pretty neat, eh?


This was first published in September 2004

Dig deeper on Oracle and SQL

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

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.

0 comments

Oldest 

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:

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close