Saturday, January 5, 2008

..and if you didnt believe DBAs are NOSY



I have been a fan of Morgen's articles. But their latest myth-buster on "oracle installation is tedious and complex" is delightfully funny to watch.

True oracle-ities are real "nosy" creatures, indeed. :)

Friday, January 4, 2008

Can you solve this?

I did the worst thing to myself just before the start of holidays..thats by reading some sql question somewhere on the list and jumping in to solve it. (that was complex enough that it was actually reserved to be asked to the Oracle guru Tom Kyte)

Obviously over the holidays I didnt get too much time..but with whatever little I could work, I havent been able to accomplish much.

Just posting the question for those like-minded souls who get a kick out of solving everything in SQL.




SQL> col id format 99
SQL> col customer_id format 99 heading CID
SQL> col service_name format a12 trunc
SQL> col start_date format a12
SQL> col end_date format a12

SQL> desc sql_test
Name Null? Type
----------------------------------------- -------- --------------
ID NOT NULL NUMBER(38)
CUSTOMER_ID NUMBER(10)
SERVICE_NAME VARCHAR2(100)
START_DATE DATE
END_DATE DATE

SQL> select * from sql_test ;

ID CID SERVICE_NAME START_DATE END_DATE
--- --- ------------ ------------ ------------
1 1 Service-S2 01-AUG-07 05-AUG-07
2 1 Service-S1 02-AUG-07 01-NOV-07
3 1 Service-S3 07-AUG-07 30-AUG-07
4 1 Service-S4 07-AUG-07 30-NOV-07
5 1 Service-S5 02-DEC-07 20-DEC-07



assume thats a table with customer info..what we need to output is the period during which customer had continued service..(doesnt matter what the service is..but as long as its continous..we need to consider it one row)

so the output should be something like this

CID START_DATE END_DATE
--- ------------ ------------
1 01-AUG-07 30-NOV-07
1 02-DEC-07 20-DEC-07



PS: extend your solution to work for many other customers too (the sample is only for one customer_id (identified as 1)

Even though I havent solved this yet (well I did, but only to find if I change the data..it fails :) )

(the only good thing so far is..I never worked with MODEL clause before..now I have extensively researched that to solve this..thats a hint hint)