SELECT 
  p.promotion_id, 
  p.from_date, 
  p.to_date, 
  active.from_date AS active_from_date, 
  active.to_date AS active_to_date, 
  expired.from_date AS expired_from_date, 
  expired.to_date AS expired_to_date 
FROM 
  cscart_promotions AS p 
  INNER JOIN cscart_ab__dotd AS ab__dotd ON p.promotion_id = ab__dotd.promotion_id 
  AND ab__dotd.use_schedule = 'Y' 
  LEFT JOIN (
    SELECT 
      promotion_id, 
      MIN(from_date) AS from_date, 
      MIN(to_date) AS to_date 
    FROM 
      cscart_ab__dotd_periods 
    WHERE 
      to_date >= 1741888641 
    GROUP BY 
      promotion_id
  ) AS active ON active.promotion_id = p.promotion_id 
  LEFT JOIN (
    SELECT 
      promotion_id, 
      MAX(from_date) AS from_date, 
      MAX(to_date) AS to_date 
    FROM 
      cscart_ab__dotd_periods 
    WHERE 
      to_date < 1741888641 
    GROUP BY 
      promotion_id
  ) AS expired ON expired.promotion_id = p.promotion_id 
WHERE 
  p.status IN ('A', 'H')