How to get formatted output using Preceding and Following?

Database
The Teradata Database channel includes discussions around advanced Teradata features such as high-performance parallel database technology, the optimizer, mixed workload management solutions, and other related technologies.

How to get formatted output using Preceding and Following?

 

My Input:

Ordnum Itemrevid Itmhistid TSStatus 
ABC123177,149,909183,403,22529/05/2018 14:31Initial
ABC123177,149,909183,403,26929/05/2018 14:32Initial
ABC123177,149,909183,403,27429/05/2018 14:32Transmitted
ABC123177,149,909183,404,28329/05/2018 14:40In Progress
ABC123177,149,909183,404,29129/05/2018 14:40In Progress
ABC123177,149,909183,407,43729/05/2018 15:08In Progress
ABC123177,149,909183,407,44229/05/2018 15:08In Progress
ABC123177,149,909183,409,16029/05/2018 15:23In Progress
ABC123177,149,909183,409,16629/05/2018 15:23In Progress
ABC123177,149,909183,409,39229/05/2018 15:25In Progress
ABC123177,149,909183,847,7795/06/2018 16:40In Progress
ABC123177,149,909183,847,7825/06/2018 16:40In Progress
ABC123177,149,909183,847,8655/06/2018 16:41In Progress
ABC123177,149,909183,847,8715/06/2018 16:41In Progress
ABC123177,149,909183,848,8235/06/2018 16:50In Progress
ABC123177,149,909183,848,8265/06/2018 16:50In Progress
ABC123177,149,909183,889,4436/06/2018 12:01In Progress
ABC123177,149,909183,889,7436/06/2018 12:04In Progress
ABC123177,149,909183,941,7457/06/2018 8:51In Progress
ABC123177,149,909183,941,7477/06/2018 8:51In Progress
ABC123177,149,909184,574,58618/06/2018 15:55In Progress
ABC123177,149,909184,770,06521/06/2018 12:35In Progress
ABC123177,149,909184,831,01222/06/2018 11:39In Progress
ABC123177,149,909184,831,02022/06/2018 11:39In Progress
ABC123177,149,909186,790,86426/07/2018 12:09In Progress
ABC123177,149,909186,790,87226/07/2018 12:09In Progress
ABC123177,149,909187,513,2308/08/2018 15:05In Progress
ABC123177,149,909187,513,2348/08/2018 15:05In Progress
ABC123177,149,909188,144,06521/08/2018 8:05In Progress
ABC123177,149,909188,144,06621/08/2018 8:06In Progress
ABC123177,149,909188,177,14021/08/2018 14:35In Progress
ABC123177,149,909188,661,80730/08/2018 9:55In Progress
ABC123177,149,909188,661,81230/08/2018 9:56In Progress
ABC123177,149,909189,145,8307/09/2018 19:45In Progress
ABC123177,149,909189,290,54311/09/2018 11:09In Progress
ABC123177,149,909189,290,54911/09/2018 11:09In Progress
ABC123177,149,909189,290,55111/09/2018 11:09In Progress
ABC123177,149,909189,471,58914/09/2018 9:24In Progress
ABC123177,149,909189,471,59514/09/2018 9:24In Progress
ABC123177,149,909189,672,22718/09/2018 14:35In Progress
ABC123177,149,909189,672,23418/09/2018 14:35In Progress
ABC123177,149,909193,462,65015/11/2018 12:00In Progress
ABC123177,149,909193,462,95515/11/2018 12:05In Progress
ABC123177,149,909193,462,96415/11/2018 12:05In Progress
ABC123177,149,909193,462,96615/11/2018 12:05In Progress
ABC123177,149,909193,462,98315/11/2018 12:05In Progress
ABC123177,149,909193,654,76519/11/2018 13:35Replaced
ABC123177,149,909193,654,77819/11/2018 13:35Replaced
ABC123184,736,434193,653,14019/11/2018 13:15In Progress
ABC123184,736,434193,653,14319/11/2018 13:16Transmitted
ABC123184,736,434193,653,18119/11/2018 13:16Transmitted
ABC123184,736,434193,653,43719/11/2018 13:20Transmitted
ABC123184,736,434193,655,10619/11/2018 13:37In Progress
ABC123184,736,434193,655,11019/11/2018 13:37In Progress
ABC123184,736,434193,713,07720/11/2018 11:05In Progress
ABC123184,736,434193,938,75223/11/2018 14:05In Progress
ABC123184,736,434193,941,13323/11/2018 14:30In Progress
ABC123184,736,434193,941,63123/11/2018 14:36In Progress
ABC123184,736,434193,941,64223/11/2018 14:36In Progress
ABC123184,736,434194,114,96627/11/2018 11:30In Progress
ABC123184,736,434194,115,34927/11/2018 11:35In Progress
ABC123184,736,434194,115,35827/11/2018 11:35In Progress
ABC123184,736,434194,115,64827/11/2018 11:38In Progress
ABC123184,736,434194,115,65427/11/2018 11:38In Progress
ABC123184,736,434194,187,82128/11/2018 12:50In Progress
ABC123184,736,434194,212,46428/11/2018 17:21Replaced
ABC123184,736,434194,212,46728/11/2018 17:21Replaced
ABC123185,179,398194,211,56728/11/2018 17:10In Progress
ABC123185,179,398194,211,74128/11/2018 17:12Transmitted
ABC123185,179,398194,212,92528/11/2018 17:25In Progress
ABC123185,179,398194,212,93728/11/2018 17:25In Progress
ABC123185,179,398194,503,9604/12/2018 13:20In Progress
ABC123185,179,398194,507,1414/12/2018 14:00In Progress
ABC123185,179,398194,520,2934/12/2018 16:36In Progress
ABC123185,179,398194,520,3064/12/2018 16:36In Progress
ABC123185,179,398194,641,2346/12/2018 14:05In Progress
ABC123185,179,398194,641,2446/12/2018 14:05In Progress
ABC123185,179,398194,722,9427/12/2018 20:06In Progress
ABC123185,179,398194,722,9507/12/2018 20:06In Progress
ABC123185,179,398194,723,3757/12/2018 20:20In Progress
ABC123185,179,398194,723,3787/12/2018 20:20In Progress
ABC123185,179,398194,986,93513/12/2018 10:40In Progress
ABC123185,179,398195,229,47418/12/2018 9:40In Progress
ABC123185,179,398195,229,93418/12/2018 9:50In Progress
ABC123185,179,398195,229,94218/12/2018 9:50In Progress
ABC123185,179,398195,230,10618/12/2018 9:52In Progress
ABC123185,179,398195,230,11418/12/2018 9:52In Progress
ABC123185,179,398195,276,78418/12/2018 20:45In Progress
ABC123185,179,398195,292,25419/12/2018 8:39Completed
ABC123185,179,398195,292,26719/12/2018 8:40Completed
ABC123185,179,398195,292,28919/12/2018 8:40Completed
     

 

