Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- SELECT * INTO NewCntIND
- FROM (SELECT
- a.al AS AL,
- (a.Flno+ "/" +b.Flno) AS Flno,
- a.NewStart AS NewStart,
- a.NewEnd AS NewEnd,
- a.Orig AS Orig,
- a.Overnight AS Overnight,
- a.Pattern AS Pattern,
- (a.ac_name + "/" + b.ac_name) AS Ac_name,
- a.Std AS STD,
- b.sta AS STA,
- b.Dest AS Dest,
- SWITCH(b.STD > dateadd("h",1,a.STA), ( INT(SUM(a.blocktime + b.blocktime + ( b.std - a.sta )) * 24) & ":" & Format(a.blocktime + b.blocktime + ( b.std - a.sta ), "nn") ) , (b.STD <= dateadd("h",1,a.STA) AND b.STD <= #02:00:00#), ( INT(SUM(a.blocktime + b.blocktime + ( dateadd("d",1,b.std) - a.sta )) * 24) & ":" & Format(a.blocktime + b.blocktime + ( dateadd("d",1,b.std) - a.sta ), "nn") )) AS BlockTime,
- (a.Orig + b.Dest) AS Route_code,
- a.class AS Class,
- a.dest AS Stop,
- b.OAL AS OAL
- FROM TempFinal a INNER JOIN TempFinal b
- ON (a.Dest = b.Orig AND a.Dest IN ("HAN","SGN","DAD") AND a.Route_section IN ("Germany","France","United Kingdom","Japan","South Korea") AND b.Dest IN ("REP","LPQ","RGN") AND a.Flno NOT LIKE "*[*]*" AND b.STD > dateadd("h",1,a.STA))
- OR (a.Dest = b.Orig AND a.Dest IN ("HAN","SGN","DAD") AND a.Route_section IN ("Germany","France","United Kingdom","Japan","South Korea") AND b.Dest IN ("REP","LPQ","RGN") AND a.Flno NOT LIKE "*[*]*" AND b.STD <= dateadd("h",1,a.STA) AND b.STD <= #02:00:00#)
- GROUP BY
- a.al,
- (a.Flno+ "/" +b.Flno),
- a.NewStart,
- a.NewEnd,
- a.Orig,
- a.Overnight ,
- a.Pattern ,
- (a.ac_name + "/" + b.ac_name),
- a.Std ,
- b.sta ,
- b.Dest ,
- a.blocktime + b.blocktime + ( b.std - a.sta ) ,(a.blocktime + b.blocktime + ( dateadd("d",1,b.std) - a.sta )),
- (a.Orig + b.Dest) ,
- a.class ,
- a.dest ,
- b.OAL) AS [%$##@_Alias];
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement