Dataphor SQL RAC (Relational Application Companion)


A site of hope for those looking for a true relational database system

Wednesday, August 16, 2006

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'?:)

29 comments:

Anonymous said...

xanax online xanax for anxiety during pregnancy - buy xanax online with a prescription

Anonymous said...

xanax online xanax effects drug - xanax street price australia

Anonymous said...

generic xanax side effects of xanax 0.25mg - xanax side effects nausea headache

Anonymous said...

generic tramadol cheap tramadol no prescription needed - tramadol withdrawal percocet

Anonymous said...

generic carisoprodol carisoprodol 350 mg shelf life - carisoprodol y acetaminofen

Anonymous said...

xanax online does generic xanax look like - xanax and drug testing erowid

Anonymous said...

buy tramadol online best way buy tramadol online - buy tramadol online overnight

Anonymous said...

buy tramadol online tramadol addiction egypt - buy tramadol 180

Anonymous said...

buy tramadol online neurontin and tramadol high - tramadol 50 mg bluelight

Anonymous said...

carisoprodol 350 mg carisoprodol 125mg para que serve - carisoprodol barbiturate

Anonymous said...

buy tramadol online tramadol for dogs vs people - tramadol addictive or not

Anonymous said...

cialis online small order cialis - buy cheap cialis line

Anonymous said...

buy cialis online generic cialis online reviews - buy generic cialis online

Anonymous said...

order tadalafil buy cialis 20 mg online - where to buy cialis safely online

Anonymous said...

buy cialis online order cialis online no prescription - low dose cialis online

Anonymous said...

cialis 10mg going price cialis - generic cialis price comparison

Anonymous said...

buy tramadol tramadol for dogs no rx - tramadol 50 mg buy online

Anonymous said...

buy tramadol online reviews tramadol 50mg - tramadol addiction in dogs

Anonymous said...

03 prednisone medication - prednisone no prescription http://www.prednisoneonlinerx.net/index.html, [url=http://www.prednisoneonlinerx.net/index.html]prednisone online [/url]

Anonymous said...

http://buytramadolonlinecool.com/#50897 tramadol hcl 50 mg tab side effects - how to buy tramadol online overnight

Anonymous said...

4, finasteride online - cheap finasteride 5mg http://www.propeciasaleonline.net/#propecia-cost, [url=http://www.propeciasaleonline.net/#cheap-propecia-5mg]propecia cost[/url]

Anonymous said...

1, order lasix - lasix drug http://www.lasixordernow.net/#lasix-cost, [url=http://www.lasixordernow.net/#lasix-online]cheap furosemide[/url]

Anonymous said...

all, buy sumatriptan online - imitrex for sale http://itrafficgeneration.com/#imitrex-for-sale , [url=http://itrafficgeneration.com/#imitrex-without-prescription ]imitrex without prescription [/url]

Anonymous said...

ooo, generic lexapro online - lexapro cost http://www.jangdailynews.com/, [url=http://www.jangdailynews.com/]lexapro for sale [/url]

Anonymous said...

ways to buy ativan online buy ativan philippines - ativan euphoria

Anonymous said...

ativan for anxiety ativan withdrawal opiates - ativan vs xanax and valium

Anonymous said...

ativan lorazepam ativan vs xanax for insomnia - ativan dosage in elderly

Anonymous said...

buy tramadol online tramadol 50mg grasscity - tramadol 50mg for dogs and humans

Anonymous said...

http://bayshorechryslerjeep.com/#4038 xanax bars called - klonopin versus xanax for anxiety

About Me

My photo
Phoenix, Arizona, United States