API tools faq
paste
Advertisement
Guest User

Untitled

a guest
Apr 26th, 2017
283
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 16.93 KB | None | 0 0
raw download clone embed print report
  1. SELECT
  2. SUBSTR(TICKET_FIRST_TRANSACTION_DT,7) AS ROUTE_YEAR,
  3. SUBSTR(TICKET_FIRST_TRANSACTION_DT,4,2) AS ROUTE_MONTH,
  4. SUBSTR(TICKET_FIRST_TRANSACTION_DT,1,2) AS ROUTE_DAY,
  5. DATEDIFF(DATE(CASE
  6. WHEN DEPARTURE_DATE_1 <> '' THEN DEPARTURE_DATE_1
  7. WHEN DEPARTURE_DATE_2 <> '' THEN DEPARTURE_DATE_2
  8. WHEN DEPARTURE_DATE_3 <> '' THEN DEPARTURE_DATE_3
  9. WHEN DEPARTURE_DATE_4 <> '' THEN DEPARTURE_DATE_4
  10. WHEN DEPARTURE_DATE_5 <> '' THEN DEPARTURE_DATE_5
  11. WHEN DEPARTURE_DATE_6 <> '' THEN DEPARTURE_DATE_6
  12. WHEN DEPARTURE_DATE_7 <> '' THEN DEPARTURE_DATE_7
  13. WHEN DEPARTURE_DATE_8 <> '' THEN DEPARTURE_DATE_8 END), DATE(TICKET_FIRST_TRANSACTION_DT)) AS DIFF_DAY,
  14. CONCAT(REPLACE(REPLACE(CONCAT(COALESCE(ORIGIN_1, ''), '-', COALESCE(DESTINATION_1, ''), '/', COALESCE(ORIGIN_2, ''), '-', COALESCE(DESTINATION_2, ''), '/', COALESCE(ORIGIN_3, ''), '-', COALESCE(DESTINATION_3, ''), '/', COALESCE(ORIGIN_4, ''), '-', COALESCE(DESTINATION_4, ''), '/', COALESCE(ORIGIN_5, ''), '-', COALESCE(DESTINATION_5, ''), '/', COALESCE(ORIGIN_6, ''), '-', COALESCE(DESTINATION_6, ''), '/', COALESCE(ORIGIN_7, ''), '-', COALESCE(DESTINATION_7, ''), '/', COALESCE(ORIGIN_8, ''), '-', COALESCE(DESTINATION_8, '')), '/-', ''), '-/', ''), '_',
  15. CASE
  16. WHEN COALESCE(AIR_CLASS_OF_SERVICE_CD_1, '') <> '' THEN CONCAT(COALESCE(AIR_CLASS_OF_SERVICE_CD_1, ''), '_')
  17. ELSE '' END,
  18. CASE
  19. WHEN COALESCE(AIR_CLASS_OF_SERVICE_CD_2, '') <> '' THEN CONCAT(COALESCE(AIR_CLASS_OF_SERVICE_CD_2, ''), '_')
  20. ELSE '' END,
  21. CASE
  22. WHEN COALESCE(AIR_CLASS_OF_SERVICE_CD_3, '') <> '' THEN CONCAT(COALESCE(AIR_CLASS_OF_SERVICE_CD_3, ''), '_')
  23. ELSE '' END,
  24. CASE
  25. WHEN COALESCE(AIR_CLASS_OF_SERVICE_CD_4, '') <> '' THEN CONCAT(COALESCE(AIR_CLASS_OF_SERVICE_CD_4, ''), '_')
  26. ELSE '' END,
  27. CASE
  28. WHEN COALESCE(AIR_CLASS_OF_SERVICE_CD_5, '') <> '' THEN CONCAT(COALESCE(AIR_CLASS_OF_SERVICE_CD_5, ''), '_')
  29. ELSE '' END,
  30. CASE
  31. WHEN COALESCE(AIR_CLASS_OF_SERVICE_CD_6, '') <> '' THEN CONCAT(COALESCE(AIR_CLASS_OF_SERVICE_CD_6, ''), '_')
  32. ELSE '' END,
  33. CASE
  34. WHEN COALESCE(AIR_CLASS_OF_SERVICE_CD_7, '') <> '' THEN CONCAT(COALESCE(AIR_CLASS_OF_SERVICE_CD_7, ''), '_')
  35. ELSE '' END,
  36. CASE
  37. WHEN COALESCE(AIR_CLASS_OF_SERVICE_CD_8, '') <> '' THEN CONCAT(COALESCE(AIR_CLASS_OF_SERVICE_CD_8, ''), '_')
  38. ELSE '' END,
  39. CASE
  40. WHEN COALESCE(LEFT(FARE_BASIS_TICKET_DESIGNATION_NM_1, 1), '') <> '' THEN CONCAT(COALESCE(LEFT(FARE_BASIS_TICKET_DESIGNATION_NM_1, 1), ''), '_')
  41. ELSE '' END,
  42. CASE
  43. WHEN COALESCE(LEFT(FARE_BASIS_TICKET_DESIGNATION_NM_2, 1), '') <> '' THEN CONCAT(COALESCE(LEFT(FARE_BASIS_TICKET_DESIGNATION_NM_2, 1), ''), '_')
  44. ELSE '' END,
  45. CASE
  46. WHEN COALESCE(LEFT(FARE_BASIS_TICKET_DESIGNATION_NM_3, 1), '') <> '' THEN CONCAT(COALESCE(LEFT(FARE_BASIS_TICKET_DESIGNATION_NM_3, 1), ''), '_')
  47. ELSE '' END,
  48. CASE
  49. WHEN COALESCE(LEFT(FARE_BASIS_TICKET_DESIGNATION_NM_4, 1), '') <> '' THEN CONCAT(COALESCE(LEFT(FARE_BASIS_TICKET_DESIGNATION_NM_4, 1), ''), '_')
  50. ELSE '' END,
  51. CASE
  52. WHEN COALESCE(LEFT(FARE_BASIS_TICKET_DESIGNATION_NM_5, 1), '') <> '' THEN CONCAT(COALESCE(LEFT(FARE_BASIS_TICKET_DESIGNATION_NM_5, 1), ''), '_')
  53. ELSE '' END,
  54. CASE
  55. WHEN COALESCE(LEFT(FARE_BASIS_TICKET_DESIGNATION_NM_6, 1), '') <> '' THEN CONCAT(COALESCE(LEFT(FARE_BASIS_TICKET_DESIGNATION_NM_6, 1), ''), '_')
  56. ELSE '' END,
  57. CASE
  58. WHEN COALESCE(LEFT(FARE_BASIS_TICKET_DESIGNATION_NM_7, 1), '') <> '' THEN CONCAT(COALESCE(LEFT(FARE_BASIS_TICKET_DESIGNATION_NM_7, 1), ''), '_')
  59. ELSE '' END,
  60. CASE
  61. WHEN COALESCE(LEFT(FARE_BASIS_TICKET_DESIGNATION_NM_8, 1), '') <> '' THEN CONCAT(COALESCE(LEFT(FARE_BASIS_TICKET_DESIGNATION_NM_8, 1), ''), '_')
  62. ELSE '' END,
  63. CASE
  64. WHEN COALESCE(MARKETING_CARRIER_CD_1, '') <> '' THEN CONCAT(COALESCE(MARKETING_CARRIER_CD_1, ''), '_')
  65. ELSE '' END,
  66. CASE
  67. WHEN COALESCE(MARKETING_CARRIER_CD_2, '') <> '' THEN CONCAT(COALESCE(MARKETING_CARRIER_CD_2, ''), '_')
  68. ELSE '' END,
  69. CASE
  70. WHEN COALESCE(MARKETING_CARRIER_CD_3, '') <> '' THEN CONCAT(COALESCE(MARKETING_CARRIER_CD_3, ''), '_')
  71. ELSE '' END,
  72. CASE
  73. WHEN COALESCE(MARKETING_CARRIER_CD_4, '') <> '' THEN CONCAT(COALESCE(MARKETING_CARRIER_CD_4, ''), '_')
  74. ELSE '' END,
  75. CASE
  76. WHEN COALESCE(MARKETING_CARRIER_CD_5, '') <> '' THEN CONCAT(COALESCE(MARKETING_CARRIER_CD_5, ''), '_')
  77. ELSE '' END,
  78. CASE
  79. WHEN COALESCE(MARKETING_CARRIER_CD_6, '') <> '' THEN CONCAT(COALESCE(MARKETING_CARRIER_CD_6, ''), '_')
  80. ELSE '' END,
  81. CASE
  82. WHEN COALESCE(MARKETING_CARRIER_CD_7, '') <> '' THEN CONCAT(COALESCE(MARKETING_CARRIER_CD_7, ''), '_')
  83. ELSE '' END,
  84. CASE
  85. WHEN COALESCE(MARKETING_CARRIER_CD_8, '') <> '' THEN CONCAT(COALESCE(MARKETING_CARRIER_CD_8, ''), '_')
  86. ELSE '' END, CAST(WEEK(DATE(TICKET_FIRST_TRANSACTION_DT) ) AS string), '_', CAST(WEEK(DATE(CASE
  87. WHEN DEPARTURE_DATE_1 <> '' THEN DEPARTURE_DATE_1
  88. WHEN DEPARTURE_DATE_2 <> '' THEN DEPARTURE_DATE_2
  89. WHEN DEPARTURE_DATE_3 <> '' THEN DEPARTURE_DATE_3
  90. WHEN DEPARTURE_DATE_4 <> '' THEN DEPARTURE_DATE_4
  91. WHEN DEPARTURE_DATE_5 <> '' THEN DEPARTURE_DATE_5
  92. WHEN DEPARTURE_DATE_6 <> '' THEN DEPARTURE_DATE_6
  93. WHEN DEPARTURE_DATE_7 <> '' THEN DEPARTURE_DATE_7
  94. WHEN DEPARTURE_DATE_8 <> '' THEN DEPARTURE_DATE_8 END)) AS string)) AS RTNG_CODE,
  95. REPLACE(REPLACE(CONCAT(COALESCE(ORIGIN_1, ''), '-', COALESCE(DESTINATION_1, ''), '/', COALESCE(ORIGIN_2, ''), '-', COALESCE(DESTINATION_2, ''), '/', COALESCE(ORIGIN_3, ''), '-', COALESCE(DESTINATION_3, ''), '/', COALESCE(ORIGIN_4, ''), '-', COALESCE(DESTINATION_4, ''), '/', COALESCE(ORIGIN_5, ''), '-', COALESCE(DESTINATION_5, ''), '/', COALESCE(ORIGIN_6, ''), '-', COALESCE(DESTINATION_6, ''), '/', COALESCE(ORIGIN_7, ''), '-', COALESCE(DESTINATION_7, ''), '/', COALESCE(ORIGIN_8, ''), '-', COALESCE(DESTINATION_8, '')), '/-', ''), '-/', '') AS ITIN,
  96. CONCAT(COALESCE(MARKETING_CARRIER_CD_1, ''), '-', COALESCE(MARKETING_CARRIER_CD_2, ''), '-', COALESCE(MARKETING_CARRIER_CD_3, ''), '-', COALESCE(MARKETING_CARRIER_CD_4, ''), '-', COALESCE(MARKETING_CARRIER_CD_5, ''), '-', COALESCE(MARKETING_CARRIER_CD_6, ''), '-', COALESCE(MARKETING_CARRIER_CD_7, ''), '-', COALESCE(MARKETING_CARRIER_CD_8, '')) AS CX,
  97. CONCAT(COALESCE(AIR_CLASS_OF_SERVICE_CD_1, ''), COALESCE(AIR_CLASS_OF_SERVICE_CD_2, ''), COALESCE(AIR_CLASS_OF_SERVICE_CD_3, ''), COALESCE(AIR_CLASS_OF_SERVICE_CD_4, ''), COALESCE(AIR_CLASS_OF_SERVICE_CD_5, ''), COALESCE(AIR_CLASS_OF_SERVICE_CD_6, ''), COALESCE(AIR_CLASS_OF_SERVICE_CD_7, ''), COALESCE(AIR_CLASS_OF_SERVICE_CD_8, '')) AS CLS,
  98. FARE,
  99. CASE
  100. WHEN LENGTH(LTRIM(RTRIM(FARE))) > 3 THEN LEFT(LTRIM(RTRIM(FARE)), 3)
  101. ELSE ''
  102. END AS FARE_Currency,
  103. CASE
  104. WHEN LENGTH(LTRIM(RTRIM(FARE))) > 3 THEN RIGHT(LTRIM(RTRIM(FARE)), LENGTH(LTRIM(RTRIM(FARE))) - 4)
  105. ELSE ''
  106. END AS FARE_Amount,
  107. TAX_FEE_AMT AS TAXES,
  108. TOTAL_TRANSACTION_TICKET_DOC_AMT AS TOTL,
  109. CASE
  110. WHEN LENGTH(LTRIM(RTRIM(TOTAL_TRANSACTION_TICKET_DOC_AMT))) > 3 THEN LEFT(LTRIM(RTRIM(TOTAL_TRANSACTION_TICKET_DOC_AMT)), 3)
  111. ELSE ''
  112. END AS TOTL_Currency,
  113. CASE
  114. WHEN LENGTH(LTRIM(RTRIM(TOTAL_TRANSACTION_TICKET_DOC_AMT))) > 3 THEN RIGHT(LTRIM(RTRIM(TOTAL_TRANSACTION_TICKET_DOC_AMT)), LENGTH(LTRIM(RTRIM(TOTAL_TRANSACTION_TICKET_DOC_AMT))) - 4)
  115. ELSE ''
  116. END AS TOTL_Amount,
  117. ORIGIN AS IATA_FROM
  118. FROM (
  119. SELECT
  120. PLAT_CRX_ALPHA,
  121. ACTUAL_TKT_NBR,
  122. INTEGER(COUNT(LTRIM(RTRIM(COUPON_NUMBER)))) AS COUPON_NUMBER_NUM,
  123. INTEGER(MIN(LTRIM(RTRIM(COUPON_NUMBER)))) AS COUPON_NUMBER_MIN,
  124. INTEGER(MAX(LTRIM(RTRIM(COUPON_NUMBER)))) AS COUPON_NUMBER_MAX,
  125. FARE,
  126. CURRENCY_CD,
  127. TICKET_FIRST_TRANSACTION_DT,
  128. AUDIT_FARE_AMT,
  129. AUDIT_TOTAL_AMT,
  130. TOTAL_TRANSACTION_TICKET_DOC_AMT,
  131. OPERATING_CARRIER_CD,
  132. COALESCE(MAX(CASE
  133. WHEN COUPON_NUMBER = '1' THEN ORIGIN
  134. ELSE NULL END), MAX(CASE
  135. WHEN COUPON_NUMBER = '2' THEN ORIGIN
  136. ELSE NULL END), MAX(CASE
  137. WHEN COUPON_NUMBER = '3' THEN ORIGIN
  138. ELSE NULL END), MAX(CASE
  139. WHEN COUPON_NUMBER = '4' THEN ORIGIN
  140. ELSE NULL END), MAX(CASE
  141. WHEN COUPON_NUMBER = '5' THEN ORIGIN
  142. ELSE NULL END), MAX(CASE
  143. WHEN COUPON_NUMBER = '6' THEN ORIGIN
  144. ELSE NULL END), MAX(CASE
  145. WHEN COUPON_NUMBER = '7' THEN ORIGIN
  146. ELSE NULL END), MAX(CASE
  147. WHEN COUPON_NUMBER = '8' THEN ORIGIN
  148. ELSE NULL END), 'XXX') AS ORIGIN,
  149. SUM(TAX_FEE_AMT) AS TAX_FEE_AMT,
  150. MAX(CASE
  151. WHEN COUPON_NUMBER = '1' THEN AIR_CLASS_OF_SERVICE_CD
  152. ELSE '' END) AS AIR_CLASS_OF_SERVICE_CD_1,
  153. MAX(CASE
  154. WHEN COUPON_NUMBER = '2' THEN AIR_CLASS_OF_SERVICE_CD
  155. ELSE '' END) AS AIR_CLASS_OF_SERVICE_CD_2,
  156. MAX(CASE
  157. WHEN COUPON_NUMBER = '3' THEN AIR_CLASS_OF_SERVICE_CD
  158. ELSE '' END) AS AIR_CLASS_OF_SERVICE_CD_3,
  159. MAX(CASE
  160. WHEN COUPON_NUMBER = '4' THEN AIR_CLASS_OF_SERVICE_CD
  161. ELSE '' END) AS AIR_CLASS_OF_SERVICE_CD_4,
  162. MAX(CASE
  163. WHEN COUPON_NUMBER = '5' THEN AIR_CLASS_OF_SERVICE_CD
  164. ELSE '' END) AS AIR_CLASS_OF_SERVICE_CD_5,
  165. MAX(CASE
  166. WHEN COUPON_NUMBER = '6' THEN AIR_CLASS_OF_SERVICE_CD
  167. ELSE '' END) AS AIR_CLASS_OF_SERVICE_CD_6,
  168. MAX(CASE
  169. WHEN COUPON_NUMBER = '7' THEN AIR_CLASS_OF_SERVICE_CD
  170. ELSE '' END) AS AIR_CLASS_OF_SERVICE_CD_7,
  171. MAX(CASE
  172. WHEN COUPON_NUMBER = '8' THEN AIR_CLASS_OF_SERVICE_CD
  173. ELSE '' END) AS AIR_CLASS_OF_SERVICE_CD_8,
  174. MAX(CASE
  175. WHEN COUPON_NUMBER = '1' THEN ORIGIN
  176. ELSE '' END) AS ORIGIN_1,
  177. MAX(CASE
  178. WHEN COUPON_NUMBER = '2' THEN ORIGIN
  179. ELSE '' END) AS ORIGIN_2,
  180. MAX(CASE
  181. WHEN COUPON_NUMBER = '3' THEN ORIGIN
  182. ELSE '' END) AS ORIGIN_3,
  183. MAX(CASE
  184. WHEN COUPON_NUMBER = '4' THEN ORIGIN
  185. ELSE '' END) AS ORIGIN_4,
  186. MAX(CASE
  187. WHEN COUPON_NUMBER = '5' THEN ORIGIN
  188. ELSE '' END) AS ORIGIN_5,
  189. MAX(CASE
  190. WHEN COUPON_NUMBER = '6' THEN ORIGIN
  191. ELSE '' END) AS ORIGIN_6,
  192. MAX(CASE
  193. WHEN COUPON_NUMBER = '7' THEN ORIGIN
  194. ELSE '' END) AS ORIGIN_7,
  195. MAX(CASE
  196. WHEN COUPON_NUMBER = '8' THEN ORIGIN
  197. ELSE '' END) AS ORIGIN_8,
  198. MAX(CASE
  199. WHEN COUPON_NUMBER = '1' THEN DESTINATION
  200. ELSE '' END) AS DESTINATION_1,
  201. MAX(CASE
  202. WHEN COUPON_NUMBER = '2' THEN DESTINATION
  203. ELSE '' END) AS DESTINATION_2,
  204. MAX(CASE
  205. WHEN COUPON_NUMBER = '3' THEN DESTINATION
  206. ELSE '' END) AS DESTINATION_3,
  207. MAX(CASE
  208. WHEN COUPON_NUMBER = '4' THEN DESTINATION
  209. ELSE '' END) AS DESTINATION_4,
  210. MAX(CASE
  211. WHEN COUPON_NUMBER = '5' THEN DESTINATION
  212. ELSE '' END) AS DESTINATION_5,
  213. MAX(CASE
  214. WHEN COUPON_NUMBER = '6' THEN DESTINATION
  215. ELSE '' END) AS DESTINATION_6,
  216. MAX(CASE
  217. WHEN COUPON_NUMBER = '7' THEN DESTINATION
  218. ELSE '' END) AS DESTINATION_7,
  219. MAX(CASE
  220. WHEN COUPON_NUMBER = '8' THEN DESTINATION
  221. ELSE '' END) AS DESTINATION_8,
  222. MAX(CASE
  223. WHEN COUPON_NUMBER = '1' THEN FARE_BASIS_TICKET_DESIGNATION_NM
  224. ELSE '' END) AS FARE_BASIS_TICKET_DESIGNATION_NM_1,
  225. MAX(CASE
  226. WHEN COUPON_NUMBER = '2' THEN FARE_BASIS_TICKET_DESIGNATION_NM
  227. ELSE '' END) AS FARE_BASIS_TICKET_DESIGNATION_NM_2,
  228. MAX(CASE
  229. WHEN COUPON_NUMBER = '3' THEN FARE_BASIS_TICKET_DESIGNATION_NM
  230. ELSE '' END) AS FARE_BASIS_TICKET_DESIGNATION_NM_3,
  231. MAX(CASE
  232. WHEN COUPON_NUMBER = '4' THEN FARE_BASIS_TICKET_DESIGNATION_NM
  233. ELSE '' END) AS FARE_BASIS_TICKET_DESIGNATION_NM_4,
  234. MAX(CASE
  235. WHEN COUPON_NUMBER = '5' THEN FARE_BASIS_TICKET_DESIGNATION_NM
  236. ELSE '' END) AS FARE_BASIS_TICKET_DESIGNATION_NM_5,
  237. MAX(CASE
  238. WHEN COUPON_NUMBER = '6' THEN FARE_BASIS_TICKET_DESIGNATION_NM
  239. ELSE '' END) AS FARE_BASIS_TICKET_DESIGNATION_NM_6,
  240. MAX(CASE
  241. WHEN COUPON_NUMBER = '7' THEN FARE_BASIS_TICKET_DESIGNATION_NM
  242. ELSE '' END) AS FARE_BASIS_TICKET_DESIGNATION_NM_7,
  243. MAX(CASE
  244. WHEN COUPON_NUMBER = '8' THEN FARE_BASIS_TICKET_DESIGNATION_NM
  245. ELSE '' END) AS FARE_BASIS_TICKET_DESIGNATION_NM_8,
  246. MAX(CASE
  247. WHEN COUPON_NUMBER = '1' THEN DEPARTURE_DATE
  248. ELSE '' END) AS DEPARTURE_DATE_1,
  249. MAX(CASE
  250. WHEN COUPON_NUMBER = '2' THEN DEPARTURE_DATE
  251. ELSE '' END) AS DEPARTURE_DATE_2,
  252. MAX(CASE
  253. WHEN COUPON_NUMBER = '3' THEN DEPARTURE_DATE
  254. ELSE '' END) AS DEPARTURE_DATE_3,
  255. MAX(CASE
  256. WHEN COUPON_NUMBER = '4' THEN DEPARTURE_DATE
  257. ELSE '' END) AS DEPARTURE_DATE_4,
  258. MAX(CASE
  259. WHEN COUPON_NUMBER = '5' THEN DEPARTURE_DATE
  260. ELSE '' END) AS DEPARTURE_DATE_5,
  261. MAX(CASE
  262. WHEN COUPON_NUMBER = '6' THEN DEPARTURE_DATE
  263. ELSE '' END) AS DEPARTURE_DATE_6,
  264. MAX(CASE
  265. WHEN COUPON_NUMBER = '7' THEN DEPARTURE_DATE
  266. ELSE '' END) AS DEPARTURE_DATE_7,
  267. MAX(CASE
  268. WHEN COUPON_NUMBER = '8' THEN DEPARTURE_DATE
  269. ELSE '' END) AS DEPARTURE_DATE_8,
  270. MAX(CASE
  271. WHEN COUPON_NUMBER = '1' THEN MARKETING_CARRIER_CD
  272. ELSE '' END) AS MARKETING_CARRIER_CD_1,
  273. MAX(CASE
  274. WHEN COUPON_NUMBER = '2' THEN MARKETING_CARRIER_CD
  275. ELSE '' END) AS MARKETING_CARRIER_CD_2,
  276. MAX(CASE
  277. WHEN COUPON_NUMBER = '3' THEN MARKETING_CARRIER_CD
  278. ELSE '' END) AS MARKETING_CARRIER_CD_3,
  279. MAX(CASE
  280. WHEN COUPON_NUMBER = '4' THEN MARKETING_CARRIER_CD
  281. ELSE '' END) AS MARKETING_CARRIER_CD_4,
  282. MAX(CASE
  283. WHEN COUPON_NUMBER = '5' THEN MARKETING_CARRIER_CD
  284. ELSE '' END) AS MARKETING_CARRIER_CD_5,
  285. MAX(CASE
  286. WHEN COUPON_NUMBER = '6' THEN MARKETING_CARRIER_CD
  287. ELSE '' END) AS MARKETING_CARRIER_CD_6,
  288. MAX(CASE
  289. WHEN COUPON_NUMBER = '7' THEN MARKETING_CARRIER_CD
  290. ELSE '' END) AS MARKETING_CARRIER_CD_7,
  291. MAX(CASE
  292. WHEN COUPON_NUMBER = '8' THEN MARKETING_CARRIER_CD
  293. ELSE '' END) AS MARKETING_CARRIER_CD_8
  294. FROM (
  295. SELECT
  296. PLAT_CRX_ALPHA,
  297. COUPON_NUMBER,
  298. ACTUAL_TKT_NBR,
  299. AIR_CLASS_OF_SERVICE_CD,
  300. 'AIR_CLASS_OF_SERVICE_CD_' + LTRIM(RTRIM(COUPON_NUMBER)) AS AIR_CLASS_OF_SERVICE_CD_COUP,
  301. FARE,
  302. CURRENCY_CD,
  303. FARE_BASIS_TICKET_DESIGNATION_NM,
  304. 'FARE_BASIS_TICKET_DESIGNATION_NM_' + LTRIM(RTRIM(COUPON_NUMBER)) AS FARE_BASIS_TICKET_DESIGNATION_NM_COUP,
  305. TICKET_FIRST_TRANSACTION_DT,
  306. AUDIT_FARE_AMT,
  307. AUDIT_TOTAL_AMT,
  308. TOTAL_TRANSACTION_TICKET_DOC_AMT,
  309. OPERATING_CARRIER_CD,
  310. MARKETING_CARRIER_CD,
  311. 'MARKETING_CARRIER_CD_' + LTRIM(RTRIM(COUPON_NUMBER)) AS MARKETING_CARRIER_CD_COUP,
  312. DEPARTURE_DATE,
  313. 'DEPARTURE_DATE_' + LTRIM(RTRIM(COUPON_NUMBER)) AS DEPARTURE_DATE_COUP,
  314. ORIGIN,
  315. 'ORIGIN_' + LTRIM(RTRIM(COUPON_NUMBER)) AS ORIGIN_COUP,
  316. DESTINATION,
  317. 'DESTINATION_' + LTRIM(RTRIM(COUPON_NUMBER)) AS DESTINATION_COUP,
  318. TAX_FEE_AMT
  319. FROM (
  320. SELECT
  321. PLAT_CRX_ALPHA,
  322. COUPON_NUMBER,
  323. LTRIM(RTRIM(ACTUAL_TKT_NBR)) AS ACTUAL_TKT_NBR,
  324. LTRIM(RTRIM(AIR_CLASS_OF_SERVICE_CD)) AS AIR_CLASS_OF_SERVICE_CD,
  325. FARE,
  326. CURRENCY_CD,
  327. LTRIM(RTRIM(FARE_BASIS_TICKET_DESIGNATION_NM)) AS FARE_BASIS_TICKET_DESIGNATION_NM,
  328. LTRIM(RTRIM(TICKET_FIRST_TRANSACTION_DT)) AS TICKET_FIRST_TRANSACTION_DT,
  329. LTRIM(RTRIM(AUDIT_FARE_AMT)) AS AUDIT_FARE_AMT,
  330. LTRIM(RTRIM(AUDIT_TOTAL_AMT)) AS AUDIT_TOTAL_AMT,
  331. LTRIM(RTRIM(TOTAL_TRANSACTION_TICKET_DOC_AMT)) AS TOTAL_TRANSACTION_TICKET_DOC_AMT,
  332. LTRIM(RTRIM(OPERATING_CARRIER_CD)) AS OPERATING_CARRIER_CD,
  333. LTRIM(RTRIM(MARKETING_CARRIER_CD)) AS MARKETING_CARRIER_CD,
  334. LTRIM(RTRIM(DEPARTURE_DATE)) AS DEPARTURE_DATE,
  335. LTRIM(RTRIM(ORIGIN)) AS ORIGIN,
  336. LTRIM(RTRIM(DESTINATION)) AS DESTINATION,
  337. SUM(CAST(TAX_FEE_AMT AS float)) AS TAX_FEE_AMT
  338. FROM
  339. [travel-observatory:ds_travel_observatory.fare_test_100]
  340. WHERE
  341. LTRIM(RTRIM(EXCHANGE_FLG)) = 'N'
  342. GROUP BY
  343. PLAT_CRX_ALPHA,
  344. COUPON_NUMBER,
  345. ACTUAL_TKT_NBR,
  346. AIR_CLASS_OF_SERVICE_CD,
  347. FARE,
  348. CURRENCY_CD,
  349. FARE_BASIS_TICKET_DESIGNATION_NM,
  350. TICKET_FIRST_TRANSACTION_DT,
  351. AUDIT_FARE_AMT,
  352. AUDIT_TOTAL_AMT,
  353. TOTAL_TRANSACTION_TICKET_DOC_AMT,
  354. OPERATING_CARRIER_CD,
  355. MARKETING_CARRIER_CD,
  356. DEPARTURE_DATE,
  357. ORIGIN,
  358. DESTINATION ) AS dt2 ) AS Dt
  359. GROUP BY
  360. PLAT_CRX_ALPHA,
  361. ACTUAL_TKT_NBR,
  362. FARE,
  363. CURRENCY_CD,
  364. TICKET_FIRST_TRANSACTION_DT,
  365. AUDIT_FARE_AMT,
  366. AUDIT_TOTAL_AMT,
  367. TOTAL_TRANSACTION_TICKET_DOC_AMT,
  368. OPERATING_CARRIER_CD ) AS Tbb
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement
Public Pastes
Advertisement
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy.  OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!