sql-fu.sql

INSERT INTO crm_membershipstatus (created, status, membership_id)
    SELECT p.end_date AS created, m.status AS status, p.membership_id AS membership_id
    FROM crm_membership m
      LEFT JOIN crm_membershipstatus s ON m.contact_id = s.membership_id
      LEFT JOIN (
        SELECT
          period1.membership_id, period1.end_date
        FROM
          crm_membershipperiod AS period1
        LEFT OUTER JOIN
          crm_membershipperiod AS period2
        ON
          period1.membership_id = period2.membership_id AND
          period1.end_date < period2.end_date
        WHERE
          period2.id IS NULL
        ORDER BY
          membership_id
        ) AS p ON m.contact_id = p.membership_id
    WHERE s.id IS NULL AND m.status <> 'NONE'
    ORDER BY member_number;