Fetching next record

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.
Teradata Employee

Fetching next record

Below is the query and sample data for an airline project.The below records are sequence of a LEG_ID (PLANE hardware) with different flight nos which operate throught out the day from one source to destination.

Here there are 3 flights,790 flight travles from source LAX to  destination YYZ ,794 flight from LAX to YYZ ,458 flight from YYZ to YYW.

Here only record 790 and 458 are valid as 1st flight starts at 13.15 (LEG_SCHD_DEP_TME) and lands at 17:50:00 (LEG_SCHD_DEP_TME) at  YYZ.so next flight should start from YYZ which is at 20:10 pm.

But record flight no 794 is invalid as the flight starts at 15:00 which cannot be the case as 790 starts at same destination but different time .Is there some wat to eliminate this record.

Else is there some query where based on previous arrival time this record can be eliminated from the result set.

 

SyntaxEditor Code Snippet

sel
 orig_leg_id, FLIGHT_NUM, FLIGHT_ORIG_DTE, LEG_SCHD_ORIG , LEG_SCHD_DEST,LEG_SCHD_DEP_DTE,LEG_SCHD_DEP_TME,LEG_SCHD_ARR_DTE,LEG_SCHD_ARR_TME
from PEDW.LEG_SCHEDULED_SSIM a
QUALIFY ROW_NUMBER() OVER (PARTITION BY  CARRIER_CDE, FLIGHT_NUM, FLIGHT_SUFFIX_CDE, FLIGHT_ORIG_DTE, LEG_SCHD_ORIG , LEG_SCHD_DEST order by POSTDATE DESC)=1
where orig_leg_id = -4249007941
order by CAST((a.LEG_SCHD_DEP_DTE (DATE, FORMAT 'YYYY-MM-DD')) || ' ' || CAST(a.LEG_SCHD_DEP_TME AS CHAR(10)) AS TIMESTAMP(0))

 

ORIG_LEG_IDFLIGHT_NUMFLIGHT_ORIG_DTELEG_SCHD_ORIGLEG_SCHD_DESTLEG_SCHD_DEP_DTELEG_SCHD_DEP_TMELEG_SCHD_ARR_DTELEG_SCHD_ARR_TME
-4,249,007,9417905/3/2016LAXYYZ5/3/201613:15:005/3/201617:50:00
-4,249,007,9417945/3/2016LAXYYZ5/3/201615:00:005/3/201619:44:00
-4,249,007,9414585/3/2016YYZYOW5/3/201620:10:005/3/201621:13:00