locked
Find member start date and end date RRS feed

  • Question

  • Id like to find a members start date and end date. The issue im having is that we can have a member from jan-march, cancel april-june and come back from july to present. I would like a start date and end date for each of these ranges. 

    Example data set

    declare @tmpTblEffDate table (rownum int, memnum bigint,receff datetime, effdate datetime, termdate datetime)
    Insert into @tmpTblEffDate values (1,79005424401,'2008-12-01 00:00:00.000',	'2008-04-01 00:00:00.000', NULL)
    Insert into @tmpTblEffDate values (2,79005424401,'2009-01-01 00:00:00.000',	'2008-04-01 00:00:00.000', '2009-01-01 00:00:00.000')
    Insert into @tmpTblEffDate values (1,79005424401,'2009-02-01 00:00:00.000',	'2008-04-01 00:00:00.000', '2009-01-01 00:00:00.000')
    Insert into @tmpTblEffDate values (2,79005424401,'2009-03-01 00:00:00.000',	'2008-04-01 00:00:00.000', NULL)
    Insert into @tmpTblEffDate values (2,79005424401,'2009-04-01 00:00:00.000',	'2008-04-01 00:00:00.000', NULL)

    You will notice that this customer became effective 2008-04-01 but terminated in 2009-01-01 for the month of january and february. Then cam back in march which is represented by the null value in the termdate field.

    I would expect the out come to be as follows:

    Memnum         StartDate                         EndDate

    79005424401, 2008-04-01 00:00:00.000, 2009-01-01 00:00:00.000

    79005424401, 2009-03-01 00:00:00.000, 2013-10-01 00:00:00.000

    I was using the sql below but realized that in a case like this where a customer may leave and come back it does not work. 

    SELECT Row_number() 
                     OVER( 
                       partition BY memnum 
                       ORDER BY memnum, Min(receff) ASC) AS RowNum, 
                   memnum, 
                   Min(receff)                           AS 'MinRecEff', 
                   Max(receff)                           AS 'MaxRecEff', 
                   effdate, 
                   termdate, 
                   CASE 
                     WHEN termdate IS NULL THEN effdate  
                     ELSE Min(receff)
                   END                                   AS StartDate, 
                   CASE 
                     WHEN termdate IS NULL THEN max(receff) 
                     ELSE termdate 
                   END                                   AS EndDate 
            FROM   @tmpTblEffDate
            GROUP  BY memnum, 
                      effdate, 
                      termdate

    Thursday, October 3, 2013 2:58 PM

