Find member start date and end date

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

Answer 1

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

Answer 2

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/

Answer 3

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.