Advertisement
Guest User

Untitled

a guest
Mar 26th, 2017
110
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
SQL 1.65 KB | None | 0 0
  1. SELECT * INTO NewCntIND
  2. FROM (SELECT  
  3.  
  4. a.al AS AL,
  5. (a.Flno+ "/" +b.Flno) AS Flno,
  6. a.NewStart AS NewStart,
  7. a.NewEnd AS NewEnd,
  8. a.Orig AS Orig,
  9. a.Overnight AS Overnight,
  10. a.Pattern AS Pattern,
  11. (a.ac_name + "/" + b.ac_name) AS Ac_name,
  12. a.Std AS STD,
  13. b.sta AS STA,
  14. b.Dest AS Dest,
  15. 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,
  16. (a.Orig + b.Dest) AS Route_code,
  17. a.class AS Class,
  18. a.dest AS Stop,
  19. b.OAL AS OAL
  20.  
  21.  
  22.  
  23. FROM TempFinal a INNER JOIN TempFinal b
  24. 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))
  25. 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#)
  26.  
  27.  
  28. GROUP BY
  29.  
  30. a.al,
  31. (a.Flno+ "/" +b.Flno),
  32. a.NewStart,
  33. a.NewEnd,
  34. a.Orig,
  35. a.Overnight ,
  36. a.Pattern  ,
  37. (a.ac_name + "/" + b.ac_name),
  38. a.Std  ,
  39. b.sta  ,
  40. b.Dest  ,
  41. a.blocktime + b.blocktime + ( b.std - a.sta ) ,(a.blocktime + b.blocktime + ( dateadd("d",1,b.std) - a.sta )),
  42. (a.Orig + b.Dest)  ,
  43. a.class  ,
  44. a.dest  ,
  45. b.OAL)  AS [%$##@_Alias];
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement