none
Group Consecutive Club Membership Dates by Member and Membership Type

    Question

  • I have a database table that currently holds membership joining information (extract below) with one line for each year/period that a 
    member have paid for membership; It is possible for a member to cancel their membership for one year/period and rejoin at a later date. 
    We need to represent each continuous membership as one line such that contents of Extract 1 below becomes Extract 2:

    Extract 1

    Member_No   GROUP Type Start_Date End_Date
    ---------   ------   --------- ---------- ----------
    10       MEM JUNIOR 2010-01-01 00:00:00.000     2011-01-01 00:00:00.000
    10       MEM JUNIOR 2011-01-01 00:00:00.000 2012-01-01 00:00:00.000
    10       MEM JUNIOR 2012-01-01 00:00:00.000     2013-01-01 00:00:00.000
    20       MEM JUNIOR 2005-01-01 00:00:00.000     2006-01-01 00:00:00.000
    20       MEM SENIOR 2006-01-01 00:00:00.000     2007-01-01 00:00:00.000
    20       MEM SENIOR 2007-01-01 00:00:00.000     2008-01-01 00:00:00.000
    20 MEM     SENIOR 2008-01-01 00:00:00.000     2009-01-01 00:00:00.000
    30   MEM     SENIOR 2005-01-01 00:00:00.000     2006-01-01 00:00:00.000
    30   MEM     SENIOR 2006-01-01 00:00:00.000     2007-01-01 00:00:00.000
    30   MEM     SENIOR 2007-01-01 00:00:00.000     2008-01-01 00:00:00.000
    30   MEM     SENIOR 2008-01-01 00:00:00.000     2009-01-01 00:00:00.000
    30   MEM     SENIOR 2009-01-01 00:00:00.000     2010-01-01 00:00:00.000
    30   MEM     SENIOR 2013-01-01 00:00:00.000     2014-01-01 00:00:00.000
    Extract 2

    Member_No   GROUP Type Start_Date               End_Date
    ---------   -------   -------- -------- --------
    10       MEM JUNIOR 2010-01-01 00:00:00.000     2013-01-01 00:00:00.000
    20       MEM JUNIOR 2005-01-01 00:00:00.000     2006-01-01 00:00:00.000
    20       MEM SENIOR 2006-01-01 00:00:00.000     2009-01-01 00:00:00.000
    30       MEM SENIOR 2005-01-01 00:00:00.000     2010-01-01 00:00:00.000
    30       MEM SENIOR 2013-10-13 00:00:00.000     2014-01-01 00:00:00.000

    I have the query below but this does quite work as I expect it to - any help would be greatly appreciated:

    ;with ms as (
          select Member_No, Type, valid_from, valid_to,
                 (	select 1 
    				from Membership ms2 
    				where ms2.Member_No = ms.Member_No 
    				and cast(ms2.valid_From as date) = cast(ms.valid_to as date)
    				and Type in ('mem')
                 ) as LinkedToNext
          from Membership ms where activity = 'mem'
         )
    select Member_No, Type, MIN(valid_from) as start_date, MAX(valid_to) as end_date
    from (select ms.*,
                 (select top 1 valid_to
                  from ms ms2 where ms2.Member_No = ms.Member_No and 
                  ms2.LinkedToNext is NULL and ms2.valid_from >= ms.valid_to
                 order by Type, valid_to desc
                 ) as grouping
          from ms
         ) ms1
    group by Member_No, Type, grouping
    order by Member_No, start_date


    • Edited by BabsOnline Wednesday, July 17, 2013 4:35 PM
    Wednesday, July 17, 2013 4:28 PM

