Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 NOT IN statement?

Author  Topic 

techsupport14
Starting Member

18 Posts

Posted - 2014-04-23 : 12:04:01
Hello,
I have never tried a NOT IN statement and it was suggested trying that to get certain set of records.

Here's the entire SQL Statement:

SELECT lookup.MemberTypes.Id AS ClubTypeID, lookup.MemberTypes.Description AS ClubType, entity.Organization.StatusId,
entity.Organization.MembershipNumber, attribute.OrgMembership.EndDate, attribute.OrgMembership.CreatedDate, YEAR(attribute.OrgMembership.EndDate) AS EndYear,
attribute.OrgMembership.Id
FROM entity.Organization INNER JOIN
attribute.OrgMembership ON entity.Organization.Id = attribute.OrgMembership.OrganizationId INNER JOIN
lookup.MemberTypes ON attribute.OrgMembership.MembershipTypeId = lookup.MemberTypes.Id
WHERE (NOT (entity.Organization.MembershipNumber IN (4178, 4179))) AND (lookup.MemberTypes.Id IN (15, 20, 21)) AND (entity.Organization.StatusId BETWEEN 3 AND 5) AND
(YEAR(attribute.OrgMembership.EndDate) = YEAR({ fn CURDATE() })) AND (NOT (attribute.OrgMembership.Id IN
(SELECT Id
FROM attribute.OrgMembership AS Memberships
WHERE (EndDate < YEAR(GETDATE())))))


The last part is the NOT IN statement and I don't think it's actually working as I need it.

NOTE: each membership record is for a membership duration of one year.
So the same membership record could have multiple records with different durations.

What I'm looking for:
I am trying to get all the members that are NOT CURRENT.
I originally tried to get all the records with <Year([EndDate]) HOWEVER this does not consider the records with current year EndDate and is not truly not current members.

Hence trying to do a NOT IN statement?

Can someone help me?
Thank you in advance!

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2014-04-23 : 12:54:23
>>I am trying to get all the members that are NOT CURRENT.

Please define (in words) how to determine if a member is current or not.


Be One with the Optimizer
TG
Go to Top of Page

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-04-23 : 13:05:26
Does your query work and return the correct row set?

FWIW, I reformatted your query so I could read it more easily:


SELECT types.Id AS ClubTypeID
, types.Description AS ClubType
, entOrg.StatusId
, entOrg.MembershipNumber
, attOrg.EndDate
, attOrg.CreatedDate
, YEAR(attOrg.EndDate) AS EndYear
, attOrg.Id

FROM entity.Organization entOrg
INNER JOIN attribute.OrgMembership attOrg
ON entOrg.Id = attOrg.OrganizationId
INNER JOIN lookup.MemberTypes types
ON attOrg.MembershipTypeId = types.Id

WHERE (NOT (entOrg.MembershipNumber IN (4178, 4179)))
AND (types.Id IN (15, 20, 21))
AND (entOrg.StatusId BETWEEN 3 AND 5)
AND (YEAR(attOrg.EndDate) = YEAR({ fn CURDATE() })) -- What is { fn CURDATE() } ??????
AND (attOrd.EndDate >= YEAR(GETDATE()))


Note that the last clause of the WHERE no longer has the NOT...IN construct. I think that it is logically equivalent to the original, but you'll need to test it with your own data.
Go to Top of Page

techsupport14
Starting Member

18 Posts

Posted - 2014-04-23 : 13:08:54
TG: Thank you.

Ok, I'll to explain another way.

NOT CURRENT member:
You became a member in 2012. EndDate year 2012
You renewed in 2013. EndDate year 2013
You didn't renew in 2014. EndDate year 2014
You are not current member because you did not renew in this year (current year).
There would be two records in the entity.OrgMembership table.


CURRENT Member:
I became a member in 2012. EndDate year 2012
You renewed in 2013. EndDate year 2013
You renewed in 2014. EndDate year 2014
You am a current member because I renewed this year (current year).
There would be three records in the entity.OrgMembership table.

I can't have you, the current member, show up as a NOT CURRENT member if I do a show all records with an EndDate < Year(GetDate()) because you have records in the table with EndDate less than this year.
Go to Top of Page

techsupport14
Starting Member

18 Posts