Expected Output:

Ordnum Status CntMaxTSMinTS
ABC123Initial229/05/2018 14:3229/05/2018 14:31
ABC123Transmitted129/05/2018 14:3229/05/2018 14:32
ABC123In Progress4315/11/2018 12:0529/05/2018 14:40
ABC123Replaced219/11/2018 13:3519/11/2018 13:35
ABC123In Progress119/11/2018 13:1519/11/2018 13:15
ABC123Transmitted319/11/2018 13:2019/11/2018 13:16
ABC123In Progress1328/11/2018 12:5019/11/2018 13:37
ABC123Replaced228/11/2018 17:2128/11/2018 17:21
ABC123In Progress128/11/2018 17:1028/11/2018 17:10
ABC123Transmitted128/11/2018 17:1228/11/2018 17:12
ABC123In Progress1918/12/2018 20:4528/11/2018 17:25
ABC123Completed319/12/2018 8:4019/12/2018 8:39

Accepted Solutions
Teradata Employee

Re: How to get formatted output using Preceding and Following?

Ho gasingh,

 

You can go for this two stat steps solution, but it may have a better performing version doable.

Datas

create multiset volatile table mvt_datas, no log
( Ordnum        char(6)       not null
, ItemRevId     integer       not null
, ItemHistId    integer       not null
, Ts            timestamp(0)  not null
, Status        varchar(11)   not null
)
primary index (OrdNum, Itemrevid)
on commit preserve rows
;

