SELECT *
FROM
  (SELECT EMPENROll.EmployeeID AS EmployeeID,
          'M' + CAST(EMPENROll.ID AS varchar(25)) AS EnrollmentID,
          EMPENROll.PlanDesignID,

     (SELECT BenefitTypeID
      FROM PLanDesign
      WHERE ID = EMPENROll.PlanDesignID) AS BenefitTypeID,
          EMPENROllCOV.ApprovedCoverageAmount,
          'C' + cast(EMPENROllCOV.ID AS varchar(25)) AS CoverageID,
          EMPENROllCOV.ID AS MasterCoverageID,
          '0' AS IsIntermediate,

     (SELECT UseEmployerAddress
      FROM Employee
      WHERE ID = EMPENROll.EmployeeID ) AS UseEmployerAddress,
          EMPENROll.TerminationDate,
          EMPENROll.TerminationDate AS ElectionTermDate,
          EMPENROll.EffectiveDate,
          EMPENROLL.DateTimeStamp,
          EMPENROllCOV.MemberTypeID,
          Replace(cast(EMPENROLL.EmployeeID AS nvarchar), cast(EMPENROLL.EmployeeID AS nvarchar),'') AS OES_VIEW_ACA_Employee_Enrollment_WaiveSendOnlyOneRecordPerMemberonRowwise9,
          Employee.ID AS MemberID,
          Employee.SocialSecurityNo AS EmployeeSocialSecurityNo2,
          Employee.SocialSecurityNo AS EmployeeSocialSecurityNo3,
          Employee.FirstName AS EmployeeFirstName4,
          Employee.MiddleInitial AS EmployeeMiddleInitial5,
          Employee.LastName AS EmployeeLastName6,
          Employee.GenderID AS EmployeeGenderID7,
          convert(nvarchar(10),cast(Employee.DateOfBirth AS datetime),101) AS EmployeeDateOfBirth8
   FROM Employee
   INNER JOIN OES_VIEW_ACA_Employee_Enrollment_Waive EMPENROll ON EMPENROll.EmployeeID = Employee.ID
   LEFT JOIN PlanDesign Pd ON Pd.ID = EMPENROll.PlanDesignID
   LEFT OUTER JOIN OES_VIEW_ACA_Employee_Enrollment_Coverage_Waive EMPENROllCOV ON EMPENROll.ID = EMPENROllCOV.Employee_EnrollmentID
   AND EMPENROllCOV.MemberTypeID = 1
   INNER JOIN ImportexportEnrollmentData IEE ON IEE.CoverageID = EMPENROllCOV.ID
   WHERE ((EMPENROLL.TerminationDate IS NULL)
          OR (DateDiff(DAY, EMPENROLL.EffectiveDate, EMPENROLL.TerminationDate) >= 0)
          OR (EMPENROLL.ID IN (0)))
     AND EMPENROll.IsApprovalPending = 0
     AND EMPENROll.ID <= 254434
     AND EMPENROll.PlanDesignID IN (56,
                                    57,
                                    122,
                                    133,
                                    148,
                                    150,
                                    205,
                                    0)
     AND ((((DATEDIFF(DAY,'2017/8/23',EMPENROll.EffectiveDate) <= 0
             AND EMPENROll.TerminationDate IS NULL)
            OR (DATEDIFF(DAY,'2017/8/23',EMPENROll.EffectiveDate) <= 0
                AND datediff(DAY,'2017/1/1',EMPENROll.EffectiveDate) >= 0
                AND EMPENROll.TerminationDate IS NOT NULL)
            OR (DATEDIFF(DAY,'2017/8/23',EMPENROll.EffectiveDate) <= 0
                AND DATEDIFF(DAY,'2017/1/1',EMPENROll.TerminationDate) >= 0))
           AND (DATEDIFF(DAY,GetDate(),EMPENROll.EffectiveDate) <= 0)))
     AND IEE.IsIntermediate = 0
     AND IEE.IsExported = 0
     AND IEE.ExportedDate IS NULL
     AND IEE.TemplateID =72
     AND EMPENROll.EmployeeID IN
       (SELECT EmployeeID
        FROM ImportExportChangeEmployeeList
        WHERE TemplateID = 72
          AND UserID = 1)
     AND EMPENROLL.EmployeeID IN
       (SELECT ID
        FROM Employee
        WHERE Employee.ID IN
            (SELECT EmployeeID
             FROM Employee_Payroll
             WHERE PayrollScheduleID IN (1,
                                         5,
                                         3,
                                         2,
                                         6,
                                         4)) )
     AND Employee.EmployeeStatusID IN (1,
                                       2,
                                       6,
                                       4,
                                       7,
                                       9,
                                       10,
                                       8,
                                       3,
                                       5)
   UNION ALL SELECT EMPENROll.EmployeeID AS EmployeeID,
                    'MI' + CAST(isnull(EMPENROll.ID,0) AS varchar(25)) AS EnrollmentID,
                    EMPENROll.PlanDesignID,

     (SELECT BenefitTypeID
      FROM PLanDesign
      WHERE ID = EMPENROll.PlanDesignID) AS BenefitTypeID,
                    EMPENROllCOV.ApprovedCoverageAmount,
                    'IC' + cast(EMPENROllCOV.ID AS varchar(25)) AS CoverageID,
                    EMPENROllCOV.ID AS MasterCoverageID,
                    '1' AS IsIntermediate,

     (SELECT UseEmployerAddress
      FROM Employee
      WHERE ID = EMPENROll.EmployeeID ) AS UseEmployerAddress,
                    EMPENROll.TerminationDate,
                    EMPENROll.TerminationDate AS ElectionTermDate,
                    EMPENROll.EffectiveDate,
                    EMPENROLL.DateTimeStamp,
                    EMPENROllCOV.MemberTypeID,
                    Replace(cast(EMPENROLL.EmployeeID AS nvarchar), cast(EMPENROLL.EmployeeID AS nvarchar),'') AS OES_VIEW_ACA_Employee_Enrollment_WaiveSendOnlyOneRecordPerMemberonRowwise9,
                    Employee.ID AS MemberID,
                    Employee.SocialSecurityNo AS EmployeeSocialSecurityNo2,
                    Employee.SocialSecurityNo AS EmployeeSocialSecurityNo3,
                    Employee.FirstName AS EmployeeFirstName4,
                    Employee.MiddleInitial AS EmployeeMiddleInitial5,
                    Employee.LastName AS EmployeeLastName6,
                    Employee.GenderID AS EmployeeGenderID7,
                    convert(nvarchar(10),cast(Employee.DateOfBirth AS datetime),101) AS EmployeeDateOfBirth8
   FROM Employee
   INNER JOIN OES_VIEW_ACA_Employee_Enrollment_Intermediate_Waive EMPENROll ON EMPENROll.EmployeeID = Employee.ID
   LEFT JOIN PlanDesign Pd ON Pd.ID = EMPENROll.PlanDesignID
   LEFT OUTER JOIN OES_VIEW_ACA_Employee_Enrollment_Coverage_Intermediate_Waive EMPENROllCOV ON EMPENROll.ID = EMPENROllCOV.Employee_Enrollment_IntermediateID
   AND EMPENROllCOV.MemberTypeID = 1
   INNER JOIN ImportexportEnrollmentData IEE ON IEE.CoverageID = EMPENROllCOV.ID
   WHERE ((EMPENROLL.TerminationDate IS NULL)
          OR (DateDiff(DAY, EMPENROLL.EffectiveDate, EMPENROLL.TerminationDate) >= 0)
          OR (EMPENROLL.ID IN (0)))
     AND EMPENROll.IsApprovalPending = 0
     AND EMPENROll.ID <= 0
     AND EMPENROll.PlanDesignID IN (56,
                                    57,
                                    122,
                                    133,
                                    148,
                                    150,
                                    205,
                                    0)
     AND ((((DATEDIFF(DAY,'2017/8/23',EMPENROll.EffectiveDate) <= 0
             AND EMPENROll.TerminationDate IS NULL)
            OR (DATEDIFF(DAY,'2017/8/23',EMPENROll.EffectiveDate) <= 0
                AND datediff(DAY,'2017/1/1',EMPENROll.EffectiveDate) >= 0
                AND EMPENROll.TerminationDate IS NOT NULL)
            OR (DATEDIFF(DAY,'2017/8/23',EMPENROll.EffectiveDate) <= 0
                AND DATEDIFF(DAY,'2017/1/1',EMPENROll.TerminationDate) >= 0))
           AND (DATEDIFF(DAY,GetDate(),EMPENROll.EffectiveDate) <= 0)))
     AND IEE.IsIntermediate = 1
     AND IEE.IsExported = 0
     AND IEE.ExportedDate IS NULL
     AND IEE.TemplateID =72
     AND EMPENROll.EmployeeID IN
       (SELECT EmployeeID
        FROM ImportExportChangeEmployeeList
        WHERE TemplateID = 72
          AND UserID = 1)
     AND EMPENROLL.EmployeeID IN
       (SELECT ID
        FROM Employee
        WHERE Employee.ID IN
            (SELECT EmployeeID
             FROM Employee_Payroll
             WHERE PayrollScheduleID IN (1,
                                         5,
                                         3,
                                         2,
                                         6,
                                         4)) )
     AND Employee.EmployeeStatusID IN (1,
                                       2,
                                       6,
                                       4,
                                       7,
                                       9,
                                       10,
                                       8,
                                       3,
                                       5) ) AS Employee