Posted - 2014-04-23 : 13:13:40
gbritton,
I'm confused?

It is producing the same number of records. 636 as what I originally posted?
Go to Top of Page

techsupport14
Starting Member

18 Posts

Posted - 2014-04-23 : 13:16:31
[code]
SELECT lookup.MemberTypes.Id AS ClubTypeID, lookup.MemberTypes.Description AS ClubType, entity.Organization.StatusId, entity.Organization.MembershipNumber,
attribute.OrgMembership.EndDate, attribute.OrgMembership.CreatedDate, YEAR(attribute.OrgMembership.EndDate) AS EndYear, attribute.OrgMembership.Id
FROM entity.Organization INNER JOIN
attribute.OrgMembership ON entity.Organization.Id = attribute.OrgMembership.OrganizationId INNER JOIN
lookup.MemberTypes ON attribute.OrgMembership.MembershipTypeId = lookup.MemberTypes.Id
WHERE (NOT (entity.Organization.MembershipNumber IN (4178, 4179))) AND (lookup.MemberTypes.Id IN (15, 20, 21)) AND (entity.Organization.StatusId BETWEEN 3 AND 5) AND
(YEAR(attribute.OrgMembership.EndDate) = YEAR({ fn CURDATE() }))
[/code]

This gives me all CURRENT members.
All member records with an EndDate of this year.

How do I get records that are not current? Records that do not have an EndDate of this year.
NOTING that I can't do this:
[code]
SELECT lookup.MemberTypes.Id AS ClubTypeID, lookup.MemberTypes.Description AS ClubType, entity.Organization.StatusId, entity.Organization.MembershipNumber,
attribute.OrgMembership.EndDate, attribute.OrgMembership.CreatedDate, YEAR(attribute.OrgMembership.EndDate) AS EndYear, attribute.OrgMembership.Id
FROM entity.Organization INNER JOIN
attribute.OrgMembership ON entity.Organization.Id = attribute.OrgMembership.OrganizationId INNER JOIN
lookup.MemberTypes ON attribute.OrgMembership.MembershipTypeId = lookup.MemberTypes.Id
WHERE (NOT (entity.Organization.MembershipNumber IN (4178, 4179))) AND (lookup.MemberTypes.Id IN (15, 20, 21)) AND (entity.Organization.StatusId BETWEEN 3 AND 5) AND
(YEAR(attribute.OrgMembership.EndDate) < YEAR({ fn CURDATE() }))
[/code]

because this will show members that are current (EndDate of this year) who happens to have prior year membership records.

I could do two separate views and do a RIGHT OUTER JOIN with <> on ID??
I thought doing a NOT IN in the one view will be simpler?
Go to Top of Page

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2014-04-23 : 13:27:05
In order to help your in a reasonable way, it probably makes sense to post sample data and expected output. If you are not familiar with how to do that, here are some links that can help guide you:

http://www.sqlservercentral.com/articles/Best+Practices/61537/
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
Go to Top of Page

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2014-04-23 : 13:41:44
Follow Lamprey's links - that will help. I still need to understand:

Are "renewals" stored in attribute.OrgMembership? What is the structure of that table and what are the values for a renewal row (when someone renews on 1/1 vs. 12/30)? If someone renews on 12/30 are they just a member for one day or are they a member until the next year on 12/29?

Be One with the Optimizer
TG
Go to Top of Page

techsupport14
Starting Member

18 Posts

Posted - 2014-04-23 : 15:16:12
quote:
Originally posted by TG

Follow Lamprey's links - that will help. I still need to understand:

Are "renewals" stored in attribute.OrgMembership? What is the structure of that table and what are the values for a renewal row (when someone renews on 1/1 vs. 12/30)? If someone renews on 12/30 are they just a member for one day or are they a member until the next year on 12/29?

Be One with the Optimizer
TG



1. Are "renewals" stored in attribute.OrgMembership? <-- yes
2. What is the structure of that table and what are the values for a renewal row (when someone renews on 1/1 vs. 12/30)? Id, membershipnumber, startdate, enddate (this the range of the membership duration), invoicenumber, createddate are fields of the table there are few other fields but the gist of it.
3. If someone renews on 12/30 are they just a member for one day or are they a member until the next year on 12/29? it's always from the date the record is generated (stored in startdate) until the current season (stored in enddate). Typically ending Jun or Sep as those are the membership enddates. Either in June or Sept. So if someone renews on 12/30 the start date would be 12/30 and the end date would be either 6/30 or 9/30 depending on what kind of membership they are registering.
Go to Top of Page

