sql question

[wsam]

I am trying to create a sql query where I subtract one date from another.
examples of the data are:

occnt segname date
2 OUTSER 08/09/02 06:18
3 REMINQ 08/09/02 06:32
8 OUTSER 09/21/02 08:06
9 LOGOFF 09/21/02 08:14
11 OUTSER 10/10/02 21:51
12 CLEAR 10/10/02 21:52

What I am trying to do is subtract the OUTSER date from the segmentname right after it. As you can see from the data, the segment name after the OUTSER can be different in each case.
Any help would be appreciated. Thanks

 

[blom0344]

Assuming there is always 1 between the values of occnt and the day-part the same in the substraction something like:

SELECT OCCNT,SEGNAME,DATE,DATE_2,TO_NUMBER(TO_CHAR(DATE,'SSSSS')),
TO_NUMBER(TO_CHAR(DATE_2,'SSSSS')),
TO_NUMBER(TO_CHAR(DATE,'SSSSS')) - TO_NUMBER(TO_CHAR(DATE_2,'SSSSS')) AS DIIF_IN_SEC FROM
(SELECT OCCNT+1 AS OCCNT_2, DATE AS DATE_2 FROM TABLE
WHERE SEGNAME = 'OUTSER') TEMP,TABLE
WHERE OCCNT = OCCNT_2

The syntax is probably not correct (being in a hurry), but the idea is to create a temp set storing the data on the 'OUTSER' records and joining a second set through OCCNT_2 which stores the next record data.

It is not a total solution, but it may give you some idea's

T. Blom
Information analyst
tbl@shimano-eu.com

 

[porcher]

Hi
First I think date is a reserved word, I think you must change the name of the column (dat for example).
I suppose occnt is the primary key og the table.

To extract what you want, try :

If occnt increases in the time:

select a.occnt,a.segname,a.date,b.date - a.date
from tabl a, tabl b
where b.occnt =
(select min(c.occnt)
from tabl c
where c.occnt > a.occnt);

If occnt does not increase in time :

select a.occnt,a.segname,a.date,b.date - a.date
from tabl a, tabl b
where b.date =
(select min(c.date)
from tabl c
where c.date > a.date);

The expression b.date - a.date gives you a number of days. I think you must transform it in seconds, minutes,...