Updated example query

SELECT DISTINCT STG_REV_APPORTION_CSC_NO.BUSINESS_DATE AS BUSINESS_DATE,
  STG_REV_APPORTION_CSC_NO.CARD_MANAGER                AS CARD_MANAGER,
  STG_REV_APPORTION_CSC_NO.OPERATOR                    AS OPERATOR,
  STG_REV_APPORTION_CSC_NO.MSG_TYPE_CD                 AS MSG_TYPE_CD,
  STG_REV_APPORTION_CSC_NO.TA_SETTLEMENT_DATE          AS TA_SETTLEMENT_DATE,
  STG_REV_APPORTION_CSC_NO.PROCESSING_DATE             AS PROCESSING_DATE,
  STG_REV_APPORTION_CSC_NO.EXIT_DATE                   AS EXIT_DATE,
  STG_REV_APPORTION_CSC_NO.TICKET_TYPE                 AS TICKET_TYPE,
  STG_REV_APPORTION_CSC_NO.ENTRY_STATION               AS ENTRY_STATION,
  STG_REV_APPORTION_CSC_NO.EXIT_STATION                AS EXIT_STATION,
  STG_REV_APPORTION_CSC_NO.TXN_AMT                     AS TXN_AMT,
  STG_REV_APPORTION_CSC_NO.APPOR_DIST                  AS APPOR_DIST,
  STG_REV_APPORTION_CSC_NO.BASE_FARE                   AS BASE_FARE,
  STG_REV_APPORTION_CSC_NO.SURCHARGE_AIRCON            AS SURCHARGE_AIRCON,
  STG_REV_APPORTION_CSC_NO.SURCHARGE_PREMIUM_RAIL      AS SURCHARGE_PREMIUM_RAIL,
  STG_REV_APPORTION_CSC_NO.SURCHARGE_EXPRESS_BUS       AS SURCHARGE_EXPRESS_BUS,
  STG_REV_APPORTION_CSC_NO.SURCHARGE4                  AS SURCHARGE4,
  STG_REV_APPORTION_CSC_NO.SURCHARGE5                  AS SURCHARGE5,
  STG_REV_APPORTION_CSC_NO.TOTAL_APPOR_FARE_GRADE_AMT  AS TOTAL_APPOR_FARE_GRADE_AMT,
  CASE
    WHEN STG_REV_APPORTION_CSC_NO.MSG_TYPE_CD NOT                                              IN (50019,50022)
    AND ISNULL(STG_REV_APPORTION_CSC_NO.DISC_AMT,0)                                             = 0
    AND ISNULL(STG_REV_APPORTION_CSC_NO1.DISC_AMT,ISNULL(STG_REV_APPORTION_CSC_NO2.DISC_AMT,0)) > 0
    THEN ISNULL(STG_REV_APPORTION_CSC_NO1.DISC_AMT,ISNULL(STG_REV_APPORTION_CSC_NO2.DISC_AMT,0))
    ELSE STG_REV_APPORTION_CSC_NO.DISC_AMT
  END                                    AS DISC_AMT,
  STG_REV_APPORTION_CSC_NO.SOURCE_TABLE  AS SOURCE_TABLE,
  STG_REV_APPORTION_CSC_NO.PURSE_TXN_CTR AS PURSE_TXN_CTR,
  STG_REV_APPORTION_CSC_NO.CSC_APP_NO    AS CSC_APP_NO,
  CASE
    WHEN STG_SEP_TEMP.BUSINESS_DATE IS NOT NULL
    THEN 'Y'
    WHEN STG_SEP_VL4357.BUSINESS_DATE IS NOT NULL
    THEN 'Y'
    WHEN STG_SEP_VL13070.BUSINESS_DATE IS NOT NULL
    THEN 'Y'
    ELSE 'N'
  END AS MATCH_IND