techsupport14
Starting Member

18 Posts

Posted - 2014-04-23 : 15:18:59
quote:
Originally posted by Lamprey

In order to help your in a reasonable way, it probably makes sense to post sample data and expected output. If you are not familiar with how to do that, here are some links that can help guide you:

http://www.sqlservercentral.com/articles/Best+Practices/61537/
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx



appreciate the guide.
But I don't understand in that I have posted the full SQL statements and the parts I'm having issues with.
Plus explanation best I know how.

based on this link
[url]http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx[/url]
Go to Top of Page

techsupport14
Starting Member

18 Posts

Posted - 2014-04-23 : 15:20:12
I don't know how else to explain:

quote:

NOT CURRENT member:
You became a member in 2012. EndDate year 2012
You renewed in 2013. EndDate year 2013
You didn't renew in 2014. EndDate year 2014
You are not current member because you did not renew in this year (current year).
There would be two records in the entity.OrgMembership table.


CURRENT Member:
I became a member in 2012. EndDate year 2012
You renewed in 2013. EndDate year 2013
You renewed in 2014. EndDate year 2014
You am a current member because I renewed this year (current year).
There would be three records in the entity.OrgMembership table.

I can't have you, the current member, show up as a NOT CURRENT member if I do a show all records with an EndDate < Year(GetDate()) because you have records in the table with EndDate less than this year.


Plus the two SQL statements that show what I explained.

Here it is again. This is CURRENT MEMBERS.

SELECT lookup.MemberTypes.Id AS ClubTypeID, lookup.MemberTypes.Description AS ClubType, entity.Organization.StatusId, entity.Organization.MembershipNumber,
attribute.OrgMembership.EndDate, attribute.OrgMembership.CreatedDate, YEAR(attribute.OrgMembership.EndDate) AS EndYear, attribute.OrgMembership.Id
FROM entity.Organization INNER JOIN
attribute.OrgMembership ON entity.Organization.Id = attribute.OrgMembership.OrganizationId INNER JOIN
lookup.MemberTypes ON attribute.OrgMembership.MembershipTypeId = lookup.MemberTypes.Id
WHERE (NOT (entity.Organization.MembershipNumber IN (4178, 4179))) AND (lookup.MemberTypes.Id IN (15, 20, 21)) AND (entity.Organization.StatusId BETWEEN 3 AND 5) AND
(YEAR(attribute.OrgMembership.EndDate) = YEAR({ fn CURDATE() }))

As long as they have a record where the EndDate is sometime this year, they are "CURRENT".

Here's a sample of the result:
15 Full Member Club 3 1278 2014-06-30 00:00:00.000 2013-08-26 20:35:53.840 2014 11086
15 Full Member Club 3 1280 2014-06-30 00:00:00.000 2013-10-14 20:54:05.560 2014 11167
15 Full Member Club 3 1281 2014-06-30 00:00:00.000 2013-10-22 13:24:17.860 2014 11228
15 Full Member Club 3 1290 2014-06-30 00:00:00.000 2013-07-28 10:46:52.780 2014 10828
15 Full Member Club 3 1292 2014-06-30 00:00:00.000 2013-08-14 11:22:05.653 2014 11019


Here is the SQL Statement for NOT CURRENT (which does not look at if the member has a current EndDate). A basic view that is saying show me all the records that are not equal to current year in EndDate:

SELECT lookup.MemberTypes.Id AS ClubTypeID, lookup.MemberTypes.Description AS ClubType, entity.Organization.StatusId, entity.Organization.MembershipNumber,
attribute.OrgMembership.EndDate, attribute.OrgMembership.CreatedDate, YEAR(attribute.OrgMembership.EndDate) AS EndYear, attribute.OrgMembership.Id
FROM entity.Organization INNER JOIN
attribute.OrgMembership ON entity.Organization.Id = attribute.OrgMembership.OrganizationId INNER JOIN
lookup.MemberTypes ON attribute.OrgMembership.MembershipTypeId = lookup.MemberTypes.Id
WHERE (NOT (entity.Organization.MembershipNumber IN (4178, 4179))) AND (lookup.MemberTypes.Id IN (15, 20, 21)) AND (entity.Organization.StatusId BETWEEN 3 AND 5) AND
(YEAR(attribute.OrgMembership.EndDate) < YEAR({ fn CURDATE() }))