Answers

  • Here is the working solution, you may need to change according to your table structure

    Also Avoid using GROUP, TYPE etc as column names because they are reserved keywords in SQL

    declare @tab table
    (	Member_No int,
    	memgroup varchar(3), 
    	memType varchar(10),
    	Start_Date datetime,
    	End_Date datetime)
    
    insert into @tab
    values
    (10,'MEM','JUNIOR','2010-01-01 00:00:00.000','2011-01-01 00:00:00.000'),
    (10,'MEM','JUNIOR','2011-01-01 00:00:00.000','2012-01-01 00:00:00.000'),
    (10,'MEM','JUNIOR','2012-01-01 00:00:00.000','2013-01-01 00:00:00.000'),
    (20,'MEM','JUNIOR','2005-01-01 00:00:00.000','2006-01-01 00:00:00.000'),
    (20,'MEM','SENIOR','2006-01-01 00:00:00.000','2007-01-01 00:00:00.000'),	
    (20,'MEM','SENIOR','2007-01-01 00:00:00.000','2008-01-01 00:00:00.000'),
    (20,'MEM','SENIOR','2008-01-01 00:00:00.000','2009-01-01 00:00:00.000');
    
    
    
    with cte as(select member_no,memgroup, memType ,min(start_date) 
    Over(partition by Member_No,memgroup,memtype) MIN_DATE,
    max(end_date) Over(partition by Member_No,memgroup,memtype) MAx_Date,
     row_number()OVer(partition by Member_No,memgroup,memtype Order by start_date,end_date) 
    RN from @tab)
    SELECT * FROM CTE WHERE RN=1


    Satheesh


    Thursday, July 18, 2013 2:49 AM

