How can I determine the nett effect of all joining/leaving records in each group?

Question

Slightly simplified, I have a table showing status records for members of an organisation. They join by becoming 'Active' and can leave by becoming 'Resigned', 'Lapsed' or 'Deceased'. Having one or more sequential leaving records only counts as having left only once, at the date of the earliest leaving record. Similarly with joining records if that were ever to occur.

Each change in status is recorded as one record using the member_id and the data of the change. A single member may have many status change records.

I need to be able to group by each member and determine if the overall result of all the records for a member in a given year, say 2015, results in a nett gain or loss for the organisation, hence making them a joiner or a leaver (or nothing if the effects cancel). The result I want is (a) how many joiners and who they are and (b) how many leavers and who they are.

Can anyone help me with the correct coding techniques to process all the records of a member in sequential, chronological order in order to determine the nett result for that member?

An example schema and data is below - see also SQLfiddle http://sqlfiddle.com/#!9/69feb5/1

CREATE TABLE memstatus (
  member_id INT,
  date_assigned DATE,
  status_type VARCHAR(10));


INSERT INTO memstatus (member_id, date_assigned, status_type)
VALUES
(178,   '1948-01-01',   'Active'),
(178,   '2015-02-12',   'Deceased'),

(190,   '2013-12-09',   'Active'),
(190,   '2014-03-23',   'Resigned'),
(190,   '2015-12-09',   'Active'),

(194,   '2013-01-01',   'Active'),
(194,   '2015-03-15',   'Lapsed'),
(194,   '2015-08-20',   'Active'),

(935,   '2015-04-01',   'Active'),

(214,   '1966-01-01',   'Active'),
(214,   '2015-01-09',   'Lapsed'),
(214,   '2015-08-10',   'Deceased')

(300,   '2015-01-09',   'Active'),
(300,   '2015-07-10',   'Resigned')

The results from this data should be

Joiners in 2015

id 190 {left before 2015 but re-joined in 2015}

id 935 {new member in 2015}

Leavers in 2015

id 178 {one leaving record in 2015}

id 214 {two sequential leaving records in 2015 - but must count as one}

neither leaver nor joiner

id 194 {left but re-joined in the same year}

id 300 {joined and left in the same year}

I tried the following for leavers (see SQLfiddle http://sqlfiddle.com/#!9/69feb5/1) but this only works if the last record is a leaving record so gives me member 300 as well. I had the same problem with joiners.

SELECT member_id, status_type, date_assigned
FROM memstatus
WHERE date_assigned = (SELECT MAX(date_assigned) 
                                    FROM memstatus AS T1 
                                    WHERE T1.member_id = memstatus.member_id 
                                    AND   T1.date_assigned BETWEEN CONCAT('2015','-01-01') AND CONCAT('2015','-12-31')
                                    )

 AND  status_type NOT LIKE 'Active'

Answer 1

SELECT member_id, assigned_year, CASE WHEN finalStatus <0 THEN 'Leaver' WHEN finalStatus >0 THEN 'Joiner' WHEN finalStatus =0 THEN 'Neither' END FROM (Select member_id, Year(date_assigned) AS assigned_year, SUM(CASE WHEN status_type_id = 'Active' THEN 1 WHEN status_type = 'Resigned' THEN -1 WHEN status_type = 'Lapsed' THEN -1 WHEN status_type = 'Deceased' THEN -1 END) AS finalStatus FROM memstatus GROUP BY member_id, Year(date_assigned) ) As a ORDER BY member_id