Here is the NOT CURRENT results based on the above SQL Statement.
I am showing the same members between 1277 and 1292.
These records should NOT show as NOT CURRENT because they are current in the current SQL Statement.
20 School Affiliated Club 3 1277 2013-09-30 00:00:00.000 2012-11-28 10:18:29.670 2013
15 Full Member Club 3 1278 2013-06-30 00:00:00.000 2012-08-09 11:12:03.180 2013
15 Full Member Club 3 1280 2013-06-30 00:00:00.000 2012-08-12 17:10:25.847 2013
15 Full Member Club 3 1281 2013-06-30 00:00:00.000 2012-11-14 06:31:34.920 2013
15 Full Member Club 3 1290 2013-06-30 00:00:00.000 2012-08-08 14:02:28.720 2013
15 Full Member Club 3 1292 2013-06-30 00:00:00.000 2012-07or-27 20:37:15.560 2013


So how do I NOT show these records from showing up because they are in fact current members?

Was thinking something like:
NOT IN (SELECT OrganizationId FROM attribute.OrgMembership AS OrgMembership_1 WHERE (EndDate = YEAR(EndDate) - 1))

but I get the same member records as above.

I've never used a NOT IN before
Go to Top of Page

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2014-04-23 : 17:02:01
you keep saying: "where the EndDate is sometime this year, they are CURRENT".

but your answer to my question:
"it's always from the date the record is generated (stored in startdate) until the current season (stored in enddate)"
That is different and makes more sense.

In that case I would think these are simplified queries get to the heart of members vs. non-members. If that is correct then you can add in the joins to the lookup tables for the other attributes.

--current members
select eo.MembershipNumber
from attribute.OrgMembership am
inner join entity.Organization eo on eo.OrganizationId = am.OrganizationId
where getdate() between StartDate and EndDate

--current non-members
select eo.MembershipNumber
from entity.Organization eo
left outer join
(
select OrganizationId
from attribute.OrgMembership
where getdate() between StartDate and EndDate
) excl on excl.OrganizationId = eo.OrganizationId
where excl.OrganizationId is null


I obviously didn't test any of this because you didn't provide any of the executable DDL/DML that Lamprey suggested.

Be One with the Optimizer
TG
Go to Top of Page

techsupport14
Starting Member

18 Posts

Posted - 2014-04-23 : 21:22:29
quote:
Originally posted by TGyou keep saying: "where the EndDate is sometime this year, they are CURRENT".

but your answer to my question:
"it's always from the date the record is generated (stored in startdate) until the current season (stored in enddate)"
That is different and makes more sense.

In that case I would think these are simplified queries get to the heart of members vs. non-members. If that is correct then you can add in the joins to the lookup tables for the other attributes.


Hi TG,
Please forget about the StartDate. Has no relevance in what is trying to be accomplished here.

It is the EndDate.

Because it doesn't matter when the StartDate is for the query.

