You state: >> no way in standard SQL to refer to "the value of this column from the previous row in the report".
At least in Oracle 8i SQL, there is a way in to refer to this, I'm sure it isn't standard, but useful nonetheless, and so I present it here.
It is called an Analytic Function. There are several, but the one demonstrated in this example is LAST_VALUE.
SELECT r1, r2, r2 - r1 reg_gap FROM (SELECT u1.update_date AS r1, LAST_VALUE (update_date) OVER (ORDER BY update_date ASC ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING) AS r2 FROM users u1 WHERE u1.user_id > 100000) WHERE r1 <> r2 ORDER BY r1
From the inside out, I take the update_date from the users table, and using the LAST_VALUE function, I ask for the last update_date value, including in the window the current row and the next chronologically ordered row.
I used a higher level query to do the difference simply to avoid repeating the long function, but I could have done it in one.
The results are the same:
"R1" "R2" "REG_GAP" 11/10/2003 5:19:00 PM 11/10/2003 8:23:24 PM 0.128055555555556 11/10/2003 8:23:24 PM 11/12/2003 7:53:10 AM 1.47900462962963 11/12/2003 7:53:10 AM 2/13/2004 3:44:47 PM 93.3275115740741
Although, as I said, I'm using 8i so I don't have the interval type.
To find out more about Analytic Functions, check out the Oracle Documentation SQL Reference.
-- K SF, September 1, 2004
"Some Profoundly Painful Things -- Calculating time intervals between rows in a table" is very useful, thank you. Some people may need the following technique to establish a sequential numeric identifier. (In the example you assume "user_id column, which we know to be sequential and unique")
declare @tmp (registration_date datetime)
select identity(int,1,1) as Sequence, registration_date into #x from users order by registration_date
... (now use #x instead of users in the example)
drop table #x
-- Steve Davis, January 29, 2006
You say: "Oops. Oracle pads some of these fields by default so that reports will be lined up and neat. We'll have to trim the strings ourselves." Not quite: one can use FM modifier in format string to instruct Oracle to trim whitespace from resulting string automatically, like this:SQL> select to_char(sysdate,'Day, Month DD, YYYY') from dual; TO_CHAR(SYSDATE,'DAY,MONTHDD, ----------------------------- Monday , May 22, 2006 SQL> select to_char(sysdate,'FMDay, Month DD, YYYY') from dual; TO_CHAR(SYSDATE,'FMDAY,MONTHD ----------------------------- Monday, May 22, 2006Note that FM is a switch - second FM in format string negates the effect of the first.
-- Vladimir Zakharychev, May 22, 2006
Not pretty at all but it works...CREATE OR REPLACE FUNCTION interval_to_seconds(x INTERVAL DAY TO SECOND ) RETURN NUMBER IS s VARCHAR2(26); days_s VARCHAR2(26); time_s VARCHAR2(26); N NUMBER(10,6); BEGIN s := TO_CHAR(x); days_s := SUBSTR(s,2,INSTR(s,' ')-2); time_s := SUBSTR(s,2+LENGTH(days_s)+1); N := 86400*TO_NUMBER(days_s) + 3600*TO_NUMBER(SUBSTR(time_s,1,2)) + 60*TO_NUMBER(SUBSTR(time_s,4,2)) + TO_NUMBER(SUBSTR(time_s,7)); IF SUBSTR(s,1,1) = '-' THEN N := - N; END IF; RETURN N; END;
-- Andre Mostert, June 20, 2006
1.Find the first monday on every quater based on date ?
Select Next_day(trunc(to_date(sysdate,'DD-MON-YYYY'), 'Q')-1,'Monday') from dual
-- Mohamed Kaleel, April 13, 2007
Computing number of seconds in an interval:
FUNCTION seconds_from_interval(invInterval IN INTERVAL DAY TO SECOND) RETURN NUMBER IS BEGIN
RETURN EXTRACT (DAY FROM invInterval) * 86400 +
EXTRACT (HOUR FROM invInterval) * 3600 +
EXTRACT (MINUTE FROM invInterval) * 60 +
EXTRACT (SECOND FROM invInterval);
-- Bob Jarvis, March 4, 2008