insert into mvt_datas values ('ABC123', 177149909, 183403225, timestamp '2018-05-29 14:31:00', 'Initial'    );
insert into mvt_datas values ('ABC123', 177149909, 183403269, timestamp '2018-05-29 14:32:00', 'Initial'    );
insert into mvt_datas values ('ABC123', 177149909, 183403274, timestamp '2018-05-29 14:32:00', 'Transmitted');
insert into mvt_datas values ('ABC123', 177149909, 183404283, timestamp '2018-05-29 14:40:00', 'In Progress');
insert into mvt_datas values ('ABC123', 177149909, 183404291, timestamp '2018-05-29 14:40:00', 'In Progress');
insert into mvt_datas values ('ABC123', 177149909, 183407437, timestamp '2018-05-29 15:08:00', 'In Progress');
insert into mvt_datas values ('ABC123', 177149909, 183407442, timestamp '2018-05-29 15:08:00', 'In Progress');
insert into mvt_datas values ('ABC123', 177149909, 183409160, timestamp '2018-05-29 15:23:00', 'In Progress');
insert into mvt_datas values ('ABC123', 177149909, 183409166, timestamp '2018-05-29 15:23:00', 'In Progress');
insert into mvt_datas values ('ABC123', 177149909, 183409392, timestamp '2018-05-29 15:25:00', 'In Progress');
insert into mvt_datas values ('ABC123', 177149909, 183847779, timestamp '2018-06-05 16:40:00', 'In Progress');
insert into mvt_datas values ('ABC123', 177149909, 183847782, timestamp '2018-06-05 16:40:00', 'In Progress');
insert into mvt_datas values ('ABC123', 177149909, 183847865, timestamp '2018-06-05 16:41:00', 'In Progress');
insert into mvt_datas values ('ABC123', 177149909, 183847871, timestamp '2018-06-05 16:41:00', 'In Progress');
insert into mvt_datas values ('ABC123', 177149909, 183848823, timestamp '2018-06-05 16:50:00', 'In Progress');
insert into mvt_datas values ('ABC123', 177149909, 183848826, timestamp '2018-06-05 16:50:00', 'In Progress');
insert into mvt_datas values ('ABC123', 177149909, 183889443, timestamp '2018-06-06 12:01:00', 'In Progress');
insert into mvt_datas values ('ABC123', 177149909, 183889743, timestamp '2018-06-06 12:04:00', 'In Progress');
insert into mvt_datas values ('ABC123', 177149909, 183941745, timestamp '2018-06-07 08:51:00', 'In Progress');
insert into mvt_datas values ('ABC123', 177149909, 183941747, timestamp '2018-06-07 08:51:00', 'In Progress');
insert into mvt_datas values ('ABC123', 177149909, 184574586, timestamp '2018-06-18 15:55:00', 'In Progress');
insert into mvt_datas values ('ABC123', 177149909, 184770065, timestamp '2018-06-21 12:35:00', 'In Progress');
insert into mvt_datas values ('ABC123', 177149909, 184831012, timestamp '2018-06-22 11:39:00', 'In Progress');
insert into mvt_datas values ('ABC123', 177149909, 184831020, timestamp '2018-06-22 11:39:00', 'In Progress');
insert into mvt_datas values ('ABC123', 177149909, 186790864, timestamp '2018-07-26 12:09:00', 'In Progress');
insert into mvt_datas values ('ABC123', 177149909, 186790872, timestamp '2018-07-26 12:09:00', 'In Progress');
insert into mvt_datas values ('ABC123', 177149909, 187513230, timestamp '2018-08-08 15:05:00', 'In Progress');
insert into mvt_datas values ('ABC123', 177149909, 187513234, timestamp '2018-08-08 15:05:00', 'In Progress');
insert into mvt_datas values ('ABC123', 177149909, 188144065, timestamp '2018-08-21 08:05:00', 'In Progress');
insert into mvt_datas values ('ABC123', 177149909, 188144066, timestamp '2018-08-21 08:06:00', 'In Progress');
insert into mvt_datas values ('ABC123', 177149909, 188177140, timestamp '2018-08-21 14:35:00', 'In Progress');
insert into mvt_datas values ('ABC123', 177149909, 188661807, timestamp '2018-08-30 09:55:00', 'In Progress');
insert into mvt_datas values ('ABC123', 177149909, 188661812, timestamp '2018-08-30 09:56:00', 'In Progress');
insert into mvt_datas values ('ABC123', 177149909, 189145830, timestamp '2018-09-07 19:45:00', 'In Progress');
insert into mvt_datas values ('ABC123', 177149909, 189290543, timestamp '2018-09-11 11:09:00', 'In Progress');
insert into mvt_datas values ('ABC123', 177149909, 189290549, timestamp '2018-09-11 11:09:00', 'In Progress');
insert into mvt_datas values ('ABC123', 177149909, 189290551, timestamp '2018-09-11 11:09:00', 'In Progress');
insert into mvt_datas values ('ABC123', 177149909, 189471589, timestamp '2018-09-14 09:24:00', 'In Progress');
insert into mvt_datas values ('ABC123', 177149909, 189471595, timestamp '2018-09-14 09:24:00', 'In Progress');
insert into mvt_datas values ('ABC123', 177149909, 189672227, timestamp '2018-09-18 14:35:00', 'In Progress');
insert into mvt_datas values ('ABC123', 177149909, 189672234, timestamp '2018-09-18 14:35:00', 'In Progress');
insert into mvt_datas values ('ABC123', 177149909, 193462650, timestamp '2018-11-15 12:00:00', 'In Progress');
insert into mvt_datas values ('ABC123', 177149909, 193462955, timestamp '2018-11-15 12:05:00', 'In Progress');
insert into mvt_datas values ('ABC123', 177149909, 193462964, timestamp '2018-11-15 12:05:00', 'In Progress');
insert into mvt_datas values ('ABC123', 177149909, 193462966, timestamp '2018-11-15 12:05:00', 'In Progress');
insert into mvt_datas values ('ABC123', 177149909, 193462983, timestamp '2018-11-15 12:05:00', 'In Progress');
insert into mvt_datas values ('ABC123', 177149909, 193654765, timestamp '2018-11-19 13:35:00', 'Replaced'   );
insert into mvt_datas values ('ABC123', 177149909, 193654778, timestamp '2018-11-19 13:35:00', 'Replaced'   );
insert into mvt_datas values ('ABC123', 184736434, 193653140, timestamp '2018-11-19 13:15:00', 'In Progress');
insert into mvt_datas values ('ABC123', 184736434, 193653143, timestamp '2018-11-19 13:16:00', 'Transmitted');
insert into mvt_datas values ('ABC123', 184736434, 193653181, timestamp '2018-11-19 13:16:00', 'Transmitted');
insert into mvt_datas values ('ABC123', 184736434, 193653437, timestamp '2018-11-19 13:20:00', 'Transmitted');
insert into mvt_datas values ('ABC123', 184736434, 193655106, timestamp '2018-11-19 13:37:00', 'In Progress');
insert into mvt_datas values ('ABC123', 184736434, 193655110, timestamp '2018-11-19 13:37:00', 'In Progress');
insert into mvt_datas values ('ABC123', 184736434, 193713077, timestamp '2018-11-20 11:05:00', 'In Progress');
insert into mvt_datas values ('ABC123', 184736434, 193938752, timestamp '2018-11-23 14:05:00', 'In Progress');
insert into mvt_datas values ('ABC123', 184736434, 193941133, timestamp '2018-11-23 14:30:00', 'In Progress');
insert into mvt_datas values ('ABC123', 184736434, 193941631, timestamp '2018-11-23 14:36:00', 'In Progress');
insert into mvt_datas values ('ABC123', 184736434, 193941642, timestamp '2018-11-23 14:36:00', 'In Progress');
insert into mvt_datas values ('ABC123', 184736434, 194114966, timestamp '2018-11-27 11:30:00', 'In Progress');
insert into mvt_datas values ('ABC123', 184736434, 194115349, timestamp '2018-11-27 11:35:00', 'In Progress');
insert into mvt_datas values ('ABC123', 184736434, 194115358, timestamp '2018-11-27 11:35:00', 'In Progress');
insert into mvt_datas values ('ABC123', 184736434, 194115648, timestamp '2018-11-27 11:38:00', 'In Progress');
insert into mvt_datas values ('ABC123', 184736434, 194115654, timestamp '2018-11-27 11:38:00', 'In Progress');
insert into mvt_datas values ('ABC123', 184736434, 194187821, timestamp '2018-11-28 12:50:00', 'In Progress');
insert into mvt_datas values ('ABC123', 184736434, 194212464, timestamp '2018-11-28 17:21:00', 'Replaced'   );
insert into mvt_datas values ('ABC123', 184736434, 194212467, timestamp '2018-11-28 17:21:00', 'Replaced'   );
insert into mvt_datas values ('ABC123', 185179398, 194211567, timestamp '2018-11-28 17:10:00', 'In Progress');
insert into mvt_datas values ('ABC123', 185179398, 194211741, timestamp '2018-11-28 17:12:00', 'Transmitted');
insert into mvt_datas values ('ABC123', 185179398, 194212925, timestamp '2018-11-28 17:25:00', 'In Progress');
insert into mvt_datas values ('ABC123', 185179398, 194212937, timestamp '2018-11-28 17:25:00', 'In Progress');
insert into mvt_datas values ('ABC123', 185179398, 194503960, timestamp '2018-12-04 13:20:00', 'In Progress');
insert into mvt_datas values ('ABC123', 185179398, 194507141, timestamp '2018-12-04 14:00:00', 'In Progress');
insert into mvt_datas values ('ABC123', 185179398, 194520293, timestamp '2018-12-04 16:36:00', 'In Progress');
insert into mvt_datas values ('ABC123', 185179398, 194520306, timestamp '2018-12-04 16:36:00', 'In Progress');
insert into mvt_datas values ('ABC123', 185179398, 194641234, timestamp '2018-12-06 14:05:00', 'In Progress');
insert into mvt_datas values ('ABC123', 185179398, 194641244, timestamp '2018-12-06 14:05:00', 'In Progress');
insert into mvt_datas values ('ABC123', 185179398, 194722942, timestamp '2018-12-07 20:06:00', 'In Progress');
insert into mvt_datas values ('ABC123', 185179398, 194722950, timestamp '2018-12-07 20:06:00', 'In Progress');
insert into mvt_datas values ('ABC123', 185179398, 194723375, timestamp '2018-12-07 20:20:00', 'In Progress');
insert into mvt_datas values ('ABC123', 185179398, 194723378, timestamp '2018-12-07 20:20:00', 'In Progress');
insert into mvt_datas values ('ABC123', 185179398, 194986935, timestamp '2018-12-13 10:40:00', 'In Progress');
insert into mvt_datas values ('ABC123', 185179398, 195229474, timestamp '2018-12-18 09:40:00', 'In Progress');
insert into mvt_datas values ('ABC123', 185179398, 195229934, timestamp '2018-12-18 09:50:00', 'In Progress');
insert into mvt_datas values ('ABC123', 185179398, 195229942, timestamp '2018-12-18 09:50:00', 'In Progress');
insert into mvt_datas values ('ABC123', 185179398, 195230106, timestamp '2018-12-18 09:52:00', 'In Progress');
insert into mvt_datas values ('ABC123', 185179398, 195230114, timestamp '2018-12-18 09:52:00', 'In Progress');
insert into mvt_datas values ('ABC123', 185179398, 195276784, timestamp '2018-12-18 20:45:00', 'In Progress');
insert into mvt_datas values ('ABC123', 185179398, 195292254, timestamp '2018-12-19 08:39:00', 'Completed'  );
insert into mvt_datas values ('ABC123', 185179398, 195292267, timestamp '2018-12-19 08:40:00', 'Completed'  );
insert into mvt_datas values ('ABC123', 185179398, 195292289, timestamp '2018-12-19 08:40:00', 'Completed'  );

