Guest User

Untitled

a guest
Oct 7th, 2014
221
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1.  SELECT  AL_LIST.OPERATING_NAME Op_Name, +
  2.                  Dom_Arr_Cur_Yr.WEIGHT Dom_Arr_Cur_Yr, +
  3.                  Int_Arr_Cur_Yr.WEIGHT  Int_Arr_Cur_Yr, +
  4.                  NVL(Dom_Arr_Cur_Yr.WEIGHT,0) + NVL(Int_Arr_Cur_Yr.WEIGHT,0)   Total_Cur_Yr, +
  5.                  Dom_Arr_Prev_Yr.WEIGHT  Dom_Arr_Prev_Yr, +
  6.                  Int_Arr_Prev_Yr.WEIGHT Int_Arr_Prev_Yr, +
  7.                  NVL(Dom_Arr_Prev_Yr.WEIGHT,0)  + NVL(Int_Arr_Prev_Yr.WEIGHT,0)   Total_Prev_Yr +
  8.  FROM +
  9.  (SELECT DISTINCT C.COMPANY_NAME, +
  10.                                    NVL(ONAME.OPERATING_NAME,C.COMPANY_NAME) OPERATING_NAME, +
  11.                                    H.AIRLINE_ID, +
  12.                                    A.AL_DC1 AS SIG_STATUS, +
  13.                                    A.AL_DC2 AS CARRIER_TYPE +
  14.    FROM CO_COMPANY  C JOIN ST_AIRLINES A ON A.COMPANY_NUMBER=C.COMPANY_NUMBER
  15.   +$P{V_LIMIT_AL} +
  16.              LEFT JOIN ST_AIRLINE_AGREEMENTS AA ON A.AIRLINE_ID = AA.AIRLINE_ID +
  17.              LEFT JOIN AGREEMENTS AGT ON AA.AGREEMENT_NUMBER = AGT.AGREEMENT_NUMBER +
  18.              LEFT JOIN CO_OPERATING_NAMES ONAME ON AGT.COMPANY_NUMBER = ONAME.COMPANY_NUMBER AND AGT.OPERATING_NAME_ID = ONAME.OPERATING_NAME_ID +
  19.              JOIN ST_REPORT_HEADER H ON H.AIRLINE_ID = A.AIRLINE_ID +
  20.              JOIN ST_STATISTICS S ON H.REPORT_ID = S.REPORT_ID AND S.STATISTICS_ID=2 AND NVL(S.STAT_VALUE,0) <> 0
  21.             +$P{V_LIMIT_BY_AL_TYPE} +
  22.    WHERE H.REPORT_DATE BETWEEN  $P{beginDate}  AND  $P{endDate} +
  23.                  OR H.REPORT_DATE BETWEEN ADD_MONTHS( $P{beginDate} ,-12) AND ADD_MONTHS( $P{endDate} ,-12) +
  24.    ) AL_LIST +
  25.    LEFT JOIN  (SELECT AIRLINE_ID, SUM(STAT_VALUE) AS LANDINGS, SUM(STAT_VALUE * STAT_MULT_COL/1000)  AS WEIGHT +
  26.      FROM ST_REPORT_HEADER H JOIN ST_STATISTICS S ON  H.REPORT_ID = S.REPORT_ID
  27.     +$P{V_LIMIT_BY_AL_TYPE} +
  28.      AND S.STATISTICS_ID=2  AND S.STAT_DC2=/'Domestic/' +
  29.      AND  H.REPORT_DATE BETWEEN  $P{beginDate}  AND  $P{endDate} +
  30.      GROUP BY H.AIRLINE_ID) Dom_Arr_Cur_Yr +
  31.   ON AL_LIST.AIRLINE_ID = Dom_Arr_Cur_Yr.AIRLINE_ID +
  32.  LEFT JOIN(SELECT AIRLINE_ID,  SUM(STAT_VALUE) AS LANDINGS, SUM(STAT_VALUE * STAT_MULT_COL/1000)  AS WEIGHT +
  33.      FROM ST_REPORT_HEADER H JOIN ST_STATISTICS S ON H.REPORT_ID = S.REPORT_ID
  34.     +$P{V_LIMIT_BY_AL_TYPE}+
  35.      AND S.STATISTICS_ID=2    AND S.STAT_DC2=/'Domestic/' +
  36.      AND  H.REPORT_DATE BETWEEN ADD_MONTHS( $P{beginDate} ,-12)  AND ADD_MONTHS( $P{endDate} ,-12) +
  37.      GROUP BY H.AIRLINE_ID) Dom_Arr_Prev_Yr +
  38.   ON AL_LIST.AIRLINE_ID = Dom_Arr_Prev_Yr.AIRLINE_ID +
  39.  LEFT JOIN (SELECT AIRLINE_ID, SUM(STAT_VALUE) AS LANDINGS, SUM(STAT_VALUE * STAT_MULT_COL/1000)  AS WEIGHT +
  40.  FROM ST_REPORT_HEADER H JOIN ST_STATISTICS S ON H.REPORT_ID = S.REPORT_ID
  41.     +$P{V_LIMIT_BY_AL_TYPE} +
  42.     AND S.STATISTICS_ID=2   AND S.STAT_DC2 IN (/'Pre-Cleared/',/'International/') +
  43.     AND  H.REPORT_DATE BETWEEN   $P{beginDate}   AND  $P{endDate} +
  44.     GROUP BY H.AIRLINE_ID) Int_Arr_Cur_Yr +
  45.   ON AL_LIST.AIRLINE_ID = Int_Arr_Cur_Yr.AIRLINE_ID +
  46.  LEFT JOIN(SELECT AIRLINE_ID,  SUM(STAT_VALUE) AS LANDINGS, SUM(STAT_VALUE * STAT_MULT_COL/1000)  AS WEIGHT +
  47.      FROM ST_REPORT_HEADER H JOIN ST_STATISTICS S ON H.REPORT_ID = S.REPORT_ID
  48.     +$P{V_LIMIT_BY_AL_TYPE}+
  49.      AND S.STATISTICS_ID=2      AND S.STAT_DC2  IN (/'Pre-Cleared/',/'International/') +
  50.      AND  H.REPORT_DATE BETWEEN ADD_MONTHS( $P{beginDate} ,-12)    AND ADD_MONTHS( $P{endDate} ,-12) +
  51.      GROUP BY H.AIRLINE_ID) Int_Arr_Prev_Yr +
  52.   ON AL_LIST.AIRLINE_ID = Int_Arr_Prev_Yr.AIRLINE_ID +
  53.  ORDER BY 4 DESC, 7 DESC
RAW Paste Data

Adblocker detected! Please consider disabling it...

We've detected AdBlock Plus or some other adblocking software preventing Pastebin.com from fully loading.

We don't have any obnoxious sound, or popup ads, we actively block these annoying types of ads!

Please add Pastebin.com to your ad blocker whitelist or disable your adblocking software.

×