Q:

SQL Query - Determine A Supporters Status

I have been tasked with cleaning up data of an organisations supporters. Whilst I am okay with some SQL query coding I have run into a problem with something a bit more complex (well for me!).
The task is to mark supporters as active or lapsed. 
I can do this by looking at their start date and if under two years from that date we can mark as active and anything over as closed. sadly nothing is ever that easy and a supporter may climb up an additional level depending on the way they interact with the organisation. 
What I need the query to do is look to see if they have an additional supporter code. For example they cannot be active on two levels and I need the query to look to see if they are at the next level and if so lapse the previous code regardless of whether they meet the rule explained above. They can rise up to 5 levels. 
I hope this makes sense. The data looks something like this.
Member ID | Category | Start Date | End Date
123456    | LEG1     | 01/12/2012 |
654321    | LEG1     | 01/01/2009 |   
421124    | LEG1     | 01/05/2011 |   
421124    | LEG2     | 02/04/2011 | 31/12/2012
421124    | LEG3     | 02/04/2011 | 31/12/2012
421124    | LEG4     | 31/12/2012 |   
567888    | LEG2     | 14/03/2012 |   
567888    | LEG3     | 22/08/2012 |   
119119    | LEG2     | 01/01/2011 | 31/12/2012

Any advice gratefully received.

A:

Try it like this:
DECLARE @t TABLE([Member ID] INT, Category VARCHAR(100), [Start Date] DATE, [End Date] DATE)
INSERT @t 
SELECT a,b,CONVERT(DATE, c, 104) c, CONVERT(DATE, d, 104)
FROM (VALUES
(123456 ,'LEG1', '01/12/2012', NULL),
(654321 ,'LEG1', '01/01/2009', NULL),
(421124 ,'LEG1', '01/05/2011', NULL),
(421124 ,'LEG2', '02/04/2011', '31/12/2012'),
(421124 ,'LEG3', '02/04/2011', '31/12/2012'),
(421124 ,'LEG4', '31/12/2012', NULL),
(567888 ,'LEG2', '14/03/2012', NULL),
(567888 ,'LEG3', '22/08/2012', NULL),
(119119 ,'LEG2', '01/01/2011', '31/12/2012')
-- I added these rows to sample data to show how borderline cases are treated
(100000 ,'LEG1', '17/02/2011', NULL),
(100001 ,'LEG1', '18/02/2011', NULL),
(100002 ,'LEG1', '19/02/2011', NULL)
) tbl(a,b,c,d)

SELECT  *
        , CASE WHEN 
            ROW_NUMBER() OVER (PARTITION BY [Member ID] ORDER BY Category DESC) > 1
            OR 
            [Start Date] < DATEADD(YEAR, -2, CAST(GETDATE() AS DATE))
          THEN 'lapsed' 
          ELSE 'active' 
          END [Status]
FROM    @t
ORDER BY
        [Member ID]

It checks for both of your conditions, ROW_NUMBER() function makes sure that rows that are not highest achieved category cannot be marked as active and DATETIME comparison between [Start Date] and today's date minus 2 years takes care of your second condition. If any of these conditions is met, record won't be marked as active.