Answers

  • >> I;d like to find a members start date and end date. The issue im having is that we can have a member from jan-march, cancel april-june and come back from july to present. I would like a start date and end date for each of these ranges. <<

    This attempt at DDL violates all kinds of rules. No keys, so this is not a table at all. We do not record a physical row number in a logical model. We never use BIGINT for an identifier (are you doing math on it?); it is a tag number. We have a DATE data type. Putting “tbl-” in a table name is so bad it has a name in data modeling; “tibbling” and we laugh at it. You do not know how to write current SQL INSERT statements. You do not know the difference between fields and columns. The use of single quotes on aliases is T-SQL dialect; we use double quotes in SQL.

    Wher is the status of the member? The Information Principle says that it has to be modeled as a scalar value in a column of a table 

    Most important is that you have no idea how time works in the ISO temporal model. Here is a guess at a correction, with correct data types, constraints and keys. 

    CREATE TABLE Membership
    (member_nbr CHAR(11) NOT NULL, 
     rec_eff_date DATE NOT NULL, 
     PRIMARY KEY (member_nbr, rec_eff_date),
     eff_date DATE NOT NULL, 
     CHECK (rec_eff_date <= eff_date),
     term_date DATE,
     CHECK (eff_date < term_date));

     ('79005424401', '2008-12-01', '2008-04-01', NULL),
     ('79005424401', '2009-01-01', '2008-04-01', '2009-01-01'),
     ('79005424401', '2009-02-01', '2008-04-01', '2009-01-01'),
     ('79005424401', '2009-03-01', '2008-04-01', NULL),
     ('79005424401', '2009-04-01', '2008-04-01', NULL);

    Now throw it out. It is a bad data model as well as weak SQL coding. To track the history of, say, Memberships we need to see time as a continuum and model it as (begin_date, end_date) pairs that define when an entity had a particular status. Here is the skeleton. 

    CREATE TABLE Foobar_History 
    (member_id CHAR(11) NOT NULL, 
     start_date DATE NOT NULL, 
     end_date DATETIME, --null means current status
     CHECK (start_date <= end_date),
     member_status CHAR(6) DEFAULT 'active' NOT NULL
        CHECK (member_status IN ('active', 'cancel')), 
     PRIMARY KEY (member_id, start_date)); 

    When the end_date is NULL, that state of being is still current. You use a simple query for the status on any particular date;

    SELECT * 
      FROM Foobar
     WHERE @in_cal_date
         BETWEEN start_date
          AND COALESCE (end_date, CURRENT_TIMESTAMP);

    There are more tricks in the DDL to prevent gaps, etc

    CREATE TABLE Mmebership_History
    (member_id CHAR(11) NOT NULL,
     previous_member_end_date DATE NOT NULL  
     CONSTRAINT Chained_Dates  
      REFERENCES members (member_end_date), 
     member_start_date DATE NOT NULL, 
     member_end_date DATE UNIQUE, -- null means member in progress
      PRIMARY KEY (member_id, member_start_date), 
     CONSTRAINT member_Order_Valid 
      CHECK (member_start_date <= member_end_date), 
     CONSTRAINT Chained_Dates 
      CHECK (DATEADD(DAY, 1, previous_member_end_date) = member_start_date),
      member_status CHAR(6) DEFAULT 'active' NOT NULL
        CHECK (member_status IN ('active', 'cancel')), 
    );

    -- disable the Chained_Dates constraint
    ALTER TABLE members NOCHECK CONSTRAINT Chained_Dates;
    GO

    -- insert a starter row
    INSERT INTO members(member_id, previous_member_end_date, member_start_date, member_end_date)
    VALUES ('Foo Fest', '2010-01-01', '2010-01-02', '2010-01-05', 'active');
    GO

    -- enable the constraint in the table
    ALTER TABLE members CHECK CONSTRAINT Chained_Dates;
    GO

    -- this works
    INSERT INTO members(member_id, previous_member_end_date, member_start_date, member_end_date)
    VALUES ('79005424401', '2010-01-05', '2010-01-06', '2010-01-10', 'cancel');

    -- this fails
    INSERT INTO members(member_id, previous_member_end_date, member_start_date, member_end_date)
    VALUES ('79005424401', '2010-01-09', '2010-01-11', '2010-01-15', 'cancel'); 

    --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

    • Marked as answer by TonyG86 Tuesday, October 8, 2013 2:34 PM
    Thursday, October 3, 2013 4:38 PM
  • Hi Tony,

    Try an do what Mr. Celko recommended, you will get cleaner database schema and simpler and cleaner SQL queries.

    If you can't do that here is a query that will give you the desired result:

    ;with cte
    as (
    	select
    		0 as Lvl
    		, tde.receff
    		, tde.memnum
    	from @tmpTblEffDate tde
    		left join @tmpTblEffDate tdeP on tde.memnum = tdeP.memnum
    				and tde.receff = dateadd(month, 1, tdeP.receff)
    				and tdeP.termdate is null
    	where tdeP.rownum is null
    		and tde.termdate is null
    	union all
    	select
    		tdeP.Lvl + 1
    		, tde.receff
    		, tde.memnum
    	from cte tdeP
    		inner join @tmpTblEffDate tde on tdeP.memnum = tde.memnum
    			and tdeP.receff = dateadd(month, -1, tde.receff)
    			and tde.termdate is null
    )
    select
    	t.memnum, min(t.receff) as min_receff, max(t.receff) as max_receff
    from (
    	select
    		c.memnum, c.receff
    		, row_number () over (partition by c.memnum order by c.receff) - c.Lvl as RNm
    	from cte c
    ) t
    group by t.memnum, t.RNm
    

    Unfortunately I could not avoid using recursion but there is just no attribute I could get a hold of and produce adequate groups for this query.


    HTH, Regards, Dean Savović, www.comminus.hr/en/

    • Proposed as answer by Allen Li - MSFT Tuesday, October 8, 2013 6:33 AM
    • Marked as answer by TonyG86 Tuesday, October 8, 2013 2:34 PM
    Thursday, October 3, 2013 9:31 PM

