Check if a Date is Within a Range of Dates

Yeah, I couldn't think of a better subject line.

Basically, I have a membership table that lists each member with an effective period, Eff_Period, that indicates a month when a member was active. So, if a member is active from Jan to Mar, there will be three rows with Eff_Periods of 201501, 201502 and 201503.

All well and good.

But, a member may not necessarily have continuous months for active membership. They might have only been active for Jan, Feb and Jun. That would still give them three rows, but with noncontinuous Eff_Periods; they'd be 201501, 201502 and 201506.

There is also a table that logs member activity. It has an Activity_Date that holds the date of the activity - betcha didn't see that comin'. What I'm trying to do is determine if an activity took place during a period when the member was active.

My original thought was to count how many rows a member has in the Membership table and compare that number to the number of months between the MIN(Eff_Period) and the MAX(Eff_Period). If the numbers didn't matchup, then I knew that the member had a disconnect somewhere; he became inactive, then active again. But, then I thought of the scenario I detailed above and realized that the counts could match, but still have a discontinuity.

So, is there a nifty little SQL shortcut that could determine if a target month is contained within a continuous or discontinuous list of months?

Thanx in advance!

August 20th, 2015 1:05pm

The simplest way will be to have a calendar table.

Select cal.theDate, MA.* from Calendar cal

inner join Membership M

ON convert(varchar(6), cal.theDate, 112) = M.Eff_Period

inner join MemberActivity MA ON Cal.theDate = MA.Activity_Date -- this will select only activity that happened when Member was

Free Windows Admin Tool Kit Click here and download it now
August 20th, 2015 1:16pm

Please post your sample  Table DDL and some insert statements for sample data so that you can get your queries answered asap.
August 20th, 2015 1:18pm

The simplest way will be to have a calendar table.

Select cal.theDate, MA.* from Calendar cal

inner join Membership M

ON convert(varchar(6), cal.theDate, 112) = M.Eff_Period

inner join MemberActivity MA ON Cal.theDate = MA.Activity_Date -- this will select only activity that happened when Member was

Free Windows Admin Tool Kit Click here and download it now
August 20th, 2015 1:47pm

DECLARE @MemberShip TABLE (
	MemberShipID INT,
	Eff_Period VARCHAR(10)
)
INSERT INTO @MemberShip VALUES
(1, '201501'), (1, '201502'), (1, '201503'), 
(2, '201501'), (2, '201502'), (2, '201506')	

--SELECT * FROM @MemberShip

DECLARE @MemberActivityLog TABLE (
	MemberShipID INT,
	Activity_Date DATETIME
)
INSERT INTO @MemberActivityLog VALUES
(1, '2015-01-10'), (1, '2015-02-10'), (1, '2015-03-10'), 
(2, '2015-01-10'), (2, '2015-02-10'), (2, '2015-03-10')	

--SELECT * FROM @MemberActivityLog

;WITH CTE AS (
	SELECT MemberShipID, CAST(YEAR(Activity_Date) AS VARCHAR(4)) + RIGHT('0' + CAST(MONTH(Activity_Date) AS VARCHAR(2)), 2) AS Activity_YearMonth
	FROM @MemberActivityLog
)

SELECT c.* 
FROM CTE AS c
WHERE c.Activity_YearMonth NOT IN (SELECT Eff_Period FROM @MemberShip WHERE MemberShipID = c.MemberShipID)

August 20th, 2015 2:36pm

The Calendar table concept should work for any SQL Server version.

You may check this article

http://www.experts-exchange.com/articles/12267/Build-your-own-SQL-calendar-table-to-perform-complex-date-expressions.html

That's the first one that comes up in the search, you'll find many more including my own article that talks about them in brief as well

Passing multiple ranges to stored procedure

Free Windows Admin Tool Kit Click here and download it now
August 20th, 2015 3:27pm

A useful idiom is a report period calendar. It gives a name to a range of dates. The worst way would be to use temporal math; it tells the world you do not think in sets or understand declarative programming yet. Here is a skeleton:  

CREATE TABLE Month_Periods
(report_name CHAR(10) NOT NULL PRIMARY KEY,
report_start_date DATE NOT NULL,
report_end_date DATE NOT NULL,
CONSTRAINT date_ordering
CHECK (report_start_date <= report_end_date),
ordinal_period INTEGER NOT NULL UNIQUE 
  CHECK(ordinal_period > 0)
etc);

I like the MySQL convention of using double zeroes for months and years, That is 'yyyy-mm-00' for a month within a year and 'yyyy-00-00' for the whole year. The advantage is that it will sort with the ISO-8601 data format required by Standard SQL. 

Load 100 years, then the difference in the ordinal period numbers is  the elapsed months. The predicate is a simple BETWEEN ()


August 20th, 2015 8:08pm

i have assumed that your eff_Period is varchar().

Declare @membership as table(id int ,eff_Period varchar(6))
Declare @Activity as table(id int ,Activity_date date)

Insert into @membership values(1,'201501'),(1,'201502'),(1,'201506')
insert into @Activity values(1,'20150115'),(1,'20150215'),(1,'20150315'),(1,'20150615')

select *,case when a.id is null then 'Not a Member' else 'Active member' end
 from @membership a full join
@Activity b on a.id=b.id
and left(convert(varchar(10),b.Activity_date,112),6)=a.eff_Period

you can filter out the this query if you want all the activities happened only during Active membership.

I hope this helps.

Free Windows Admin Tool Kit Click here and download it now
August 20th, 2015 8:29pm

So there could be activity in a month when not an active member and that is what you're trying to find? Just sounds so odd, looking for periods of activity when they weren't active... (although I am imagining a scenario of casual users)

Doing a left outer with activity on the left side and then searching for nulls would give these.

To find breaks then a left outer join to a date calendar would help (you'd have to distinct the activity since there could be more than one in a month - I assume) or a "gaps and islands" approach may do

August 20th, 2015 8:32pm

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics