SELECT *
  FROM (
           SELECT d.ProgramCD,
                  p.ApplicableTo,
                  d.BreakByLevelID,
                  d.BreakByLevelName,
                  d.Revenue,
                  d.Quantity,
                  p.Descr
             FROM DMSAimTMKProgramBreakDownDetail AS d
                  INNER JOIN
                  DMSAimTMKTProgram AS p ON p.ProgramCD = d.ProgramCD
                  INNER JOIN
                  DMSAimCustomer AS c ON c.CustomerCD = 'C000023306' AND
                                         c.LocationCD = 'MAIN' AND
                                         d.Revenue <= c.Avg3M
            WHERE p.ApplicableTo <> 'U' AND
                  d.ProgramCD IN (
                      SELECT ProgramCD
                        FROM (
                                 SELECT DISTINCT p.*,
                                                 CASE WHEN rd.ProgramCD IS NULL THEN 0 ELSE 1 END IsRegister,
                                                 CASE WHEN rd.VisitID = 'SR000608C00002330616a9145220000' THEN 1 ELSE 0 END IsReRegisterInSameVisit,
                                                 CASE WHEN p.RegisterType = 'U' THEN 1 ELSE 0 END IsFromHO,
                                                 CASE WHEN r.Status = 'R' THEN 1 ELSE 0 END IsReject
                                   FROM (
                                            SELECT *
                                              FROM DMSAimTMKTProgram
                                             WHERE UserName = 'SR000608' AND
                                                   (ApplicableTo = 'A' OR
                                                    (ApplicableTo = 'L' AND
                                                     ProgramCD IN (
                                                        SELECT ProgramCD
                                                          FROM DMSAimTMKTProgramApplicableTo
                                                         WHERE DMSAimTMKTProgramApplicableTo.UserName = 'SR000608' AND
                                                               GroupCD IN (
                                                                   SELECT DMSAimTMKTCustomerGroupList.GroupCD
                                                                     FROM DMSAimTMKTCustomerGroupList
                                                                    WHERE DMSAimTMKTCustomerGroupList.UserName = 'SR000608' AND
                                                                          DMSAimTMKTCustomerGroupList.LocationCD = 'MAIN' AND
                                                                          DMSAimTMKTCustomerGroupList.CustomerCD = 'C000023306'
                                                               )
                                                    )
                                                    ) OR
                                                    (ApplicableTo = 'G' AND
                                                     ProgramCD IN (
                                                        SELECT ProgramCD
                                                          FROM DMSAimTMKTProgramApplicableTo
                                                         WHERE DMSAimTMKTProgramApplicableTo.UserName = 'SR000608' AND
                                                               GroupCD IN (
                                                                   SELECT DMSAimTMKTCustomerGroupAttribute.GroupCD
                                                                     FROM DMSAimTMKTCustomerGroupAttribute
                                                                    WHERE (DMSAimTMKTCustomerGroupAttribute.Attribute0 = '1075' OR
                                                                           DMSAimTMKTCustomerGroupAttribute.Attribute0 = '0') AND
                                                                          (DMSAimTMKTCustomerGroupAttribute.Attribute1 = '' OR
                                                                           DMSAimTMKTCustomerGroupAttribute.Attribute1 = '0') AND
                                                                          (DMSAimTMKTCustomerGroupAttribute.Attribute2 = '1085' OR
                                                                           DMSAimTMKTCustomerGroupAttribute.Attribute2 = '0') AND
                                                                          (DMSAimTMKTCustomerGroupAttribute.Attribute3 = '1098' OR
                                                                           DMSAimTMKTCustomerGroupAttribute.Attribute3 = '0') AND
                                                                          (DMSAimTMKTCustomerGroupAttribute.Attribute4 = '1094' OR
                                                                           DMSAimTMKTCustomerGroupAttribute.Attribute4 = '0') AND
                                                                          (DMSAimTMKTCustomerGroupAttribute.Attribute5 = '' OR
                                                                           DMSAimTMKTCustomerGroupAttribute.Attribute5 = '0') AND
                                                                          (DMSAimTMKTCustomerGroupAttribute.Attribute6 = '1106' OR
                                                                           DMSAimTMKTCustomerGroupAttribute.Attribute6 = '0') AND
                                                                          (DMSAimTMKTCustomerGroupAttribute.Attribute7 = '1108' OR
                                                                           DMSAimTMKTCustomerGroupAttribute.Attribute7 = '0') AND
                                                                          (DMSAimTMKTCustomerGroupAttribute.Attribute8 = '1110' OR
                                                                           DMSAimTMKTCustomerGroupAttribute.Attribute8 = '0') AND
                                                                          (DMSAimTMKTCustomerGroupAttribute.Attribute9 = '' OR
                                                                           DMSAimTMKTCustomerGroupAttribute.Attribute9 = '0')
                                                               )
                                                    )
                                                    ) OR
                                                    (ApplicableTo = 'U' AND
                                                     ProgramCD IN (
                                                        SELECT p.ProgramCD
                                                          FROM DMSAimTMKTProgram p
                                                               JOIN
                                                               DMSAimTMKTProgramRegister r ON r.ProgramCD = p.ProgramCD
                                                         WHERE p.ApplicableTo = 'U' AND
                                                               r.CustomerCD = 'C000023306' AND
                                                               r.LocationCD = 'MAIN'
                                                    )
                                                    ) ) AND
                                                   (ApplyTo = 'A' OR
                                                    (ApplyTo = 'R' AND
                                                     ProgramCD IN (
                                                        SELECT ProgramCD
                                                          FROM DMSAimTMKTProgramApplyTo
                                                         WHERE DMSAimTMKTProgramApplyTo.UserName = 'SR000608' AND
                                                               DMSAimTMKTProgramApplyTo.ApplyToCD = '0'
                                                    )
                                                    ) OR
                                                    (ApplyTo = 'P' AND
                                                     ProgramCD IN (
                                                        SELECT ProgramCD
                                                          FROM DMSAimTMKTProgramApplyTo
                                                         WHERE DMSAimTMKTProgramApplyTo.UserName = 'SR000608' AND
                                                               DMSAimTMKTProgramApplyTo.ApplyToCD = '113'
                                                    )
                                                    ) OR
                                                    (ApplyTo = 'D' AND
                                                     ProgramCD IN (
                                                        SELECT ProgramCD
                                                          FROM DMSAimTMKTProgramApplyTo
                                                         WHERE DMSAimTMKTProgramApplyTo.UserName = 'SR000608' AND
                                                               DMSAimTMKTProgramApplyTo.ApplyToCD = '1269'
                                                    )
                                                    ) OR
                                                    (ApplyTo = 'T' AND
                                                     ProgramCD IN (
                                                        SELECT ProgramCD
                                                          FROM DMSAimTMKTProgramApplyTo
                                                         WHERE DMSAimTMKTProgramApplyTo.UserName = 'SR000608' AND
                                                               DMSAimTMKTProgramApplyTo.ApplyToCD = '373'
                                                    )
                                                    ) )
                                        )
                                        p
                                        LEFT JOIN
                                        DMSAimTMKTProgramRegister r ON p.ProgramCD = r.ProgramCD AND
                                                                       r.CustomerCD = 'C000023306' AND
                                                                       r.LocationCD = 'MAIN'
                                        LEFT JOIN
                                        DMSAimTMKTProgramRegistered rd ON p.ProgramCD = rd.ProgramCD AND
                                                                          rd.CustomerCD = 'C000023306' AND
                                                                          rd.LocationCD = 'MAIN'
                                        LEFT JOIN
                                        DMSAimTMKTReason ra ON r.ReasonID = ra.ReasonID
                                  WHERE (p.RegisterType = 'P' OR
                                         (p.RegisterType = 'U' AND
                                          r.IsRegister = 0) ) AND
                                        (r.ProgramCD IS NULL OR
                                         r.Status = 'U' OR
                                         r.IsRegister = 0 OR
                                         (r.IsRegister = 1 AND
                                          r.Status = 'R') ) AND
                                        DATE(p.StartRegisterDate) <= '2019-10-16' AND
                                        DATE(p.EndRegisterDate) >= '2019-10-16'
                             )
                  )
           UNION
           SELECT d.ProgramCD,
                  p.ApplicableTo,
                  d.BreakByLevelID,
                  d.BreakByLevelName,
                  r.RegisterAmt AS Revenue,
                  d.Quantity,
                  p.Descr
             FROM DMSAimTMKProgramBreakDownDetail AS d
                  INNER JOIN
                  DMSAimTMKTProgram AS p ON p.ProgramCD = d.ProgramCD
                  INNER JOIN
                  DMSAimTMKTProgramRegister AS r ON r.CustomerCD = 'C000023306' AND
                                                    r.LocationCD = 'MAIN' AND
                                                    r.LevelID = d.BreakByLevelID AND
                                                    r.ProgramCD = p.ProgramCD
            WHERE d.ProgramCD IN (
                      SELECT ProgramCD
                        FROM (
                                 SELECT DISTINCT p.*,
                                                 CASE WHEN rd.ProgramCD IS NULL THEN 0 ELSE 1 END IsRegister,
                                                 CASE WHEN rd.VisitID = 'SR000608C00002330616a9145220000' THEN 1 ELSE 0 END IsReRegisterInSameVisit,
                                                 CASE WHEN p.RegisterType = 'U' THEN 1 ELSE 0 END IsFromHO,
                                                 CASE WHEN r.Status = 'R' THEN 1 ELSE 0 END IsReject
                                   FROM (
                                            SELECT *
                                              FROM DMSAimTMKTProgram
                                             WHERE UserName = 'SR000608' AND
                                                   (ApplicableTo = 'A' OR
                                                    (ApplicableTo = 'L' AND
                                                     ProgramCD IN (
                                                        SELECT ProgramCD
                                                          FROM DMSAimTMKTProgramApplicableTo
                                                         WHERE DMSAimTMKTProgramApplicableTo.UserName = 'SR000608' AND
                                                               GroupCD IN (
                                                                   SELECT DMSAimTMKTCustomerGroupList.GroupCD
                                                                     FROM DMSAimTMKTCustomerGroupList
                                                                    WHERE DMSAimTMKTCustomerGroupList.UserName = 'SR000608' AND
                                                                          DMSAimTMKTCustomerGroupList.LocationCD = 'MAIN' AND
                                                                          DMSAimTMKTCustomerGroupList.CustomerCD = 'C000023306'
                                                               )
                                                    )
                                                    ) OR
                                                    (ApplicableTo = 'G' AND
                                                     ProgramCD IN (
                                                        SELECT ProgramCD
                                                          FROM DMSAimTMKTProgramApplicableTo
                                                         WHERE DMSAimTMKTProgramApplicableTo.UserName = 'SR000608' AND
                                                               GroupCD IN (
                                                                   SELECT DMSAimTMKTCustomerGroupAttribute.GroupCD
                                                                     FROM DMSAimTMKTCustomerGroupAttribute
                                                                    WHERE (DMSAimTMKTCustomerGroupAttribute.Attribute0 = '1075' OR
                                                                           DMSAimTMKTCustomerGroupAttribute.Attribute0 = '0') AND
                                                                          (DMSAimTMKTCustomerGroupAttribute.Attribute1 = '' OR
                                                                           DMSAimTMKTCustomerGroupAttribute.Attribute1 = '0') AND
                                                                          (DMSAimTMKTCustomerGroupAttribute.Attribute2 = '1085' OR
                                                                           DMSAimTMKTCustomerGroupAttribute.Attribute2 = '0') AND
                                                                          (DMSAimTMKTCustomerGroupAttribute.Attribute3 = '1098' OR
                                                                           DMSAimTMKTCustomerGroupAttribute.Attribute3 = '0') AND
                                                                          (DMSAimTMKTCustomerGroupAttribute.Attribute4 = '1094' OR
                                                                           DMSAimTMKTCustomerGroupAttribute.Attribute4 = '0') AND
                                                                          (DMSAimTMKTCustomerGroupAttribute.Attribute5 = '' OR
                                                                           DMSAimTMKTCustomerGroupAttribute.Attribute5 = '0') AND
                                                                          (DMSAimTMKTCustomerGroupAttribute.Attribute6 = '1106' OR
                                                                           DMSAimTMKTCustomerGroupAttribute.Attribute6 = '0') AND
                                                                          (DMSAimTMKTCustomerGroupAttribute.Attribute7 = '1108' OR
                                                                           DMSAimTMKTCustomerGroupAttribute.Attribute7 = '0') AND
                                                                          (DMSAimTMKTCustomerGroupAttribute.Attribute8 = '1110' OR
                                                                           DMSAimTMKTCustomerGroupAttribute.Attribute8 = '0') AND
                                                                          (DMSAimTMKTCustomerGroupAttribute.Attribute9 = '' OR
                                                                           DMSAimTMKTCustomerGroupAttribute.Attribute9 = '0')
                                                               )
                                                    )
                                                    ) OR
                                                    (ApplicableTo = 'U' AND
                                                     ProgramCD IN (
                                                        SELECT p.ProgramCD
                                                          FROM DMSAimTMKTProgram p
                                                               JOIN
                                                               DMSAimTMKTProgramRegister r ON r.ProgramCD = p.ProgramCD
                                                         WHERE p.ApplicableTo = 'U' AND
                                                               r.CustomerCD = 'C000023306' AND
                                                               r.LocationCD = 'MAIN'
                                                    )
                                                    ) ) AND
                                                   (ApplyTo = 'A' OR
                                                    (ApplyTo = 'R' AND
                                                     ProgramCD IN (
                                                        SELECT ProgramCD
                                                          FROM DMSAimTMKTProgramApplyTo
                                                         WHERE DMSAimTMKTProgramApplyTo.UserName = 'SR000608' AND
                                                               DMSAimTMKTProgramApplyTo.ApplyToCD = '0'
                                                    )
                                                    ) OR
                                                    (ApplyTo = 'P' AND
                                                     ProgramCD IN (
                                                        SELECT ProgramCD
                                                          FROM DMSAimTMKTProgramApplyTo
                                                         WHERE DMSAimTMKTProgramApplyTo.UserName = 'SR000608' AND
                                                               DMSAimTMKTProgramApplyTo.ApplyToCD = '113'
                                                    )
                                                    ) OR
                                                    (ApplyTo = 'D' AND
                                                     ProgramCD IN (
                                                        SELECT ProgramCD
                                                          FROM DMSAimTMKTProgramApplyTo
                                                         WHERE DMSAimTMKTProgramApplyTo.UserName = 'SR000608' AND
                                                               DMSAimTMKTProgramApplyTo.ApplyToCD = '1269'
                                                    )
                                                    ) OR
                                                    (ApplyTo = 'T' AND
                                                     ProgramCD IN (
                                                        SELECT ProgramCD
                                                          FROM DMSAimTMKTProgramApplyTo
                                                         WHERE DMSAimTMKTProgramApplyTo.UserName = 'SR000608' AND
                                                               DMSAimTMKTProgramApplyTo.ApplyToCD = '373'
                                                    )
                                                    ) )
                                        )
                                        p
                                        LEFT JOIN
                                        DMSAimTMKTProgramRegister r ON p.ProgramCD = r.ProgramCD AND
                                                                       r.CustomerCD = 'C000023306' AND
                                                                       r.LocationCD = 'MAIN'
                                        LEFT JOIN
                                        DMSAimTMKTProgramRegistered rd ON p.ProgramCD = rd.ProgramCD AND
                                                                          rd.CustomerCD = 'C000023306' AND
                                                                          rd.LocationCD = 'MAIN'
                                        LEFT JOIN
                                        DMSAimTMKTReason ra ON r.ReasonID = ra.ReasonID
                                  WHERE (p.RegisterType = 'P' OR
                                         (p.RegisterType = 'U' AND
                                          r.IsRegister = 0) ) AND
                                        (r.ProgramCD IS NULL OR
                                         r.Status = 'U' OR
                                         r.IsRegister = 0 OR
                                         (r.IsRegister = 1 AND
                                          r.Status = 'R') ) AND
                                        DATE(p.StartRegisterDate) <= '2019-10-16' AND
                                        DATE(p.EndRegisterDate) >= '2019-10-16'
                             )
                  )
       )
       t
 ORDER BY ProgramCD,
          Revenue DESC,
          Quantity DESC;