WHERE Employee.EnrollmentID IN
    (SELECT max(EID)
     FROM
       (SELECT top 1 'M' + cast(EE.ID AS varchar(25)) AS EID
        FROM OES_VIEW_ACA_Employee_Enrollment_Coverage_Waive EEC
        INNER JOIN OES_VIEW_ACA_Employee_Enrollment_Waive EE ON EEC.Employee_EnrollmentID = EE.ID
        INNER JOIN ImportExportEnrollmentData IEE ON IEE.CoverageID = EEC.ID
        AND IEE.IsIntermediate=0
        AND IEE.TemplateID =72
        WHERE EE.PlanDesignID IN
            (SELECT ID
             FROM Plandesign
             WHERE BenefittypeID = Employee.BenefittypeID)
          AND EE.EmployeeID = Employee.EmployeeID
          AND EE.IsApprovalPending = 0
          AND EE.PlanDesignID IN (56,
                                  57,
                                  122,
                                  133,
                                  148,
                                  150,
                                  205,
                                  0)
          AND EE.EmployeeID = Employee.EmployeeID
          AND ((EE.TerminationDate IS NULL)
               OR (DateDiff(DAY, EE.EffectiveDate, EE.TerminationDate) >= 0)
               OR (EE.ID IN (0)))
          AND ((((DATEDIFF(DAY,'2017/8/23',EE.EffectiveDate) <= 0
                  AND EE.TerminationDate IS NULL)
                 OR (DATEDIFF(DAY,'2017/8/23',EE.EffectiveDate) <= 0
                     AND datediff(DAY,'2017/1/1',EE.EffectiveDate) >= 0
                     AND EE.TerminationDate IS NOT NULL)
                 OR (DATEDIFF(DAY,'2017/8/23',EE.EffectiveDate) <= 0
                     AND DATEDIFF(DAY,'2017/1/1',EE.TerminationDate) >= 0))
                AND (DATEDIFF(DAY,GetDate(),EE.EffectiveDate) <= 0)))
        ORDER BY EE.EffectiveDate DESC,EE.ID DESC
        UNION ALL SELECT top 1 'MI' + cast(EE.ID AS varchar(25)) AS EID
        FROM OES_VIEW_ACA_Employee_Enrollment_Coverage_Intermediate_Waive EEC
        INNER JOIN OES_VIEW_ACA_Employee_Enrollment_Intermediate_Waive EE ON EEC.Employee_Enrollment_IntermediateID = EE.ID
        INNER JOIN ImportExportEnrollmentData IEE ON IEE.CoverageID = EEC.ID
        AND IEE.IsIntermediate=1
        AND IEE.TemplateID =72
        WHERE EE.PlanDesignID IN
            (SELECT ID
             FROM Plandesign
             WHERE BenefittypeID = Employee.BenefittypeID)
          AND EE.EmployeeID = Employee.EmployeeID
          AND EE.IsApprovalPending = 0
          AND EE.PlanDesignID IN (56,
                                  57,
                                  122,
                                  133,
                                  148,
                                  150,
                                  205,
                                  0)
          AND EE.EmployeeID = Employee.EmployeeID
          AND ((EE.TerminationDate IS NULL)
               OR (DateDiff(DAY, EE.EffectiveDate, EE.TerminationDate) >= 0)
               OR (EE.ID IN (0)))
          AND ((((DATEDIFF(DAY,'2017/8/23',EE.EffectiveDate) <= 0
                  AND EE.TerminationDate IS NULL)
                 OR (DATEDIFF(DAY,'2017/8/23',EE.EffectiveDate) <= 0
                     AND datediff(DAY,'2017/1/1',EE.EffectiveDate) >= 0
                     AND EE.TerminationDate IS NOT NULL)
                 OR (DATEDIFF(DAY,'2017/8/23',EE.EffectiveDate) <= 0
                     AND DATEDIFF(DAY,'2017/1/1',EE.TerminationDate) >= 0))
                AND (DATEDIFF(DAY,GetDate(),EE.EffectiveDate) <= 0)))
        ORDER BY EE.EffectiveDate DESC,EE.ID DESC) AS MaxChild)
-- below commented statements causing an issue. code changes found under the 
	JIRA ID-wt-10152.
  --INNER JOIN ImportExportEnrollmentData IEE ON IEE.CoverageID = EEC.ID
  --AND IEE.IsIntermediate=0
  --AND IEE.TemplateID =72
  --INNER JOIN ImportExportEnrollmentData IEE ON IEE.CoverageID = EEC.ID
  --AND IEE.IsIntermediate=1
  --AND IEE.TemplateID =72