All Forums Analytics
sri67 3 posts Joined 06/16
08 Jun 2016
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.

 
 

Rgds, sri67
dnoeth 4628 posts Joined 11/04
11 Jun 2016

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

 

Dieter

sri67 3 posts Joined 06/16
27 Jun 2016

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

Rgds,
sri67

dnoeth 4628 posts Joined 11/04
27 Jun 2016

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

 

Dieter

sri67 3 posts Joined 06/16
28 Jun 2016

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

Rgds,
sri67

You must sign in to leave a comment.