Take the 2-minute tour ×

I have the following table in my database


  • ComapanyId
  • IsMember
  • JoinedDate
  • ExpiryDate
  • RejoinDate
  • LeavingDate

I am using the following query to return all the members as for 2011-01-31

select * from MEMBERSHIP 
where (JoinedDate < '2011-01-31' or RejoinDate  < '2011-01-31') and IsMember=1

My problem is it doesnt tally with the figures my manager has. Am I doing something wrong?

share|improve this question
An you explain the relationship between joineddate, expirydate, rejoindate and leavingdate. And also what ismember means? –  MatBailie May 25 '12 at 14:47
@Dems member or not –  Joshua May 25 '12 at 14:49
You're being too vague. For ismember does it mean that they're a member today and so means nothing for a historical date? You need to be complete, and precise, about all those fields so that we can understand all of the use cases. –  MatBailie May 25 '12 at 14:51
@dems Member as of today. Got what I was doing wrong in the following comments. Thanks –  Joshua May 25 '12 at 15:01
add comment

4 Answers

up vote 1 down vote accepted

The problem is that IsMember is currently whether they are a member, not whether they were a member on 2011-01-31. So, your query is really answering the question: "Which members who were active today joined or last rejoined before 2011-01-31?"

For instance, someone who starts on 2011-01-30 and quits on 2011-02-01 would not count as a member in your query, but would be a member on that date.

The most accurate answer is to look in a transaction table for memberships, where you have joins and quits in the table.

Barring that, the following probably gets close enough:

select *
where '2011-01-31' between JoinedDate and LeaveDate
share|improve this answer
add comment

Shouldn't you remove the "IsMember = 1" statement as you want to list users that were members before 2011-01-31 but are not members anymore? And maybe you should use '<=' if you want to include the 31th.

Also what is the default value of RejoinDate? If it is null it could have an influence on your WHERE statement...

share|improve this answer
add comment

The problem is that your date is implicitly treated as midnight of 2011-01-31 ('2011-01-31 00:00:00'), so any activity on that day is not included. Try using < '2011-02-01' instead, so that all of the activity on the 31st is included.

share|improve this answer
That's just one problem. I'd presume IsMember is a cached value for whether they're a member today - So filtering by that for a historic date is a problem. And just joining before a specific date is no indicator of whether they left before that date. And without knowing what all the fields mean, there may be more... –  MatBailie May 25 '12 at 14:49
And the original query is using "<" not "<=", so that seems to be what is intended. –  Gordon Linoff May 25 '12 at 14:53
add comment

You clould use something like this:

JoinedDate <= CONVERT(Date, '2011-01-31')
share|improve this answer
The OP isn't experiencing a syntax error, just different results. This won't help at all. –  MatBailie May 25 '12 at 14:52
add comment

Your Answer