All replies

  • Can you try this?

    with cte as(select member_no,group,min(start_date)
    Over(partition by Member_No,GROUP,Type Order by start_date) MIN_DATE,
    max(start_date) Over(partition by Member_No,GROUP,Type Order by start_date) MAx_Date,
    row_number()OVer(partition by Member_No,GROUP,Type Order by start_date,end_date)
    RN from table)

    SELECT * FROM CTE WHERE RN=1



    Satheesh

    Wednesday, July 17, 2013 5:07 PM
  • Why did you post a picture instead of DDL? Why do you not know ISO-11179 standards for naming data elements? Consider this self-reference trick to prevent gaps in a timeline of memberships:

    CREATE TABLE Memberships
    (member_nbr CHAR(10) NOT NULL,
     membership_status CHAR(5) NOT NULL
      CHECK (membership_status IN ('mem', 'exp', …)),
     previous_membership_end_date DATE NOT NULL
     CONSTRAINT Chained_Dates
     REFERENCES Memberships (membership_end_date),
     membership_start_date DATE NOT NULL,
     membership_end_date DATE UNIQUE, -- null is current 
    PRIMARY KEY (member_nbr, membership_start_date),
    CONSTRAINT Membership_Order_Valid
    CHECK (membership_start_date <= membership_end_date),
    CONSTRAINT Chained_Dates 
    CHECK (DATEADD(DAY, 1, previous_membership_end_date) = membership_start_date).
    << other stuff for this membership >>
    );

    -- disable the Chained_Dates constraint
    ALTER TABLE memberships NOCHECK CONSTRAINT Chained_Dates

    -- insert a starter row
    INSERT INTO memberships(member_nbr, previous_membership_end_date, membership_start_date, membership_end_date)
    VALUES ('Foo Fest', '2010-01-01', '2010-01-02', '2010-01-05');

    -- enable the constraint in the table
    ALTER TABLE memberships CHECK CONSTRAINT Chained_Dates

    -- this works
    INSERT INTO memberships(member_nbr, previous_membership_end_date, membership_start_date, membership_end_date)
    VALUES ('Glob Week', '2010-01-05', '2010-01-06', '2010-01-10');

    -- this fails
    INSERT INTO memberships(member_nbr, previous_membership_end_date, membership_start_date, membership_end_date)
    VALUES ('Snoob', '2010-01-09', '2010-01-11', '2010-01-15'); 

    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Wednesday, July 17, 2013 5:10 PM
  • Sorry I am new to this world and did not know how to format the extracts I posted. Thanks for responding but idea is not to prevent gaps in membership as we cannot stand in the way of members leaving one year and returning in another year
    Wednesday, July 17, 2013 10:14 PM
  • Hey Satheesh, I couldnt get this to run successfull and I get the folowing error:

    Msg 102, Level 15, State 1, Line 2
    Incorrect syntax near 'order'.

    Can you please help?

    Wednesday, July 17, 2013 10:40 PM
  • Here is the working solution, you may need to change according to your table structure

    Also Avoid using GROUP, TYPE etc as column names because they are reserved keywords in SQL

    declare @tab table
    (	Member_No int,
    	memgroup varchar(3), 
    	memType varchar(10),
    	Start_Date datetime,
    	End_Date datetime)
    
    insert into @tab
    values
    (10,'MEM','JUNIOR','2010-01-01 00:00:00.000','2011-01-01 00:00:00.000'),
    (10,'MEM','JUNIOR','2011-01-01 00:00:00.000','2012-01-01 00:00:00.000'),
    (10,'MEM','JUNIOR','2012-01-01 00:00:00.000','2013-01-01 00:00:00.000'),
    (20,'MEM','JUNIOR','2005-01-01 00:00:00.000','2006-01-01 00:00:00.000'),
    (20,'MEM','SENIOR','2006-01-01 00:00:00.000','2007-01-01 00:00:00.000'),	
    (20,'MEM','SENIOR','2007-01-01 00:00:00.000','2008-01-01 00:00:00.000'),
    (20,'MEM','SENIOR','2008-01-01 00:00:00.000','2009-01-01 00:00:00.000');
    
    
    
    with cte as(select member_no,memgroup, memType ,min(start_date) 
    Over(partition by Member_No,memgroup,memtype) MIN_DATE,
    max(end_date) Over(partition by Member_No,memgroup,memtype) MAx_Date,
     row_number()OVer(partition by Member_No,memgroup,memtype Order by start_date,end_date) 
    RN from @tab)
    SELECT * FROM CTE WHERE RN=1


    Satheesh


    Thursday, July 18, 2013 2:49 AM
  • Thanks for revised solution Satheesh. I have just tested this but it does not identify gaps i.e. if we had

    (10,'MEM','JUNIOR','2010-01-01 00:00:00.000','2011-01-01 00:00:00.000'),
    (10,'MEM','JUNIOR','2011-01-01 00:00:00.000','2012-01-01 00:00:00.000'),
    (10,'MEM','JUNIOR','2012-01-01 00:00:00.000','2013-01-01 00:00:00.000'),
    (20,'MEM','JUNIOR','2005-01-01 00:00:00.000','2006-01-01 00:00:00.000'),
    (20,'MEM','SENIOR','2006-01-01 00:00:00.000','2007-01-01 00:00:00.000'),	
    (20,'MEM','SENIOR','2007-01-01 00:00:00.000','2008-01-01 00:00:00.000'),
    (20,'MEM','SENIOR','2008-01-01 00:00:00.000','2009-01-01 00:00:00.000'),
    (20,'MEM', 'SENIOR','2012-01-01 00:00:00.000','2013-01-01 00:00:00.000'); --Please notice this line as it shows that this individual was not a member between 2009 and 2012

    Then I will need the output to be as follows:

    10 MEM JUNIOR 2010-01-01 00:00:00.000 2013-01-01 00:00:00.000 1
    20 MEM JUNIOR 2005-01-01 00:00:00.000 2006-01-01 00:00:00.000 1
    20 MEM SENIOR 2006-01-01 00:00:00.000 2009-01-01 00:00:00.000 1
    20 MEM SENIOR 2012-01-01 00:00:00.000 2013-01-01 00:00:00.00 -- I need the result to show where there has been a break in membership


    • Edited by BabsOnline Friday, July 19, 2013 10:11 AM updated comment
    Friday, July 19, 2013 10:09 AM
  • what is the sql version you using?

    Satheesh

    Friday, July 19, 2013 10:17 AM
  • Microsoft SQL Server 2008 R2 (SP2) - 10.50.4000.0 (X64)   Jun 28 2012 08:36:30   Copyright (c) Microsoft Corporation  Standard Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) 

    Friday, July 19, 2013 10:19 AM