The point is to look at when the membership ends and ended.
And for each season (which is year long in the set membership end dates (which depends on what kind of membership, so as noted, it could end 6/30 or 9/30, hence it doesn't matter when you start your membership it'll end at the 6/30 or 9/30.

Either way, all memberships ending in the current year are considered CURRENT MEMBERS.

All memberships that ended last year BUT did not renew this year are considered NOT CURRENT MEMBERS.

As you can see there are membership records that are in there multiple times because you could have started a few years ago.
And renewed, you're a CURRENT MEMBER.

Say you did not renew this year, you would be a NOT CURRENT MEMBER.

BUT I can't just do a simple query that says show me all records without an EndDate of this year because it includes you since you were a member last year.

Maybe another way to put it is how many did not renew this year that were members last year?
Go to Top of Page

techsupport14
Starting Member

18 Posts

Posted - 2014-04-23 : 21:49:50
I thought that this would produce the correct results:

SELECT dbo.v030mbrshp05MemberClubsNotCurrent.ClubTypeID, dbo.v030mbrshp05MemberClubsNotCurrent.ClubType, dbo.v030mbrshp05MemberClubsNotCurrent.StatusId,
dbo.v030mbrshp05MemberClubsNotCurrent.MembershipNumber, dbo.v030mbrshp05MemberClubsNotCurrent.CreatedDate
FROM dbo.v030mbrshp05MemberClubs INNER JOIN
dbo.v030mbrshp05MemberClubsNotCurrent ON
dbo.v030mbrshp05MemberClubs.MembershipNumber <> dbo.v030mbrshp05MemberClubsNotCurrent.MembershipNumber
GROUP BY dbo.v030mbrshp05MemberClubsNotCurrent.ClubTypeID, dbo.v030mbrshp05MemberClubsNotCurrent.ClubType, dbo.v030mbrshp05MemberClubsNotCurrent.StatusId,
dbo.v030mbrshp05MemberClubsNotCurrent.MembershipNumber, dbo.v030mbrshp05MemberClubsNotCurrent.CreatedDate


Where the view dbo.v030mbrshp05MemberClubsNotCurrent is all records with EndDates of last year.
So with the join I thought it would give me records the membership does not exit in the dbo.v030mbrshp05MemberClubs.
But it's not. I can see the range of membershipnumbers from 1278-1281 in the dbo.v030mbrshp05MemberClubs AND in dbo.v030mbrshp05MemberClubsNotCurrent.

These membershipnumbers should NOT be showing up because they are renewed members.
Go to Top of Page

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2014-04-23 : 22:02:49
>>all memberships ending in the current year are considered CURRENT MEMBERS.
so if the membership ends on 6/30/2014 and today is 9/30/2014 they are current?

then in my example above replace this:
where getdate() between StartDate and EndDate
with this:
where datediff(year, getdate(), endDate) = 0


if that still doesn't do it then:
This could be really easy if you just follow those links - and post code to create your tables and code to populate them with some sample data. And indicate which orgs are current members and which are not.

Be One with the Optimizer
TG
Go to Top of Page

techsupport14
Starting Member

18 Posts

Posted - 2014-04-23 : 22:05:29
The dbo.v030mbrshp05MemberClubs shows 634 records.
So 634 members that have a EndDate of this year. (2014)

The dbo.v030mbrshp05MemberClubsNotCurrent shows 676 records that has a EndDate of last year. (2013)

Seemingly the difference are the records that did not renew. 676 last year's members and 634 this year's members.
So how do I write a query to show the 42 members that did NOT renew?


quote:

if that still doesn't do it then:
This could be really easy if you just follow those links - and post code to create your tables and code to populate them with some sample data. And indicate which orgs are current members and which are not.

Be One with the Optimizer
TG

Not sure what code and results I'm not posting because I've posted the code to produce current members.
And code to produce all members with an expired membership date (EndDate of last year's)?

So once again code for CURRENT MEMBERS is:

SELECT lookup.MemberTypes.Id AS ClubTypeID, lookup.MemberTypes.Description AS ClubType, entity.Organization.StatusId, entity.Organization.MembershipNumber,
attribute.OrgMembership.EndDate, attribute.OrgMembership.CreatedDate, YEAR(attribute.OrgMembership.EndDate) AS EndYear, attribute.OrgMembership.Id
FROM entity.Organization INNER JOIN
attribute.OrgMembership ON entity.Organization.Id = attribute.OrgMembership.OrganizationId INNER JOIN
lookup.MemberTypes ON attribute.OrgMembership.MembershipTypeId = lookup.MemberTypes.Id
WHERE (NOT (entity.Organization.MembershipNumber IN (4178, 4179))) AND (lookup.MemberTypes.Id IN (15, 20, 21)) AND (entity.Organization.StatusId BETWEEN 3 AND 5) AND
(YEAR(attribute.OrgMembership.EndDate) = YEAR({ fn CURDATE() }))


This is the code that shows all members with an EndDate of last year.
Another words, membership records that expired.

SELECT lookup.MemberTypes.Id AS ClubTypeID, lookup.MemberTypes.Description AS ClubType, entity.Organization.StatusId, entity.Organization.MembershipNumber,
attribute.OrgMembership.EndDate, attribute.OrgMembership.CreatedDate, YEAR(attribute.OrgMembership.EndDate) AS EndYear, attribute.OrgMembership.Id
FROM entity.Organization INNER JOIN
attribute.OrgMembership ON entity.Organization.Id = attribute.OrgMembership.OrganizationId INNER JOIN
lookup.MemberTypes ON attribute.OrgMembership.MembershipTypeId = lookup.MemberTypes.Id
WHERE (NOT (entity.Organization.MembershipNumber IN (4178, 4179))) AND (lookup.MemberTypes.Id IN (15, 20, 21)) AND (entity.Organization.StatusId BETWEEN 3 AND 5) AND
(YEAR(attribute.OrgMembership.EndDate) < YEAR({ fn CURDATE() }))


Both does show some same membershipnumbers.
So I need to show those that do not have the same membershipnumbers.

The seemingly 42 that are not in the 634 current memberships from the 676 expired memberships.
Go to Top of Page

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2014-04-23 : 22:56:26
Is this a typo?
quote:
So how do I write a query to show the 42 members that did NOT renew (have membership records with EndDate of this year)?
Because you've said (many times now) that memberships with EndDate this year are current.

And 676 noncurrent members - 634 current members to me doesn't necessarily mean that 42 didn't renew. couldn't some of the current members be brand new? and couldn't some of the noncurrent one not have been members for several years?

Also your original question was: "I am trying to get all the members that are NOT CURRENT."
now you say you have that query and you need a query to find the differences.

It is this type of ambiguity and changing questions you've posted throughout this thread that makes it impossible to answer without the DDL/DML and expected results based on the sample data.

>>not sure what code and results I'm not posting
Between Lamprey's links and my explanation of sample data we're asking for I don't know how else to answer that.

Be One with the Optimizer
TG
Go to Top of Page

techsupport14
Starting Member

18 Posts

Posted - 2014-04-24 : 00:46:28
quote:
Originally posted by TG

Is this a typo?
quote:
So how do I write a query to show the 42 members that did NOT renew (have membership records with EndDate of this year)?
Because you've said (many times now) that memberships with EndDate this year are current.

I can't help but notice that things were not understood as I posted in the original post about the NOT IN and tried to explain that with other explanations there after. And that they were misconstrued / misunderstood. And part of it is my part of trying to explain it. However after posting the obvious for each query I'm at a loss as to how else to explain it.

quote:

And 676 noncurrent members - 634 current members to me doesn't necessarily mean that 42 didn't renew. couldn't some of the current members be brand new? and couldn't some of the noncurrent one not have been members for several years?

Sure HOWEVER you must be missing the part in the queries provided it is saying where the memberships are "related" but show where they don't exist in (NOT IN) the current year's query from the not current year's query.

quote:

Also your original question was: "I am trying to get all the members that are NOT CURRENT."
now you say you have that query and you need a query to find the differences.

partially correct. you left out part of it. the part I posted many times, the BUT the WHERE parts. Which are key.

quote:

It is this type of ambiguity and changing questions you've posted throughout this thread that makes it impossible to answer without the DDL/DML and expected results based on the sample data.


sorry, it's not ambiguous when it is clearly spelled out what's in the CURRENT query and what's in the NOT CURRENT query and what needs to be figured out.

And as for the DDl/DML, I can't provide something I don't understand either. :(
As far as I can tell I've provide the entire queries of both PLUS the explanation of what each query is doing and what the end result needs to be. If that's not sufficient I really do not know what is?

quote:

>>not sure what code and results I'm not posting
Between Lamprey's links and my explanation of sample data we're asking for I don't know how else to answer that.

Be One with the Optimizer
TG


see above.

I do appreciate you trying to help ... I'm just not sure how else to go about this :(

Let's try yet another way, let me ask you questions so I can see if you understand what I'm writing.

1. Do you understand that there are multiple records of the same member? Because the member's record is entered for each season (annually).
2. If you understand that then you know that if you got a membership in 2012 and renewed in 2013 you'll have two records in the table. Correct?
3. If you understand that then you know that you IF you did not renew for the current year (2014) you would be considered NOT CURRENT. Correct?
4. If you understand that then you know that, going back to 2., there are two records in the membership table. Correct?
5. Do you understand then that if you did/do renew this year there would be 3 records in the membership table and you would be now a CURRENT member. Correct?
6. If you understand that and say you DID renew then you know the two simple queries will show your record for prior year and current year membership entries. As both current and not current because the simple query does NOT look at the fact that you renewed and exclude your record in the NOT CURRENT record. RE: NOT IN statement I originally posted trying to do.

So how do get ONLY the records that did not renew where these members records exists in the membership table BUT not a record for this year.

MembershipNumber = 1
MembershipEndDate = 6/30/2012

MembershipNumber = 1
MembershipEndDate = 6/30/2013

MembershipNumber = 1
MembershipEndDate = 6/30/2014

CURRENT query will show the record with EndDate of 6/30/2014
NOT CURRENT query will show the record with EndDate of 6/30/2013

TRULY NOT CURRENT would NOT show this member because they are actually CURRENT.

Hope this helps.
And hopefully you could help create that query to NOT include those CURRENT members that exist in the table with the expired EndDate.

RE: NOT IN()?
Go to Top of Page

Ifor
Aged Yak Warrior

700 Posts

Posted - 2014-04-24 : 06:37:05
Maybe I am missing something, but I fail to see what this has to do with NOT IN.

If you just what dates less than the current year use something like:

WHERE EndDate < DATEADD(year, DATEDIFF(year, 0, CURRENT_TIMESTAMP), 0)


eg

-- *** Test Data ***
CREATE TABLE #t
(
MemberId int NOT NULL
,EndDate datetime NOT NULL
)
INSERT INTO #t
SELECT 1, '20140203'
UNION ALL SELECT 1, '20130216'
UNION ALL SELECT 1, '20120318'
UNION ALL SELECT 2, '20140317'
UNION ALL SELECT 3, '20130708'
UNION ALL SELECT 3, '20120605'
UNION ALL SELECT 4, '20070505'
-- *** End Test Data ***

SELECT MemberId, EndDate
FROM #t T1
WHERE EndDate < DATEADD(year, DATEDIFF(year, 0, CURRENT_TIMESTAMP), 0)
AND NOT EXISTS
(
SELECT 1
FROM #t T2
WHERE T2.MemberId = T1.MemberId
AND EndDate >= DATEADD(year, DATEDIFF(year, 0, CURRENT_TIMESTAMP), 0)
);

--or with IN:
SELECT MemberId, EndDate
FROM #t
WHERE EndDate < DATEADD(year, DATEDIFF(year, 0, CURRENT_TIMESTAMP), 0)
AND NOT MemberId IN
(
SELECT MemberId
FROM #t
WHERE EndDate >= DATEADD(year, DATEDIFF(year, 0, CURRENT_TIMESTAMP), 0)
);

-- NOT IN will only work as you expect if there are no NULLS in the result set.
-- NOT IN should only be used if you understand 3 value logic and wnat to handle the NULLS.
SELECT MemberId, EndDate
FROM #t
WHERE EndDate < DATEADD(year, DATEDIFF(year, 0, CURRENT_TIMESTAMP), 0)
AND MemberId NOT IN
(
SELECT MemberId
FROM #t
WHERE EndDate >= DATEADD(year, DATEDIFF(year, 0, CURRENT_TIMESTAMP), 0)
);


--or just the latest year they where members:
WITH ExMembers
AS
(
SELECT MemberId, EndDate
,ROW_NUMBER() OVER (PARTITION BY MemberId ORDER BY EndDate DESC) rn
FROM #t T1
WHERE EndDate < DATEADD(year, DATEDIFF(year, 0, CURRENT_TIMESTAMP), 0)
AND NOT EXISTS
(
SELECT 1
FROM #t T2
WHERE T2.MemberId = T1.MemberId
AND EndDate >= DATEADD(year, DATEDIFF(year, 0, CURRENT_TIMESTAMP), 0)
)
)
SELECT MemberId, EndDate
FROM ExMembers
WHERE rn = 1;


Go to Top of Page

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-04-24 : 08:03:06
You really need to post some DDL and DML to build sample tables, along with the output you want to see. We're all just shooting in the dark here since we have nothing concrete to work with.
Go to Top of Page

techsupport14
Starting Member

18 Posts

Posted - 2014-04-24 : 10:21:37
gbritton,
I did provide sample data with the queries that produces them.
quote:

Here it is again. This is CURRENT MEMBERS.

SELECT lookup.MemberTypes.Id AS ClubTypeID, lookup.MemberTypes.Description AS ClubType, entity.Organization.StatusId, entity.Organization.MembershipNumber,
attribute.OrgMembership.EndDate, attribute.OrgMembership.CreatedDate, YEAR(attribute.OrgMembership.EndDate) AS EndYear, attribute.OrgMembership.Id
FROM entity.Organization INNER JOIN
attribute.OrgMembership ON entity.Organization.Id = attribute.OrgMembership.OrganizationId INNER JOIN
lookup.MemberTypes ON attribute.OrgMembership.MembershipTypeId = lookup.MemberTypes.Id
WHERE (NOT (entity.Organization.MembershipNumber IN (4178, 4179))) AND (lookup.MemberTypes.Id IN (15, 20, 21)) AND (entity.Organization.StatusId BETWEEN 3 AND 5) AND
(YEAR(attribute.OrgMembership.EndDate) = YEAR({ fn CURDATE() }))

As long as they have a record where the EndDate is sometime this year, they are "CURRENT".

Here's a sample of the result:
15 Full Member Club 3 1278 2014-06-30 00:00:00.000 2013-08-26 20:35:53.840 2014 11086
15 Full Member Club 3 1280 2014-06-30 00:00:00.000 2013-10-14 20:54:05.560 2014 11167
15 Full Member Club 3 1281 2014-06-30 00:00:00.000 2013-10-22 13:24:17.860 2014 11228
15 Full Member Club 3 1290 2014-06-30 00:00:00.000 2013-07-28 10:46:52.780 2014 10828
15 Full Member Club 3 1292 2014-06-30 00:00:00.000 2013-08-14 11:22:05.653 2014 11019


Here is the SQL Statement for NOT CURRENT (which does not look at if the member has a current EndDate). A basic view that is saying show me all the records that are not equal to current year in EndDate:

SELECT lookup.MemberTypes.Id AS ClubTypeID, lookup.MemberTypes.Description AS ClubType, entity.Organization.StatusId, entity.Organization.MembershipNumber,
attribute.OrgMembership.EndDate, attribute.OrgMembership.CreatedDate, YEAR(attribute.OrgMembership.EndDate) AS EndYear, attribute.OrgMembership.Id
FROM entity.Organization INNER JOIN
attribute.OrgMembership ON entity.Organization.Id = attribute.OrgMembership.OrganizationId INNER JOIN
lookup.MemberTypes ON attribute.OrgMembership.MembershipTypeId = lookup.MemberTypes.Id
WHERE (NOT (entity.Organization.MembershipNumber IN (4178, 4179))) AND (lookup.MemberTypes.Id IN (15, 20, 21)) AND (entity.Organization.StatusId BETWEEN 3 AND 5) AND
(YEAR(attribute.OrgMembership.EndDate) < YEAR({ fn CURDATE() }))


Here is the NOT CURRENT results based on the above SQL Statement.
I am showing the same members between 1278 and 1292.
These records should NOT show as NOT CURRENT because they are current in the current SQL Statement.
15 Full Member Club 3 1278 2013-06-30 00:00:00.000 2012-08-09 11:12:03.180 2013
15 Full Member Club 3 1280 2013-06-30 00:00:00.000 2012-08-12 17:10:25.847 2013
15 Full Member Club 3 1281 2013-06-30 00:00:00.000 2012-11-14 06:31:34.920 2013
15 Full Member Club 3 1290 2013-06-30 00:00:00.000 2012-08-08 14:02:28.720 2013
15 Full Member Club 3 1292 2013-06-30 00:00:00.000 2012-07or-27 20:37:15.560 2013


So how do I NOT show these records from showing up because they are in fact current members?

Was thinking something like:
NOT IN (SELECT OrganizationId FROM attribute.OrgMembership AS OrgMembership_1 WHERE (EndDate = YEAR(EndDate) - 1))

but I get the same member records as above.

I've never used a NOT IN before



So please tell me from this what is still missing?


Iifor,
Thank you, it looks like you are using NOT IN / NOT EXIST.
I'll try it.
Go to Top of Page
    Next Page

- Advertisement -