collect statistics column (OrdNum, Itemrevid) on mvt_datas;

Query

with cte_grp (OrdNum, Itemrevid, Ts, Status, grp) as
(
select OrdNum, Itemrevid, Ts, Status
     , row_number() over(partition by OrdNum, Itemrevid         order by Ts asc, ItemHistId asc)
     - row_number() over(partition by OrdNum, Itemrevid, Status order by Ts asc, ItemHistId asc) as grp
  from mvt_datas
)
  select OrdNum, Status
       , count(*) as Cnt
       , min(Ts)  as MinTs
       , max(Ts)  as MaxTs
    from cte_grp
group by OrdNum, Itemrevid, Status, grp
order by OrdNum, Itemrevid, min(Ts);
1 ACCEPTED SOLUTION
3 REPLIES 3
Teradata Employee

Re: How to get formatted output using Preceding and Following?

Ho gasingh,

 

You can go for this two stat steps solution, but it may have a better performing version doable.

Datas

create multiset volatile table mvt_datas, no log
( Ordnum        char(6)       not null
, ItemRevId     integer       not null
, ItemHistId    integer       not null
, Ts            timestamp(0)  not null
, Status        varchar(11)   not null
)
primary index (OrdNum, Itemrevid)
on commit preserve rows
;

insert into mvt_datas values ('ABC123', 177149909, 183403225, timestamp '2018-05-29 14:31:00', 'Initial'    );
insert into mvt_datas values ('ABC123', 177149909, 183403269, timestamp '2018-05-29 14:32:00', 'Initial'    );
insert into mvt_datas values ('ABC123', 177149909, 183403274, timestamp '2018-05-29 14:32:00', 'Transmitted');
insert into mvt_datas values ('ABC123', 177149909, 183404283, timestamp '2018-05-29 14:40:00', 'In Progress');
insert into mvt_datas values ('ABC123', 177149909, 183404291, timestamp '2018-05-29 14:40:00', 'In Progress');
insert into mvt_datas values ('ABC123', 177149909, 183407437, timestamp '2018-05-29 15:08:00', 'In Progress');
insert into mvt_datas values ('ABC123', 177149909, 183407442, timestamp '2018-05-29 15:08:00', 'In Progress');
insert into mvt_datas values ('ABC123', 177149909, 183409160, timestamp '2018-05-29 15:23:00', 'In Progress');
insert into mvt_datas values ('ABC123', 177149909, 183409166, timestamp '2018-05-29 15:23:00', 'In Progress');
insert into mvt_datas values ('ABC123', 177149909, 183409392, timestamp '2018-05-29 15:25:00', 'In Progress');
insert into mvt_datas values ('ABC123', 177149909, 183847779, timestamp '2018-06-05 16:40:00', 'In Progress');
insert into mvt_datas values ('ABC123', 177149909, 183847782, timestamp '2018-06-05 16:40:00', 'In Progress');
insert into mvt_datas values ('ABC123', 177149909, 183847865, timestamp '2018-06-05 16:41:00', 'In Progress');
insert into mvt_datas values ('ABC123', 177149909, 183847871, timestamp '2018-06-05 16:41:00', 'In Progress');
insert into mvt_datas values ('ABC123', 177149909, 183848823, timestamp '2018-06-05 16:50:00', 'In Progress');
insert into mvt_datas values ('ABC123', 177149909, 183848826, timestamp '2018-06-05 16:50:00', 'In Progress');
insert into mvt_datas values ('ABC123', 177149909, 183889443, timestamp '2018-06-06 12:01:00', 'In Progress');
insert into mvt_datas values ('ABC123', 177149909, 183889743, timestamp '2018-06-06 12:04:00', 'In Progress');
insert into mvt_datas values ('ABC123', 177149909, 183941745, timestamp '2018-06-07 08:51:00', 'In Progress');
insert into mvt_datas values ('ABC123', 177149909, 183941747, timestamp '2018-06-07 08:51:00', 'In Progress');
insert into mvt_datas values ('ABC123', 177149909, 184574586, timestamp '2018-06-18 15:55:00', 'In Progress');
insert into mvt_datas values ('ABC123', 177149909, 184770065, timestamp '2018-06-21 12:35:00', 'In Progress');
insert into mvt_datas values ('ABC123', 177149909, 184831012, timestamp '2018-06-22 11:39:00', 'In Progress');
insert into mvt_datas values ('ABC123', 177149909, 184831020, timestamp '2018-06-22 11:39:00', 'In Progress');
insert into mvt_datas values ('ABC123', 177149909, 186790864, timestamp '2018-07-26 12:09:00', 'In Progress');
insert into mvt_datas values ('ABC123', 177149909, 186790872, timestamp '2018-07-26 12:09:00', 'In Progress');
insert into mvt_datas values ('ABC123', 177149909, 187513230, timestamp '2018-08-08 15:05:00', 'In Progress');
insert into mvt_datas values ('ABC123', 177149909, 187513234, timestamp '2018-08-08 15:05:00', 'In Progress');
insert into mvt_datas values ('ABC123', 177149909, 188144065, timestamp '2018-08-21 08:05:00', 'In Progress');
insert into mvt_datas values ('ABC123', 177149909, 188144066, timestamp '2018-08-21 08:06:00', 'In Progress');
insert into mvt_datas values ('ABC123', 177149909, 188177140, timestamp '2018-08-21 14:35:00', 'In Progress');
insert into mvt_datas values ('ABC123', 177149909, 188661807, timestamp '2018-08-30 09:55:00', 'In Progress');
insert into mvt_datas values ('ABC123', 177149909, 188661812, timestamp '2018-08-30 09:56:00', 'In Progress');
insert into mvt_datas values ('ABC123', 177149909, 189145830, timestamp '2018-09-07 19:45:00', 'In Progress');
insert into mvt_datas values ('ABC123', 177149909, 189290543, timestamp '2018-09-11 11:09:00', 'In Progress');
insert into mvt_datas values ('ABC123', 177149909, 189290549, timestamp '2018-09-11 11:09:00', 'In Progress');
insert into mvt_datas values ('ABC123', 177149909, 189290551, timestamp '2018-09-11 11:09:00', 'In Progress');
insert into mvt_datas values ('ABC123', 177149909, 189471589, timestamp '2018-09-14 09:24:00', 'In Progress');
insert into mvt_datas values ('ABC123', 177149909, 189471595, timestamp '2018-09-14 09:24:00', 'In Progress');
insert into mvt_datas values ('ABC123', 177149909, 189672227, timestamp '2018-09-18 14:35:00', 'In Progress');
insert into mvt_datas values ('ABC123', 177149909, 189672234, timestamp '2018-09-18 14:35:00', 'In Progress');
insert into mvt_datas values ('ABC123', 177149909, 193462650, timestamp '2018-11-15 12:00:00', 'In Progress');
insert into mvt_datas values ('ABC123', 177149909, 193462955, timestamp '2018-11-15 12:05:00', 'In Progress');
insert into mvt_datas values ('ABC123', 177149909, 193462964, timestamp '2018-11-15 12:05:00', 'In Progress');
insert into mvt_datas values ('ABC123', 177149909, 193462966, timestamp '2018-11-15 12:05:00', 'In Progress');
insert into mvt_datas values ('ABC123', 177149909, 193462983, timestamp '2018-11-15 12:05:00', 'In Progress');
insert into mvt_datas values ('ABC123', 177149909, 193654765, timestamp '2018-11-19 13:35:00', 'Replaced'   );
insert into mvt_datas values ('ABC123', 177149909, 193654778, timestamp '2018-11-19 13:35:00', 'Replaced'   );
insert into mvt_datas values ('ABC123', 184736434, 193653140, timestamp '2018-11-19 13:15:00', 'In Progress');
insert into mvt_datas values ('ABC123', 184736434, 193653143, timestamp '2018-11-19 13:16:00', 'Transmitted');
insert into mvt_datas values ('ABC123', 184736434, 193653181, timestamp '2018-11-19 13:16:00', 'Transmitted');
insert into mvt_datas values ('ABC123', 184736434, 193653437, timestamp '2018-11-19 13:20:00', 'Transmitted');
insert into mvt_datas values ('ABC123', 184736434, 193655106, timestamp '2018-11-19 13:37:00', 'In Progress');
insert into mvt_datas values ('ABC123', 184736434, 193655110, timestamp '2018-11-19 13:37:00', 'In Progress');
insert into mvt_datas values ('ABC123', 184736434, 193713077, timestamp '2018-11-20 11:05:00', 'In Progress');
insert into mvt_datas values ('ABC123', 184736434, 193938752, timestamp '2018-11-23 14:05:00', 'In Progress');
insert into mvt_datas values ('ABC123', 184736434, 193941133, timestamp '2018-11-23 14:30:00', 'In Progress');
insert into mvt_datas values ('ABC123', 184736434, 193941631, timestamp '2018-11-23 14:36:00', 'In Progress');
insert into mvt_datas values ('ABC123', 184736434, 193941642, timestamp '2018-11-23 14:36:00', 'In Progress');
insert into mvt_datas values ('ABC123', 184736434, 194114966, timestamp '2018-11-27 11:30:00', 'In Progress');
insert into mvt_datas values ('ABC123', 184736434, 194115349, timestamp '2018-11-27 11:35:00', 'In Progress');
insert into mvt_datas values ('ABC123', 184736434, 194115358, timestamp '2018-11-27 11:35:00', 'In Progress');
insert into mvt_datas values ('ABC123', 184736434, 194115648, timestamp '2018-11-27 11:38:00', 'In Progress');
insert into mvt_datas values ('ABC123', 184736434, 194115654, timestamp '2018-11-27 11:38:00', 'In Progress');
insert into mvt_datas values ('ABC123', 184736434, 194187821, timestamp '2018-11-28 12:50:00', 'In Progress');
insert into mvt_datas values ('ABC123', 184736434, 194212464, timestamp '2018-11-28 17:21:00', 'Replaced'   );
insert into mvt_datas values ('ABC123', 184736434, 194212467, timestamp '2018-11-28 17:21:00', 'Replaced'   );
insert into mvt_datas values ('ABC123', 185179398, 194211567, timestamp '2018-11-28 17:10:00', 'In Progress');
insert into mvt_datas values ('ABC123', 185179398, 194211741, timestamp '2018-11-28 17:12:00', 'Transmitted');
insert into mvt_datas values ('ABC123', 185179398, 194212925, timestamp '2018-11-28 17:25:00', 'In Progress');
insert into mvt_datas values ('ABC123', 185179398, 194212937, timestamp '2018-11-28 17:25:00', 'In Progress');
insert into mvt_datas values ('ABC123', 185179398, 194503960, timestamp '2018-12-04 13:20:00', 'In Progress');
insert into mvt_datas values ('ABC123', 185179398, 194507141, timestamp '2018-12-04 14:00:00', 'In Progress');
insert into mvt_datas values ('ABC123', 185179398, 194520293, timestamp '2018-12-04 16:36:00', 'In Progress');
insert into mvt_datas values ('ABC123', 185179398, 194520306, timestamp '2018-12-04 16:36:00', 'In Progress');
insert into mvt_datas values ('ABC123', 185179398, 194641234, timestamp '2018-12-06 14:05:00', 'In Progress');
insert into mvt_datas values ('ABC123', 185179398, 194641244, timestamp '2018-12-06 14:05:00', 'In Progress');
insert into mvt_datas values ('ABC123', 185179398, 194722942, timestamp '2018-12-07 20:06:00', 'In Progress');
insert into mvt_datas values ('ABC123', 185179398, 194722950, timestamp '2018-12-07 20:06:00', 'In Progress');
insert into mvt_datas values ('ABC123', 185179398, 194723375, timestamp '2018-12-07 20:20:00', 'In Progress');
insert into mvt_datas values ('ABC123', 185179398, 194723378, timestamp '2018-12-07 20:20:00', 'In Progress');
insert into mvt_datas values ('ABC123', 185179398, 194986935, timestamp '2018-12-13 10:40:00', 'In Progress');
insert into mvt_datas values ('ABC123', 185179398, 195229474, timestamp '2018-12-18 09:40:00', 'In Progress');
insert into mvt_datas values ('ABC123', 185179398, 195229934, timestamp '2018-12-18 09:50:00', 'In Progress');
insert into mvt_datas values ('ABC123', 185179398, 195229942, timestamp '2018-12-18 09:50:00', 'In Progress');
insert into mvt_datas values ('ABC123', 185179398, 195230106, timestamp '2018-12-18 09:52:00', 'In Progress');
insert into mvt_datas values ('ABC123', 185179398, 195230114, timestamp '2018-12-18 09:52:00', 'In Progress');
insert into mvt_datas values ('ABC123', 185179398, 195276784, timestamp '2018-12-18 20:45:00', 'In Progress');
insert into mvt_datas values ('ABC123', 185179398, 195292254, timestamp '2018-12-19 08:39:00', 'Completed'  );
insert into mvt_datas values ('ABC123', 185179398, 195292267, timestamp '2018-12-19 08:40:00', 'Completed'  );
insert into mvt_datas values ('ABC123', 185179398, 195292289, timestamp '2018-12-19 08:40:00', 'Completed'  );

