Help extracting select time period records ( rank / any other way)

Analytics
Fan

Help extracting select time period records ( rank / any other way)

Have the following records,

COLA COLB COLC 

AA   11   10:00:00

AA   11   10:01:00

AA   12   10:01:20

BB   11   10:00:00

BB   11   10:02:00

BB   11   10:03:10

BB   12   10:03:35

AA   11   11:00:00

AA   11   11:01:00

AA   11   10:02:00

AA   12   10:02:40

DD   11   10:02:40

DD   11   10:03:14

CC   11   10:05:00

CC   12   10:05:15

CC   11   11:10:00

CC   12   11:10:12

DD   12   11:12:24

Need records for same COLA, COLB 11 latest followed by 12 with time difference between those 2 records is less than 30 secs. Need to report multiple occurence for the same COLA too (rec 4 of output).

Output

AA 11 12 10:01:00 10:01:20 20

BB 11 12 10:03:10 10:03:35 25

CC 11 12 10:05:00 10:05:15 15

CC 11 12 11:10:00 11:10:12 12

Appreciate any help in this aspect.

4 REPLIES
Senior Apprentice

Re: Help extracting select time period records ( rank / any other way)

You simply need to check the next row's values:

select COLA, COLB, COLC, 
min(COLB) over (partition by COLA order by COLC) as nextCOLB,
min(COLC) over (partition by COLA order by COLC) as nextCOLC
from tab
qualify COLB = 11
and nextCOLB = 12
and nextCOLC <= COLC + interval '30' second
Fan

Re: Help extracting select time period records ( rank / any other way)

Thanks Dieter for the help and sorry to have responded back too late.

I think the requirement specified by me was not clear and the solution specified do not return any rows. The nextColb has always 11 as it is the min value.

The output should be,

COLA   COLB(MIN)  COLB(MAX)   COLB(MIN)_TIME    COLB(MAX)_TIME  TIME_DIFF_SECS

AA          11               12             10:01:00               10:01:20                 20

BB          11               12             10:03:10               10:03:35                 25

CC          11               12             10:05:00               10:05:15                 15

CC          11               12             11:10:00               11:10:12                 12

Senior Apprentice

Re: Help extracting select time period records ( rank / any other way)

Ops, the ROWS part was missing: 

select COLA, COLB, COLC, 
min(COLB)
over (partition by COLA
order by COLC
rows between 1 following and 1 following) as nextCOLB,
min(COLC)
over (partition by COLA
order by COLC
rows between 1 following and 1 following) as nextCOLC
from tab
qualify COLB = 11
and nextCOLB = 12
and nextCOLC <= COLC + interval '30' second
Fan

Re: Help extracting select time period records ( rank / any other way)

Thanks for your quick response. It worked. As I am new to Teradata, your help keeps me going and have a attitude to learn new.

Appreciate your help