How do we read the next row to perform some operation on before one?

Database
Highlighted
Teradata Employee

How do we read the next row to perform some operation on before one?

Hi Forum,

 

How do we read the next row to perform some operation on current one?

Lets say input table is as below,

 

date        ename    mname
18-03-2018 emp1 manager1
17-04-2018 emp1 manager2
15-05-2018 emp1 manager3
22-05-2018 emp1 manager4
28-06-2018 emp1 manager5

 

I need output as below format,

 

startdate enddate ename mname

18-03-2018 16-04-2018 emp1 manager1

17-04-2018 14-05-2018 emp1 manager2

15-05-2018 21-05-2018 emp1 manager3

22-05-2018 27-06-2018 emp1 manager4

28-06-2018                    emp1 manager5

 

 

Any ideas? 

 

Best Regards,

Sandeep.

 

GANGA SANDEEP KUMAR

 

 

 


Accepted Solutions
Teradata Employee

Re: How do we read the next row to perform some operation on before one?

Hi Sandeep,

 

From 16.10+ you can use the LEAD function, otherwise you can use MIN / MAX analtyics functions with 1 following.

Datas

 

create multiset volatile table mvt_datas, no log
( dt    date
, ename  char(4)
, mname char(8)
)
primary index (ename)
on commit preserve rows;

insert into mvt_datas values (date '2018-03-18', 'emp1', 'manager1');
insert into mvt_datas values (date '2018-04-17', 'emp1', 'manager2');
insert into mvt_datas values (date '2018-05-15', 'emp1', 'manager3');
insert into mvt_datas values (date '2018-05-22', 'emp1', 'manager4');
insert into mvt_datas values (date '2018-06-28', 'emp1', 'manager5');

collect stats column (ename) on mvt_datas;

Queries

-- 16.10+
select dt as start_date
     , lead(dt) over(partition by ename order by dt asc) - 1 as end_date
     , ename, mname
  from mvt_datas;

-- otherwise  
select dt as start_date
     , min(dt) over(partition by ename order by dt asc rows between 1 following and 1 following) - 1 as end_date
     , ename, mname
  from mvt_datas;

-- results
start_date  end_date    ename  mname
----------  ----------  -----  --------
2018-03-18  2018-04-16  emp1   manager1
2018-04-17  2018-05-14  emp1   manager2
2018-05-15  2018-05-21  emp1   manager3
2018-05-22  2018-06-27  emp1   manager4
2018-06-28              emp1   manager5

 

 

 

 

1 ACCEPTED SOLUTION
2 REPLIES 2
Teradata Employee

Re: How do we read the next row to perform some operation on before one?

Hi Sandeep,

 

From 16.10+ you can use the LEAD function, otherwise you can use MIN / MAX analtyics functions with 1 following.

Datas

 

create multiset volatile table mvt_datas, no log
( dt    date
, ename  char(4)
, mname char(8)
)
primary index (ename)
on commit preserve rows;

insert into mvt_datas values (date '2018-03-18', 'emp1', 'manager1');
insert into mvt_datas values (date '2018-04-17', 'emp1', 'manager2');
insert into mvt_datas values (date '2018-05-15', 'emp1', 'manager3');
insert into mvt_datas values (date '2018-05-22', 'emp1', 'manager4');
insert into mvt_datas values (date '2018-06-28', 'emp1', 'manager5');

collect stats column (ename) on mvt_datas;

Queries

-- 16.10+
select dt as start_date
     , lead(dt) over(partition by ename order by dt asc) - 1 as end_date
     , ename, mname
  from mvt_datas;

-- otherwise  
select dt as start_date
     , min(dt) over(partition by ename order by dt asc rows between 1 following and 1 following) - 1 as end_date
     , ename, mname
  from mvt_datas;

-- results
start_date  end_date    ename  mname
----------  ----------  -----  --------
2018-03-18  2018-04-16  emp1   manager1
2018-04-17  2018-05-14  emp1   manager2
2018-05-15  2018-05-21  emp1   manager3
2018-05-22  2018-06-27  emp1   manager4
2018-06-28              emp1   manager5

 

 

 

 

Teradata Employee

Re: How do we read the next row to perform some operation on before one?

Hi.

 

BTEQ -- Enter your SQL request or BTEQ command:
SELECT * FROM TheTable ORDER BY TheDate;


*** Query completed. 5 rows found. 3 columns returned.
*** Total elapsed time was 1 second.

TheDate    ename                     mname
---------- ------------------------- -------------------------
2018-03-18 emp1                      manager1
2018-04-17 emp1                      manager2
2018-05-15 emp1                      manager3
2018-05-22 emp1                      manager4
2018-06-28 emp1                      manager5

BTEQ -- Enter your SQL request or BTEQ command:
SELECT TheDate, MIN(TheDate) OVER (partition by ename ORDER BY TheDate ROWS BETWEEN 1 FOLLOWING AND 1 FOLLOWING) -1 EndDate, ename, mname FROM TheTable;


*** Query completed. 5 rows found. 4 columns returned.
*** Total elapsed time was 1 second.

TheDate    EndDate    ename                     mname
---------- ---------- ------------------------- -------------------------
2018-03-18 2018-04-16 emp1                      manager1
2018-04-17 2018-05-14 emp1                      manager2
2018-05-15 2018-05-21 emp1                      manager3
2018-05-22 2018-06-27 emp1                      manager4
2018-06-28 (null)     emp1                      manager5

 

Cheers.

 

Carlos.