RAC - Sealed with a KISS

Here's a recent question posted in an MS Sql Server newsgroup:

I am trying to formulate a SELECT statement to query the following
table:

CREATE TABLE dbo.PART_TABLE(
  PART_ID VARCHAR(12) NOT NULL
, JOB_NUMBER VARCHAR(12) NOT NULL
, ENTRY_DATE DATETIME NOT NULL DEFAULT GETDATE()
, TYPE_CODE VARCHAR(12) NOT NULL
, PRIMARY KEY (PART_ID, JOB_NUMBER, ENTRY_DATE)
)


What I want to do is to return all rows of the table where the
TYPE_CODE is the same for two consecutive ENTRY_DATEs, for each
PART_ID.

The PART_IDs will not necessarily have a daily entry, so the
'consecutive' dates may actually be a few days apart.

In other words, I want to know the rows where a PART_ID had the same
TYPE_CODE on the chronologically previous ENTRY_DATE as the next.

So, if my table contained:

-------------------------------------------------
1111       30       01/06/06      ZXY
2222       40       01/06/06      ZXY
2222       35       03/06/06      ABC
1111       23       03/06/06      ZXY
3333       87       02/06/06      ABC
---------------------------------------------------

the query would return:
1111       30       01/06/06      ZXY
1111       23       03/06/06      ZXY

as the same TYPE_CODE was used on two consecutive ENTRY_DATEs for
PART_ID '1111'.

A number of very bright sql programmers proposed solutions.
Unfortunately you would need to be an extremely sophisticated
sql expert to understand them. There is a point at which the 
code completely obfuscates the logic of the solution.
Now I'm an advocate of KISS (keep it simple stupid). This was
one of the major motivations for developing the RAC utility.
Questions like the one above can be kissed very easily if
framed properly. At its simplist this problem can be solved
by sorting and computing a few ranks based on the sort.

Here is some same data:

insert PART_TABLE values('1111',30,'01/06/06','ZXY')
insert PART_TABLE values('2222',40,'01/06/06','ZXY')
insert PART_TABLE values('2222',35,'03/06/06','ABC')
insert PART_TABLE values('1111',23,'03/06/06','ZXY')
insert PART_TABLE values('3333',87,'02/06/06','ABC')
insert PART_TABLE values('1111',21,'03/10/06','ZXY')
insert PART_TABLE values('2222',23,'03/21/06','ABC')
insert PART_TABLE values('3333',24,'02/16/06','HUH')
insert PART_TABLE values('2222',27,'01/04/06','ABC')

Here is the data sorted in RAC without any other processing.
Sorting by the sequence of PART_ID, ENTRY_CODE and TYPE_CODE
encapsulates the whole solution.

Exec Rac
@transform='_dummy_',
@rows='PART_ID & TYPE_CODE & ENTRY_DATE(date) & JOB_NUMBER',
@rowsort='PART_ID & ENTRY_DATE & TYPE_CODE',  
@pvtcol='Report Mode',
@from='PART_TABLE',@rowbreak='n',@defaults1='y',
@defaultexceptions='dumy',@racheck='y'

PART_ID TYPE_CODE ENTRY_DATE JOB_NUMBER 
------- --------- ---------- ---------- 
1111    ZXY       01/06/06   30
1111    ZXY       03/06/06   23
1111    ZXY       03/10/06   21
2222    ABC       01/04/06   27
2222    ZXY       01/06/06   40
2222    ABC       03/06/06   35
2222    ABC       03/21/06   23
3333    ABC       02/06/06   87
3333    HUH       02/16/06   24

Now we're going to put a counter/rank on 
TYPE_CODE within PART_ID called TYPERANK.
Every time PART_ID changes or TYPE_CODE within
PART_ID changes the rank is set back to 1.
Otherwise the rank increments by 1. We're also
going to add a first/last indicator for the rank
called LASTYPE. If the rank is not the last combination
of PART_ID/TYPE_CODE it's 0, if its the last a 1.

Exec Rac
@transform='_dummy_',
@rows='PART_ID & TYPE_CODE & ENTRY_DATE(date) & JOB_NUMBER',
@rowsort='PART_ID & ENTRY_DATE &  TYPE_CODE',  
@pvtcol='Report Mode',
@from='PART_TABLE',@rowbreak='n',@defaults1='y',
@defaultexceptions='dumy',@racheck='y',
@rowcounters='TYPE_CODE{TYPERANK}',
@lastcounters='TYPE_CODE{LASTYPE}'

PART_ID TYPE_CODE ENTRY_DATE JOB_NUMBER TYPERANK LASTYPE 
------- --------- ---------- ---------- -------- ------- 
1111    ZXY       01/06/06   30         1        0
1111    ZXY       03/06/06   23         2        0
1111    ZXY       03/10/06   21         3        1
2222    ABC       01/04/06   27         1        1
2222    ZXY       01/06/06   40         1        1
2222    ABC       03/06/06   35         1        0
2222    ABC       03/21/06   23         2        1
3333    ABC       02/06/06   87         1        1
3333    HUH       02/16/06   24         1        1

Now using TYPERANK and LASTYPE it's easy to pick off
the 1st 2 consecutive rows for PART_ID and TYPE_CODE.
The @wherecounters parameter makes this simple test.
Conceptually all this is done with a single pass thru
the data.

Exec Rac
@transform='_dummy_',
@rows='PART_ID & TYPE_CODE & ENTRY_DATE(date) & JOB_NUMBER',
@rowsort='PART_ID & ENTRY_DATE &  TYPE_CODE',  
@pvtcol='Report Mode',
@from='PART_TABLE',@rowbreak='n',@defaults1='y',
@defaultexceptions='dumy',@racheck='y',
@rowcounters='TYPE_CODE{TYPERANK}',
@lastcounters='TYPE_CODE{LASTYPE}',
@wherecounters='LASTYPE=0 or TYPERANK=2'

PART_ID TYPE_CODE ENTRY_DATE JOB_NUMBER TYPERANK LASTYPE 
------- --------- ---------- ---------- -------- ------- 
1111    ZXY       01/06/06   30         1        0
1111    ZXY       03/06/06   23         2        0
2222    ABC       03/06/06   35         1        0
2222    ABC       03/21/06   23         2        1

Kiss/RAC which also stands for less is more.

Hopefully this has been helpful. Or are we back to 'huh'?:)