Hello all,

DB2 v9.5 LUW Fixpack 5.

create table TESTE (
MACHINE_ID VARCHAR (24),
LOGIN_NAME VARCHAR (32),
EVENT_TIME TIMESTAMP,
OPERATION CHAR (1) );

insert into TESTE values
('00000000001007B70BCB1546','wiocpere','2011-05-16-08.01.49.000000','I'),
('00000000001007B70BCB1546','wiocpere','2011-05-16-08.47.49.000000','U'),
('00000000001007B70BCB1546','wiocpere','2011-05-16-09.23.41.000000','I'),
('00000000001007B70BCB1546','wiocpere','2011-05-16-16.17.49.000000','U'),
('00000000001007B70BCB1546','wiocpere','2011-05-17-07.11.41.000000','I'),
('00000000001007B70BCB1546','wiocpere','2011-05-17-08.47.49.000000','U'),
('00000000001007B70BCB1546','wiocpere','2011-05-18-09.23.41.000000','I');


My application needs detect when a completed event happened
(Instalation / Uninstalation ).

Ex.

MACHINE_ID LOGIN_NAME
EVENT_TIME OPERATION
------------------------ ------------------------------
-------------------------- ---------
00000000001007B70BCB1546 wiocpere
2011-05-16-08.01.49.000000 I
00000000001007B70BCB1546 wiocpere
2011-05-16-08.47.49.000000 U
00000000001007B70BCB1546 wiocpere
2011-05-16-09.23.41.000000 I
00000000001007B70BCB1546 wiocpere
2011-05-16-16.17.49.000000 U
00000000001007B70BCB1546 wiocpere
2011-05-17-07.11.41.000000 I
00000000001007B70BCB1546 wiocpere
2011-05-17-08.47.49.000000 U
00000000001007B70BCB1546 wiocpere
2011-05-18-09.23.41.000000 I



EVENT_TIMEs ( 2011-05-16-08.01.49.000000 and
2011-05-16-08.47.49.000000 ) is a completed event. (Installation and
Uninstallation ).
EVENT_TIMEs ( 2011-05-16-09.23.41.000000 and
2011-05-16-16.17.49.000000 ) is a completed event. (Installation and
Uninstallation ).
EVENT_TIMEs ( 2011-05-17-07.11.41.000000 and
2011-05-17-08.47.49.000000 ) is a completed event. (Installation and
Uninstallation ).
EVENT_TIME ( 2011-05-18-09.23.41.000000 ) is a incompleted event
( Just installation ).


I need return, this result set:

MACHINE_ID LOGIN_NAME
EVENT_TIME_INST OPERATION EVENT_TIME_UNINST
OPERATION COMPLETED
------------------------ ------------------------------
-------------------------- --------- --------------------------
--------- ----------
00000000001007B70BCB1546 wiocpere
2011-05-16-08.01.49.000000 I 2011-05-16-08.47.49.000000
U Y
00000000001007B70BCB1546 wiocpere
2011-05-16-09.23.41.000000 I 2011-05-16-16.17.49.000000
U Y
00000000001007B70BCB1546 wiocpere
2011-05-17-07.11.41.000000 I 2011-05-17-08.47.49.000000
U Y
00000000001007B70BCB1546 wiocpere
2011-05-17-09.23.41.000000 I -
- N


Is there a way to get this result set?

Thanks,

Bruno.

On 2011-05-26 18:55, brunoalsantos wrote:
Definitely so. If we can assume that I and U operation always come in
pairs (i.e. no consecutive inserts for example):

select MACHINE_ID, LOGIN_NAME, EVENT_TIME_INST, EVENT_TIME_UNINST,
case when EVENT_TIME_UNINST is null
then 'N' else 'Y' end as status, operation
from (
select MACHINE_ID, LOGIN_NAME, EVENT_TIME as EVENT_TIME_INST,
lead(EVENT_TIME) over (partition by MACHINE_ID
order by EVENT_TIME
) as EVENT_TIME_UNINST,
OPERATION from teste
) as x
where operation = 'I';

MACHINE_ID LOGIN_NAME
EVENT_TIME_INST EVENT_TIME_UNINST STATUS OPERATION
------------------------ --------------------------------
-------------------------- -------------------------- ------ ---------
00000000001007B70BCB1546 wiocpere
2011-05-16-08.01.49.000000 2011-05-16-08.47.49.000000 Y I
00000000001007B70BCB1546 wiocpere
2011-05-16-09.23.41.000000 2011-05-16-16.17.49.000000 Y I
00000000001007B70BCB1546 wiocpere
2011-05-17-07.11.41.000000 2011-05-17-08.47.49.000000 Y I
00000000001007B70BCB1546 wiocpere
2011-05-18-09.23.41.000000 - N I

4 record(s) selected.


It should not be that difficult to solve with the consecutive
restriction removed either, but then the problem need to be further defined.


/Lennart