INTO smrt_stg.dbo.STG_REV_APPORTION_CSC_01A
FROM smrt_stg.dbo.STG_REV_APPORTION_CSC_NO STG_REV_APPORTION_CSC_NO
LEFT OUTER JOIN smrt_stg.dbo.STG_SEP_TEMP STG_SEP_TEMP
ON (STG_REV_APPORTION_CSC_NO.BUSINESS_DATE=STG_SEP_TEMP.BUSINESS_DATE
AND STG_REV_APPORTION_CSC_NO.CSC_APP_NO   =STG_SEP_TEMP.CSC_APP_NO
AND STG_REV_APPORTION_CSC_NO.PURSE_TXN_CTR=STG_SEP_TEMP.PURSE_TXN_CTR)
LEFT OUTER JOIN smrt_stg.dbo.STG_REV_APPORTION_CSC_NO STG_REV_APPORTION_CSC_NO1
ON (STG_REV_APPORTION_CSC_NO.BUSINESS_DATE=STG_REV_APPORTION_CSC_NO1.BUSINESS_DATE
AND STG_REV_APPORTION_CSC_NO.CSC_APP_NO   =STG_REV_APPORTION_CSC_NO1.CSC_APP_NO
AND STG_REV_APPORTION_CSC_NO.EXIT_DATE    =STG_REV_APPORTION_CSC_NO1.EXIT_DATE
AND STG_REV_APPORTION_CSC_NO.OPERATOR     =STG_REV_APPORTION_CSC_NO1.OPERATOR)
LEFT OUTER JOIN smrt_stg.dbo.STG_REV_APPORTION_CSC_NO STG_REV_APPORTION_CSC_NO2
ON (STG_REV_APPORTION_CSC_NO.BUSINESS_DATE=STG_REV_APPORTION_CSC_NO2.BUSINESS_DATE
AND STG_REV_APPORTION_CSC_NO.CSC_APP_NO   =STG_REV_APPORTION_CSC_NO2.CSC_APP_NO
AND STG_REV_APPORTION_CSC_NO.EXIT_DATE    =STG_REV_APPORTION_CSC_NO2.EXIT_DATE
AND STG_REV_APPORTION_CSC_NO.OPERATOR     =STG_REV_APPORTION_CSC_NO2.OPERATOR)
LEFT OUTER JOIN smrt_stg.dbo.STG_SEP_VL STG_SEP_VL13070
ON (STG_SEP_VL13070.BUSINESS_DATE =STG_REV_APPORTION_CSC_NO.BUSINESS_DATE
AND STG_SEP_VL13070.CSC_APP_NO    =STG_REV_APPORTION_CSC_NO.CSC_APP_NO
AND STG_SEP_VL13070.JOURNEY_NO    =STG_REV_APPORTION_CSC_NO.JOURNEY_NO
AND (STG_SEP_VL13070.TRIP_NO +1)  =STG_REV_APPORTION_CSC_NO.TRIP_NO)
LEFT OUTER JOIN smrt_stg.dbo.STG_SEP_VL STG_SEP_VL4357
ON (STG_SEP_VL4357.BUSINESS_DATE          =STG_REV_APPORTION_CSC_NO.BUSINESS_DATE
AND STG_SEP_VL4357.CSC_APP_NO             = STG_REV_APPORTION_CSC_NO.CSC_APP_NO
AND STG_SEP_VL4357.JOURNEY_NO             = STG_REV_APPORTION_CSC_NO.JOURNEY_NO
AND STG_SEP_VL4357.PURSE_TXN_CTR          = STG_REV_APPORTION_CSC_NO.PURSE_TXN_CTR)
WHERE STG_SEP_VL13070.MSG_TYPE_CD         = 13070
AND STG_SEP_VL4357.MSG_TYPE_CD            = 4357
AND STG_REV_APPORTION_CSC_NO.MSG_TYPE_CD IN ( 4098,4137,4141,4142,4144,4145,4147,4148,4152,4352, 50016,50015,50019,4357 )
AND STG_REV_APPORTION_CSC_NO1.MSG_TYPE_CD = 50019
AND STG_REV_APPORTION_CSC_NO2.MSG_TYPE_CD = 50022