All replies

  • >> I;d like to find a members start date and end date. The issue im having is that we can have a member from jan-march, cancel april-june and come back from july to present. I would like a start date and end date for each of these ranges. <<

    This attempt at DDL violates all kinds of rules. No keys, so this is not a table at all. We do not record a physical row number in a logical model. We never use BIGINT for an identifier (are you doing math on it?); it is a tag number. We have a DATE data type. Putting “tbl-” in a table name is so bad it has a name in data modeling; “tibbling” and we laugh at it. You do not know how to write current SQL INSERT statements. You do not know the difference between fields and columns. The use of single quotes on aliases is T-SQL dialect; we use double quotes in SQL.

    Wher is the status of the member? The Information Principle says that it has to be modeled as a scalar value in a column of a table 

    Most important is that you have no idea how time works in the ISO temporal model. Here is a guess at a correction, with correct data types, constraints and keys. 

    CREATE TABLE Membership
    (member_nbr CHAR(11) NOT NULL, 
     rec_eff_date DATE NOT NULL, 
     PRIMARY KEY (member_nbr, rec_eff_date),
     eff_date DATE NOT NULL, 
     CHECK (rec_eff_date <= eff_date),
     term_date DATE,
     CHECK (eff_date < term_date));

     ('79005424401', '2008-12-01', '2008-04-01', NULL),
     ('79005424401', '2009-01-01', '2008-04-01', '2009-01-01'),
     ('79005424401', '2009-02-01', '2008-04-01', '2009-01-01'),
     ('79005424401', '2009-03-01', '2008-04-01', NULL),
     ('79005424401', '2009-04-01', '2008-04-01', NULL);

    Now throw it out. It is a bad data model as well as weak SQL coding. To track the history of, say, Memberships we need to see time as a continuum and model it as (begin_date, end_date) pairs that define when an entity had a particular status. Here is the skeleton. 

    CREATE TABLE Foobar_History 
    (member_id CHAR(11) NOT NULL, 
     start_date DATE NOT NULL, 
     end_date DATETIME, --null means current status
     CHECK (start_date <= end_date),
     member_status CHAR(6) DEFAULT 'active' NOT NULL
        CHECK (member_status IN ('active', 'cancel')), 
     PRIMARY KEY (member_id, start_date)); 

    When the end_date is NULL, that state of being is still current. You use a simple query for the status on any particular date;

    SELECT * 
      FROM Foobar
     WHERE @in_cal_date
         BETWEEN start_date
          AND COALESCE (end_date, CURRENT_TIMESTAMP);

    There are more tricks in the DDL to prevent gaps, etc

    CREATE TABLE Mmebership_History
    (member_id CHAR(11) NOT NULL,
     previous_member_end_date DATE NOT NULL  
     CONSTRAINT Chained_Dates  
      REFERENCES members (member_end_date), 
     member_start_date DATE NOT NULL, 
     member_end_date DATE UNIQUE, -- null means member in progress
      PRIMARY KEY (member_id, member_start_date), 
     CONSTRAINT member_Order_Valid 
      CHECK (member_start_date <= member_end_date), 
     CONSTRAINT Chained_Dates 
      CHECK (DATEADD(DAY, 1, previous_member_end_date) = member_start_date),
      member_status CHAR(6) DEFAULT 'active' NOT NULL
        CHECK (member_status IN ('active', 'cancel')), 
    );

    -- disable the Chained_Dates constraint
    ALTER TABLE members NOCHECK CONSTRAINT Chained_Dates;
    GO

    -- insert a starter row
    INSERT INTO members(member_id, previous_member_end_date, member_start_date, member_end_date)
    VALUES ('Foo Fest', '2010-01-01', '2010-01-02', '2010-01-05', 'active');
    GO

    -- enable the constraint in the table
    ALTER TABLE members CHECK CONSTRAINT Chained_Dates;
    GO

    -- this works
    INSERT INTO members(member_id, previous_member_end_date, member_start_date, member_end_date)
    VALUES ('79005424401', '2010-01-05', '2010-01-06', '2010-01-10', 'cancel');

    -- this fails
    INSERT INTO members(member_id, previous_member_end_date, member_start_date, member_end_date)
    VALUES ('79005424401', '2010-01-09', '2010-01-11', '2010-01-15', 'cancel'); 

    --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

    • Marked as answer by TonyG86 Tuesday, October 8, 2013 2:34 PM
    Thursday, October 3, 2013 4:38 PM
  • Hi Tony,

    Try an do what Mr. Celko recommended, you will get cleaner database schema and simpler and cleaner SQL queries.

    If you can't do that here is a query that will give you the desired result:

    ;with cte
    as (
    	select
    		0 as Lvl
    		, tde.receff
    		, tde.memnum
    	from @tmpTblEffDate tde
    		left join @tmpTblEffDate tdeP on tde.memnum = tdeP.memnum
    				and tde.receff = dateadd(month, 1, tdeP.receff)
    				and tdeP.termdate is null
    	where tdeP.rownum is null
    		and tde.termdate is null
    	union all
    	select
    		tdeP.Lvl + 1
    		, tde.receff
    		, tde.memnum
    	from cte tdeP
    		inner join @tmpTblEffDate tde on tdeP.memnum = tde.memnum
    			and tdeP.receff = dateadd(month, -1, tde.receff)
    			and tde.termdate is null
    )
    select
    	t.memnum, min(t.receff) as min_receff, max(t.receff) as max_receff
    from (
    	select
    		c.memnum, c.receff
    		, row_number () over (partition by c.memnum order by c.receff) - c.Lvl as RNm
    	from cte c
    ) t
    group by t.memnum, t.RNm
    

    Unfortunately I could not avoid using recursion but there is just no attribute I could get a hold of and produce adequate groups for this query.


    HTH, Regards, Dean Savović, www.comminus.hr/en/

    • Proposed as answer by Allen Li - MSFT Tuesday, October 8, 2013 6:33 AM
    • Marked as answer by TonyG86 Tuesday, October 8, 2013 2:34 PM
    Thursday, October 3, 2013 9:31 PM
  • Thank you ill look into this. Unfortunately i have no control over the schema and have to work with what i have. Also, the example i gave is a close enough representation to get the desired outcome but by no means is a reflection of any internal naming conventions or structure. That being said it needs to be changed and ive made my recommendations. Ultimately its not up to me. I appreciate the help and will vote once i have a chance to try these out.  
    Thursday, October 3, 2013 9:49 PM