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.
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.IdFROM entity.Organization INNER JOIN attribute.OrgMembership ON entity.Organization.Id = attribute.OrgMembership.OrganizationId INNER JOIN lookup.MemberTypes ON attribute.OrgMembership.MembershipTypeId = lookup.MemberTypes.IdWHERE (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 OptimizerTG |
|
|
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.IdFROM entity.Organization entOrg INNER JOIN attribute.OrgMembership attOrg ON entOrg.Id = attOrg.OrganizationId INNER JOIN lookup.MemberTypes types ON attOrg.MembershipTypeId = types.IdWHERE (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. |
|
|
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 2012You renewed in 2013. EndDate year 2013You didn't renew in 2014. EndDate year 2014You 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 2012You renewed in 2013. EndDate year 2013You renewed in 2014. EndDate year 2014You 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. |
|
|
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? |
|
|
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.IdFROM entity.Organization INNER JOIN attribute.OrgMembership ON entity.Organization.Id = attribute.OrgMembership.OrganizationId INNER JOIN lookup.MemberTypes ON attribute.OrgMembership.MembershipTypeId = lookup.MemberTypes.IdWHERE (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.IdFROM entity.Organization INNER JOIN attribute.OrgMembership ON entity.Organization.Id = attribute.OrgMembership.OrganizationId INNER JOIN lookup.MemberTypes ON attribute.OrgMembership.MembershipTypeId = lookup.MemberTypes.IdWHERE (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? |
|
|
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 |
|
|
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 OptimizerTG |
|
|
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 OptimizerTG
1. Are "renewals" stored in attribute.OrgMembership? <-- yes2. 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. |
|
|
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] |
|
|
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 2012You renewed in 2013. EndDate year 2013You didn't renew in 2014. EndDate year 2014You 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 2012You renewed in 2013. EndDate year 2013You renewed in 2014. EndDate year 2014You 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.IdFROM entity.Organization INNER JOIN attribute.OrgMembership ON entity.Organization.Id = attribute.OrgMembership.OrganizationId INNER JOIN lookup.MemberTypes ON attribute.OrgMembership.MembershipTypeId = lookup.MemberTypes.IdWHERE (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 1108615 Full Member Club 3 1280 2014-06-30 00:00:00.000 2013-10-14 20:54:05.560 2014 1116715 Full Member Club 3 1281 2014-06-30 00:00:00.000 2013-10-22 13:24:17.860 2014 1122815 Full Member Club 3 1290 2014-06-30 00:00:00.000 2013-07-28 10:46:52.780 2014 1082815 Full Member Club 3 1292 2014-06-30 00:00:00.000 2013-08-14 11:22:05.653 2014 11019Here 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.IdFROM entity.Organization INNER JOIN attribute.OrgMembership ON entity.Organization.Id = attribute.OrgMembership.OrganizationId INNER JOIN lookup.MemberTypes ON attribute.OrgMembership.MembershipTypeId = lookup.MemberTypes.IdWHERE (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 201315 Full Member Club 3 1278 2013-06-30 00:00:00.000 2012-08-09 11:12:03.180 201315 Full Member Club 3 1280 2013-06-30 00:00:00.000 2012-08-12 17:10:25.847 201315 Full Member Club 3 1281 2013-06-30 00:00:00.000 2012-11-14 06:31:34.920 201315 Full Member Club 3 1290 2013-06-30 00:00:00.000 2012-08-08 14:02:28.720 201315 Full Member Club 3 1292 2013-06-30 00:00:00.000 2012-07or-27 20:37:15.560 2013So 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 |
|
|
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 membersselect eo.MembershipNumberfrom attribute.OrgMembership aminner join entity.Organization eo on eo.OrganizationId = am.OrganizationIdwhere getdate() between StartDate and EndDate--current non-membersselect eo.MembershipNumberfrom entity.Organization eoleft outer join ( select OrganizationId from attribute.OrgMembership where getdate() between StartDate and EndDate ) excl on excl.OrganizationId = eo.OrganizationIdwhere 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 OptimizerTG |
|
|
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? |
|
|
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.CreatedDateFROM dbo.v030mbrshp05MemberClubs INNER JOIN dbo.v030mbrshp05MemberClubsNotCurrent ON dbo.v030mbrshp05MemberClubs.MembershipNumber <> dbo.v030mbrshp05MemberClubsNotCurrent.MembershipNumberGROUP 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. |
|
|
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 EndDatewith this:where datediff(year, getdate(), endDate) = 0if 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 OptimizerTG |
|
|
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 OptimizerTG
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.IdFROM entity.Organization INNER JOIN attribute.OrgMembership ON entity.Organization.Id = attribute.OrgMembership.OrganizationId INNER JOIN lookup.MemberTypes ON attribute.OrgMembership.MembershipTypeId = lookup.MemberTypes.IdWHERE (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.IdFROM entity.Organization INNER JOIN attribute.OrgMembership ON entity.Organization.Id = attribute.OrgMembership.OrganizationId INNER JOIN lookup.MemberTypes ON attribute.OrgMembership.MembershipTypeId = lookup.MemberTypes.IdWHERE (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. |
|
|
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 postingBetween 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 OptimizerTG |
|
|
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 postingBetween 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 OptimizerTG
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 = 1MembershipEndDate = 6/30/2012MembershipNumber = 1MembershipEndDate = 6/30/2013MembershipNumber = 1MembershipEndDate = 6/30/2014CURRENT query will show the record with EndDate of 6/30/2014NOT CURRENT query will show the record with EndDate of 6/30/2013TRULY 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()? |
|
|
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 #tSELECT 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, EndDateFROM #t T1WHERE 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, EndDateFROM #tWHERE 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, EndDateFROM #tWHERE 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 ExMembersAS( 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, EndDateFROM ExMembersWHERE rn = 1; |
|
|
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. |
|
|
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.IdFROM entity.Organization INNER JOIN attribute.OrgMembership ON entity.Organization.Id = attribute.OrgMembership.OrganizationId INNER JOIN lookup.MemberTypes ON attribute.OrgMembership.MembershipTypeId = lookup.MemberTypes.IdWHERE (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 1108615 Full Member Club 3 1280 2014-06-30 00:00:00.000 2013-10-14 20:54:05.560 2014 1116715 Full Member Club 3 1281 2014-06-30 00:00:00.000 2013-10-22 13:24:17.860 2014 1122815 Full Member Club 3 1290 2014-06-30 00:00:00.000 2013-07-28 10:46:52.780 2014 1082815 Full Member Club 3 1292 2014-06-30 00:00:00.000 2013-08-14 11:22:05.653 2014 11019Here 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.IdFROM entity.Organization INNER JOIN attribute.OrgMembership ON entity.Organization.Id = attribute.OrgMembership.OrganizationId INNER JOIN lookup.MemberTypes ON attribute.OrgMembership.MembershipTypeId = lookup.MemberTypes.IdWHERE (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 201315 Full Member Club 3 1280 2013-06-30 00:00:00.000 2012-08-12 17:10:25.847 201315 Full Member Club 3 1281 2013-06-30 00:00:00.000 2012-11-14 06:31:34.920 201315 Full Member Club 3 1290 2013-06-30 00:00:00.000 2012-08-08 14:02:28.720 201315 Full Member Club 3 1292 2013-06-30 00:00:00.000 2012-07or-27 20:37:15.560 2013So 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. |
|
|
Next Page
|
|
|
|
|