collect statistics column (OrdNum, Itemrevid) on mvt_datas;

Query

with cte_grp (OrdNum, Itemrevid, Ts, Status, grp) as
(
select OrdNum, Itemrevid, Ts, Status
     , row_number() over(partition by OrdNum, Itemrevid         order by Ts asc, ItemHistId asc)
     - row_number() over(partition by OrdNum, Itemrevid, Status order by Ts asc, ItemHistId asc) as grp
  from mvt_datas
)
  select OrdNum, Status
       , count(*) as Cnt
       , min(Ts)  as MinTs
       , max(Ts)  as MaxTs
    from cte_grp
group by OrdNum, Itemrevid, Status, grp
order by OrdNum, Itemrevid, min(Ts);
Teradata Employee

Re: How to get formatted output using Preceding and Following?

And the nPath solution (with only one STAT step) :

  select OrdNum as OrdNum
       , Status as Status
       , Cnt    as Cnt
       , MinTs  as MinTs
       , MaxTs  as MaxTs
    from nPath(          on mvt_datas
               partition by OrdNum, ItemRevId
                   order by ItemHistId asc
                      using
                       mode ( nonoverlapping )
                    symbols ( Status <> lag (Status, 1, '') as N
                            , Status  = lag (Status, 1, '') as S
                            )
                    pattern ( 'N.S*' )
                     result ( first(OrdNum     of     N   ) as OrdNum
                            , first(ItemRevId  of     N   ) as ItemRevId
                            , first(ItemHistId of     N   ) as ItemHistId
                            , first(Status     of     N   ) as Status
                            , count(*          of any(N,S)) as Cnt
                            , first(Ts         of     N   ) as MinTs
                            , last (Ts         of any(N,S)) as MaxTs
                            )
              ) as np
order by OrdNum, ItemRevId, ItemHistId;

 

Re: How to get formatted output using Preceding and Following?

Hello Waldar,

 

Thank you so much for the solution. It worked like charm. 

I had a volatile table in which i created the row number as you did and then used the second half exactly in the way you did. 

 

Wanted to take out some time and appreciate your effort of loading the data and then testing it out and providing multiple solutions. 

 

Regards,

Gagan