OUTER JOIN not producing records when it does not exist in one table?

techsupport15 · August 18, 2015, 10:14pm

I tried both and both produced the same results

sorry, they wouldn't allow me to post for 3 hours due to daily limit??
Also, how do I add an attachment? I can upload the tables in Access and a spreadsheet of what I'm looking for the formula to produce.

SELECT     c.ClubNo, c.SortName, c.ClubName, c.BSProgram, c.ClubSection, c.Code, c.President, c.Email, c.Phone, c.FacilityName, c.StreetOne, c.StreetTwo, c.City, c.State, c.PostalCode, c.URL, c.Certified, c.FacilityLastUpdate, c.ByLawsUploadDate,
c.ProgramStatusId, c.Status, YEAR(p.EndDate) AS YearEnd, RTRIM(p.InvoiceNumber) AS InvNo, p.MembershipTypeId, p.PersonId, c.ClubID
FROM dbo.v060ClubOfficersPresOrNot AS c LEFT OUTER JOIN
attribute.PersonMembership AS p ON p.OrganizationId = c.ClubID AND p.EndDate
>= GETDATE() AND GETDATE() >= DATEADD(month, -6 - CASE WHEN p.MembershipTypeId = 3 THEN 36 ELSE 0 END, DATEADD(year, DATEDIFF(year, 0, DATEADD(month, DATEDIFF(month, 0, p.EndDate) + 6, 0)), 0)) AND p.MembershipTypeId IN (1, 2, 3, 4)
GROUP BY c.ClubNo, c.SortName, c.ClubName, c.BSProgram, c.ClubSection, c.Code, RTRIM(p.InvoiceNumber), c.President, c.Email, c.Phone, c.FacilityName, c.StreetOne, c.StreetTwo, c.City, c.State, c.PostalCode, YEAR(p.EndDate), p.MembershipTypeId, c.URL, c.Certified, c.FacilityLastUpdate, c.ByLawsUploadDate, c.ProgramStatusId, c.Status, p.PersonId, c.ClubID
HAVING     
(c.ClubNo = 5305 OR                  
c.ClubNo = 8004)

bitsmed · August 19, 2015, 5:01am

Just a few sample rows of the rows you are missing would do.
Look here (section 2 & 3).

Kristen · August 19, 2015, 7:26am

There is a file-upload icon on the editor toolbar, but maybe you don;t have enough posts to have access to that? if so you'd have to upload somewhere else and link to that file I'm afraid.

techsupport15 · August 19, 2015, 1:48pm

So the highlighted row and the last row your formula is showing.
though this is details here, then i would Group and do the count from the details.

I need the 1st 15 records for ClubNo 5305.

ClubID
ClubNo
SortName
ClubName
FirstName
MembershipTypeId
EndDate
InvoiceNumber
PersonMembership_PersonId

ORG00004619
5305
Shoreline
Shoreline Skating Club
Mandy
1
6/30/2015

PER00147307

ORG00004619
5305
Shoreline
Shoreline Skating Club
Mandy
1
6/30/2015

PER01271204

ORG00004619
5305
Shoreline
Shoreline Skating Club
Mandy
1
6/30/2015
0011069
PER00009323

ORG00004619
5305
Shoreline
Shoreline Skating Club
Mandy
1
6/30/2015
0011069
PER01271203

ORG00004619
5305
Shoreline
Shoreline Skating Club
Mandy
1
6/30/2015
0014744
PER00034123

ORG00004619
5305
Shoreline
Shoreline Skating Club
Mandy
1
6/30/2015
0014744
PER00334157

ORG00004619
5305
Shoreline
Shoreline Skating Club
Mandy
1
6/30/2015
0015085
PER00020124

ORG00004619
5305
Shoreline
Shoreline Skating Club
Mandy
1
6/30/2015
0015112
PER00386833

ORG00004619
5305
Shoreline
Shoreline Skating Club
Mandy
1
6/30/2015
0015112
PER00392472

ORG00004619
5305
Shoreline
Shoreline Skating Club
Mandy
1
6/30/2015
0015112
PER00475635

ORG00004619
5305
Shoreline
Shoreline Skating Club
Mandy
1
6/30/2015
0015112
PER00817444

ORG00004619
5305
Shoreline
Shoreline Skating Club
Mandy
1
6/30/2015
0015112
PER01271200

ORG00004619
5305
Shoreline
Shoreline Skating Club
Mandy
1
6/30/2015
0015141
PER01269327

ORG00004619
5305
Shoreline
Shoreline Skating Club
Mandy
1
6/30/2015
0015141
PER01271202

ORG00004619
5305
Shoreline
Shoreline Skating Club
Mandy
2
6/30/2015
0015112
PER00818801

ORG00004619
5305
Shoreline
Shoreline Skating Club
Mandy

ORG00001430
8004

Florida Interclub Skating Council
Charlotte

ORG00001430
8004

Florida Interclub Skating Council
David

ORG00001430
8004

Florida Interclub Skating Council
Earl

ORG00001430
8004

Florida Interclub Skating Council
Jacqueline

techsupport15 · August 19, 2015, 1:49pm

Yes, New Users Cannot Upload

bitsmed · August 19, 2015, 5:25pm

Another attempt (using startdate):

declare @season int=2015;
declare @seasonstart date=dateadd(month,6,dateadd(year,@season-1901,0));
declare @seasonend date=dateadd(year,1,@seasonstart);

select c.ClubNo
      ,c.SortName
      ,c.ClubName
      ,c.BSProgram
      ,c.ClubSection
      ,c.Code
      ,rtrim(p.InvoiceNumber) as InvNo
      ,c.President
      ,c.Email
      ,c.Phone
      ,c.FacilityName
      ,c.StreetOne
      ,c.StreetTwo
      ,c.City
      ,c.State
      ,c.PostalCode
      ,year(p.EndDate) as YearEnd
      ,p.MembershipTypeId
      ,c.URL
      ,c.Certified
      ,c.FacilityLastUpdate
      ,c.ByLawsUploadDate
      ,c.ProgramStatusId
      ,c.Status
  from dbo.v060ClubOfficersPresOrNot as c
       left outer join attribute.PersonMembership as p
                    on p.OrganizationId=c.ClubID
                   and p.StartDate<@seasonend
                   and p.EndDate>=@seasonstart
                   and p.MembershipTypeId in (1,2,3,4)

Another attempt (using enddate to calculate startdate):

declare @season int=2015;
declare @seasonstart date=dateadd(month,6,dateadd(year,@season-1901,0));
declare @seasonend date=dateadd(year,1,@seasonstart);

select c.ClubNo
      ,c.SortName
      ,c.ClubName
      ,c.BSProgram
      ,c.ClubSection
      ,c.Code
      ,rtrim(p.InvoiceNumber) as InvNo
      ,c.President
      ,c.Email
      ,c.Phone
      ,c.FacilityName
      ,c.StreetOne
      ,c.StreetTwo
      ,c.City
      ,c.State
      ,c.PostalCode
      ,year(p.EndDate) as YearEnd
      ,p.MembershipTypeId
      ,c.URL
      ,c.Certified
      ,c.FacilityLastUpdate
      ,c.ByLawsUploadDate
      ,c.ProgramStatusId
      ,c.Status
  from dbo.v060ClubOfficersPresOrNot as c
       left outer join attribute.PersonMembership as p
                    on p.OrganizationId=c.ClubID
                   and dateadd(month
                              ,6-case when p.MembershipTypeId=3 then 24 else 0 end
                              ,dateadd(year,datediff(year,0,dateadd(month,(datediff(month,0,p.EndDate))-6,0)),0)
                              )
                       <@seasonend
                   and p.EndDate>=@seasonstart
                   and p.MembershipTypeId in (1,2,3,4)

techsupport15 · August 19, 2015, 6:08pm

Currently unable to access the SQL Server to try your modifications.

A quick note though to be sure I made sense on what I'm stuck with and looking for:

The dbo.v060ClubOfficersPresOrNot is supposed to all have data on ALL Clubs and their info WITH President, if a President exist, if not still show the Club info without the President. Which it is.

attribute.PersonMembership is only if there's a membership so there should never be a record in that table with a missing EndDate.

Not all Clubs will have a membership.

So they want to see ALL Clubs and their President or Not (hence the View name "ClubOfficersPresOrNot" ) and whether they have a membership or not and how many. So would show 15 for 5305 in 2015 0 in 2016, etc ... and 8004 0 in 2015 and 0 in 2016.

You're an organization ("Club") but haven't purchased a membership or did last time but not this time.
Report needs to show your Organization with or without membership(s) and the end goal is how many your Club purchased memberships or not at any given time.

I can do the final grouping and the count once the details of the info sought is figured out.

techsupport15 · August 19, 2015, 7:35pm

Ok, got on to the SQL Server.

btw - the other formula should what needed for 5305 EXCEPT it also needs to include the current year 2015 expired memberships. As the EndDate range for the report is >= 6/30/currentyear when MemberTypeID = 1,2,4 and >=6/30/currentyear+4 when MemberTypeID = 3

Both are producing the same results. It's including EndYear 2014.
Don't need 2014, need current and future years AND when there is no membership even if they had it.

Because 8004 doesn't have membership BUT does show and 5305 has a membership BUT does not for next year BUT still needs to show that, even though they have memberships ending this year, 2015, that they don't have a "current" membership which ends in 2016.

As you've noticed the enddate is 6/30/yyyy. So this year, 2015, are expired, and next year, which is deemed "current" is for 2016 +.

And ALL Clubs needs to show whether they were current and not current now or didn't have any like 8004.

So the details show the 15 PersonID with the corresponding Invoices with EndDates for 5305, which will count to having 15 number of memberships in the final grouping and count but for EndYear 2015.
BUT also needs to show, the grouping step for EndYear 2016 with 0 membership count.
Like it is for 8004.

bitsmed · August 19, 2015, 8:13pm

Please provide sample data of the results you get with endyear 2014 (formated as insert statements like described in section 3 here).

The last query I posted, shows only results where endyear is what you declare here (not future endyears):

declare @season int=2015;

If your result should show all records with enddate >= 2015, then this sould surfice:

declare @season int=2015;
declare @seasonstart date=dateadd(month,6,dateadd(year,@season-1901,0));

select c.ClubNo
      ,c.SortName
      ,c.ClubName
      ,c.BSProgram
      ,c.ClubSection
      ,c.Code
      ,rtrim(p.InvoiceNumber) as InvNo
      ,c.President
      ,c.Email
      ,c.Phone
      ,c.FacilityName
      ,c.StreetOne
      ,c.StreetTwo
      ,c.City
      ,c.State
      ,c.PostalCode
      ,year(p.EndDate) as YearEnd
      ,p.MembershipTypeId
      ,c.URL
      ,c.Certified
      ,c.FacilityLastUpdate
      ,c.ByLawsUploadDate
      ,c.ProgramStatusId
      ,c.Status
  from dbo.v060ClubOfficersPresOrNot as c
       left outer join attribute.PersonMembership as p
                    on p.OrganizationId=c.ClubID
                   and p.EndDate>=@seasonstart
                   and p.MembershipTypeId in (1,2,3,4)

techsupport15 · August 19, 2015, 8:28pm

Ugh, just realized that the DECLARE states only 2015.
I changed it to 2016 and is in fact seeing 5305 with NULL values in the membership fields.

What I was looking for. Thank you!

I think I can work with what's there now to include all the fields needed.
Can I just change
declare @season int=2015;
to
declare @season int>=Year(GetDate());

techsupport15 · August 19, 2015, 8:32pm

[code]
use tempdb;

CREATE TABLE v060ClubOfficersPresOrNot(
ClubID VARCHAR(11) NOT NULL
,ClubNo INTEGER NOT NULL
,SortName VARCHAR(11)
,ClubName VARCHAR(33)
,Contact VARCHAR(22)
,USFSA INTEGER
,StreetOne VARCHAR(22)
,StreetTwo VARCHAR(4)
,CareOf VARCHAR(16)
,City VARCHAR(11)
,State VARCHAR(4)
,PostalCode VARCHAR(5)
,OrgType VARCHAR(12)
,ClubRegion VARCHAR(17)
,ClubSection VARCHAR(10)
,Email VARCHAR(24)
,Phone VARCHAR(13)
,FirstName VARCHAR(10)
,FacilityName VARCHAR(25)
,PresClubSec VARCHAR(7)
,PresUSFSA VARCHAR(7)
,PresCareOf VARCHAR(16)
,PresAdd1 VARCHAR(22)
,PresAdd2 VARCHAR(4)
,PresCity VARCHAR(11)
,PresState VARCHAR(4)
,PresZip VARCHAR(5)
,PersonId VARCHAR(11)
,President VARCHAR(17)
,PresEmail VARCHAR(21)
,PresPhone VARCHAR(12)
,StatusId VARCHAR(4)
,URL VARCHAR(16)
,Certified VARCHAR(4)
,Code VARCHAR(4)
,FacilityLastUpdate VARCHAR(23)
,ByLawsUploadDate VARCHAR(4)
,BSProgram VARCHAR(3)
,ProgramStatusId VARCHAR(4)
,Status VARCHAR(6)
,IsActive BIT
);
INSERT INTO v060ClubOfficersPresOrNot(ClubID,ClubNo,SortName,ClubName,Contact,USFSA,StreetOne,StreetTwo,CareOf,City,State,PostalCode,OrgType,ClubRegion,ClubSection,Email,Phone,FirstName,FacilityName,PresClubSec,PresUSFSA,PresCareOf,PresAdd1,PresAdd2,PresCity,PresState,PresZip,PersonId,President,PresEmail,PresPhone,StatusId,URL,Certified,Code,FacilityLastUpdate,ByLawsUploadDate,BSProgram,ProgramStatusId,Status,IsActive) VALUES
('ORG00000201',1617,NULL,'Cedar Valley FSC','Abby Loeffelholz',554410,NULL,NULL,NULL,NULL,NULL,NULL,'Regular Club','Upper Great Lakes','Midwestern',NULL,NULL,'Abby',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'No',NULL,NULL,0)
,('ORG00000201',1617,NULL,'Cedar Valley FSC','Carrie Schaefer',1185225,NULL,NULL,NULL,NULL,NULL,NULL,'Regular Club','Upper Great Lakes','Midwestern',NULL,NULL,'Carrie',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'No',NULL,NULL,0)
,('ORG00000201',1617,NULL,'Cedar Valley FSC','Karen Bushanam',1097873,NULL,NULL,NULL,NULL,NULL,NULL,'Regular Club','Upper Great Lakes','Midwestern',NULL,NULL,'Karen',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'No',NULL,NULL,0)
,('ORG00000201',1617,NULL,'Cedar Valley FSC','Keith Loeffelholz',1508633,NULL,NULL,NULL,NULL,NULL,NULL,'Regular Club','Upper Great Lakes','Midwestern',NULL,NULL,'Keith',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'No',NULL,NULL,0)
,('ORG00000201',1617,NULL,'Cedar Valley FSC','Virginia Fienup',1453716,NULL,NULL,NULL,NULL,NULL,NULL,'Regular Club','Upper Great Lakes','Midwestern',NULL,NULL,'Virginia',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'No',NULL,NULL,0)
,('ORG00000380',22,'ADIRONDACKS','SC of the Adirondacks, Inc.','Jackie Germaine',8925101,'Ruger St',NULL,'MarkChristiansen','Plattsburg','NY','12901','Regular Club','North Atlantic','Eastern','christma@plattsburgh.edu',NULL,'Jackie','Stafford Arena','Eastern','8930130','MarkChristiansen','Ruger St',NULL,'Plattsburg','NY','12901','PER01341423','Jeanette Woodruff','jwoodruff@saranac.org',NULL,'3',NULL,'Yes','NA','2014-07-06 17:16:00.000',NULL,'Yes','3','Active',1)
,('ORG00000380',22,'ADIRONDACKS','SC of the Adirondacks, Inc.','Jeanette Woodruff',8930130,'Ruger St',NULL,'MarkChristiansen','Plattsburg','NY','12901','Regular Club','North Atlantic','Eastern','christma@plattsburgh.edu',NULL,'Jeanette','Stafford Arena','Eastern','8930130','MarkChristiansen','Ruger St',NULL,'Plattsburg','NY','12901','PER01341423','Jeanette Woodruff','jwoodruff@saranac.org',NULL,'3',NULL,'Yes','NA','2014-07-06 17:16:00.000',NULL,'Yes','3','Active',1)
,('ORG00000380',22,'ADIRONDACKS','SC of the Adirondacks, Inc.','Martha Breyette',8676581,'Ruger St',NULL,'MarkChristiansen','Plattsburg','NY','12901','Regular Club','North Atlantic','Eastern','christma@plattsburgh.edu',NULL,'Martha','Stafford Arena','Eastern','8930130','MarkChristiansen','Ruger St',NULL,'Plattsburg','NY','12901','PER01341423','Jeanette Woodruff','jwoodruff@saranac.org',NULL,'3',NULL,'Yes','NA','2014-07-06 17:16:00.000',NULL,'Yes','3','Active',1)
,('ORG00000380',22,'ADIRONDACKS','SC of the Adirondacks, Inc.','Robin Vanweort',1207724,'Ruger St',NULL,'MarkChristiansen','Plattsburg','NY','12901','Regular Club','North Atlantic','Eastern','christma@plattsburgh.edu',NULL,'Robin','Stafford Arena','Eastern','8930130','MarkChristiansen','Ruger St',NULL,'Plattsburg','NY','12901','PER01341423','Jeanette Woodruff','jwoodruff@saranac.org',NULL,'3',NULL,'Yes','NA','2014-07-06 17:16:00.000',NULL,'Yes','3','Active',1)
,('ORG00000380',22,'ADIRONDACKS','SC of the Adirondacks, Inc.','Susan Billow',8875466,'Ruger St',NULL,'MarkChristiansen','Plattsburg','NY','12901','Regular Club','North Atlantic','Eastern','christma@plattsburgh.edu',NULL,'Susan','Stafford Arena','Eastern','8930130','MarkChristiansen','Ruger St',NULL,'Plattsburg','NY','12901','PER01341423','Jeanette Woodruff','jwoodruff@saranac.org',NULL,'3',NULL,'Yes','NA','2014-07-06 17:16:00.000',NULL,'Yes','3','Active',1)
,('ORG00001430',8004,NULL,'Florida Interclub Skating Council','Charlotte Schermerhorn',553074,NULL,NULL,NULL,NULL,NULL,NULL,'Interclubs','South Atlantic','Eastern',NULL,NULL,'Charlotte',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'No',NULL,NULL,0)
,('ORG00001430',8004,NULL,'Florida Interclub Skating Council','David Thompson',403333,NULL,NULL,NULL,NULL,NULL,NULL,'Interclubs','South Atlantic','Eastern',NULL,NULL,'David',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'No',NULL,NULL,0)
,('ORG00001430',8004,NULL,'Florida Interclub Skating Council','Earl Roberts',538190,NULL,NULL,NULL,NULL,NULL,NULL,'Interclubs','South Atlantic','Eastern',NULL,NULL,'Earl',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'No',NULL,NULL,0)
,('ORG00001430',8004,NULL,'Florida Interclub Skating Council','Jacqueline Addis',551706,NULL,NULL,NULL,NULL,NULL,NULL,'Interclubs','South Atlantic','Eastern',NULL,NULL,'Jacqueline',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'No',NULL,NULL,0)
,('ORG00004619',5305,'Shoreline','Shoreline Skating Club','Mandy Curtin',1561794,'121 Donald Lynch Blvd.',NULL,NULL,'Marlborough','MA','01752','Regular Club','New England','Eastern',NULL,'(508)229-2700','Mandy','New England Sports Center','Eastern','1561794',NULL,'121 Donald Lynch Blvd.',NULL,'Marlborough','MA','01752','PER00334157','Mandy Curtin','mandycurtin64@msn.com','617-909-0578','3','www.scboston.org','Yes','NE','2014-07-02 13:44:00.000',NULL,'No',NULL,'Active',1);[/code]

bitsmed · August 19, 2015, 8:33pm

Sure no problem

techsupport15 · August 19, 2015, 8:38pm

CREATE TABLE PersonMembership( Id INTEGER NOT NULL ,PersonId VARCHAR(11) ,OrganizationId VARCHAR(11) ,MembershipTypeId INTEGER ,StartDate DATETIME ,EndDate DATETIME ,InvoiceNumber VARCHAR(17) ,XfrRequestedToClubId VARCHAR(11) ,IsReleased BIT ,CreatedById VARCHAR(11) ,CreatedDate DATETIME ,ModifiedById VARCHAR(11) ,Timestamp INTEGER ,PaymentDate DATETIME ,EndYear INTEGER ); INSERT INTO PersonMembership(Id,PersonId,OrganizationId,MembershipTypeId,StartDate,EndDate,InvoiceNumber,XfrRequestedToClubId,IsReleased,CreatedById,CreatedDate,ModifiedById,PaymentDate,EndYear) VALUES (3205076,'PER00817444','ORG00004619',1,'2014-07-01 00:00:00.000','2015-06-30 00:00:00.000','0015112',NULL,0,'PER01084657','2014-06-25 13:54:02.000','PER01084657','2014-06-25 00:00:00.000',2015) ,(3205078,'PER00475635','ORG00004619',1,'2014-07-01 00:00:00.000','2015-06-30 00:00:00.000','0015112',NULL,0,'PER01084657','2014-06-25 13:54:02.000','PER01084657','2014-06-25 00:00:00.000',2015) ,(3205112,'PER00020124','ORG00004619',1,'2014-06-25 00:00:00.000','2015-06-30 00:00:00.000','0015085',NULL,0,'PER00474964','2014-06-25 00:00:00.000','PER00087235','2014-06-25 00:00:00.000',2015) ,(3205079,'PER01271203','ORG00004619',1,'2014-06-25 00:00:00.000','2015-06-30 00:00:00.000','0011069',NULL,0,'PER00474964','2014-06-25 00:00:00.000',NULL,'2014-06-11 00:00:00.000',2015) ,(3205111,'PER00009323','ORG00004619',1,'2014-06-25 00:00:00.000','2015-06-30 00:00:00.000','0011069',NULL,0,'PER00474964','2014-06-25 00:00:00.000',NULL,'2014-06-11 00:00:00.000',2015) ,(3205073,'PER00386833','ORG00004619',1,'2014-07-01 00:00:00.000','2015-06-30 00:00:00.000','0015112',NULL,0,'PER01084657','2014-06-25 13:54:02.000','PER01084657','2014-06-25 00:00:00.000',2015) ,(3205074,'PER00399881','ORG00004619',1,'2014-07-01 00:00:00.000','2015-03-25 00:00:00.000','0015112','ORG00000213',1,'PER01084657','2014-06-25 13:54:02.000','PER00470923','2014-06-25 00:00:00.000',2015) ,(3213330,'PER00147307','ORG00004619',1,'2014-06-30 00:00:00.000','2015-06-30 00:00:00.000',NULL,NULL,0,'PER00147307','2014-06-30 00:00:00.000',NULL,'2014-06-06 00:00:00.000',2015) ,(3205080,'PER00034123','ORG00004619',1,'2014-06-25 00:00:00.000','2015-06-30 00:00:00.000','0014744',NULL,0,'PER00474964','2014-06-25 00:00:00.000',NULL,'2014-06-24 00:00:00.000',2015) ,(3205082,'PER00334157','ORG00004619',1,'2014-06-25 00:00:00.000','2015-06-30 00:00:00.000','0014744',NULL,0,'PER00474964','2014-06-25 00:00:00.000',NULL,'2014-06-24 00:00:00.000',2015) ,(3205114,'PER01269327','ORG00004619',1,'2014-07-01 00:00:00.000','2015-06-30 00:00:00.000','0015141',NULL,0,'PER01084657','2014-06-25 14:06:21.000','PER01084657','2014-06-25 00:00:00.000',2015) ,(3213389,'PER01271204','ORG00004619',1,'2014-06-30 00:00:00.000','2015-06-30 00:00:00.000',NULL,NULL,0,'PER01271204','2014-06-30 00:00:00.000',NULL,'2014-06-04 00:00:00.000',2015) ,(3205113,'PER01271202','ORG00004619',1,'2014-07-01 00:00:00.000','2015-06-30 00:00:00.000','0015141',NULL,0,'PER01084657','2014-06-25 14:06:21.000','PER01084657','2014-06-25 00:00:00.000',2015) ,(3205072,'PER01271200','ORG00004619',1,'2014-07-01 00:00:00.000','2015-06-30 00:00:00.000','0015112',NULL,0,'PER01084657','2014-06-25 13:54:02.000','PER01084657','2014-06-25 00:00:00.000',2015) ,(3205075,'PER00818801','ORG00004619',2,'2014-07-01 00:00:00.000','2015-06-30 00:00:00.000','0015112',NULL,0,'PER01084657','2014-06-25 13:54:02.000','PER01084657','2014-06-25 00:00:00.000',2015) ,(3205077,'PER00392472','ORG00004619',1,'2014-07-01 00:00:00.000','2015-06-30 00:00:00.000','0015112',NULL,0,'PER01084657','2014-06-25 13:54:02.000','PER01084657','2014-06-25 00:00:00.000',2015);

bitsmed · August 19, 2015, 8:45pm

These are all enddate 2015 (not 2014 as you wrote earlier).

Just to be sure, is the query showing the result as you expected for all clubs/members?

techsupport15 · August 19, 2015, 9:01pm

Yes, I was seeing 2014 in one of the formulas and do not need last year.
Should be only this year and future years IF the Clubs do or does not have membership(s).

Yes I believe so. However the >=Year(GetDate()) did not work
when ran with 2015 it produces the correct info.
when i changed it to 2016 it produced what I need as well.

Can the formula be changed to show this year (2015) AND +4 years from this year?

bitsmed · August 19, 2015, 9:18pm

Ohh didn't see you wrote >= in the declare statement - no that wouldn't Work.

Leave the declare:

declare @season int=2015;

and change the join to:

       left outer join attribute.PersonMembership as p
                    on p.OrganizationId=c.ClubID
                   and p.EndDate>=@seasonstart
                   and p.MembershipTypeId in (1,2,3,4)

techsupport15 · August 19, 2015, 9:42pm

ok but what happens years from now having the 2015 set?

techsupport15 · August 19, 2015, 9:43pm

Also, prefer being able to see it on the panel like your other formula in the beginning.
Having the Declare, it does not allow you to see it in the panel view

bitsmed · August 19, 2015, 9:44pm

you can set it to (date will change every calendar year):

declare @season int=year(getdate());

or (date will change every Juli 1. = current season):

declare @season int=datediff(year,0,dateadd(month,(datediff(month,0,getdate()))+6,0))+1900;

techsupport15 · August 19, 2015, 10:09pm

Yes, tried the 1st one, which gives me 2015 and 2014 though?
then tried it with +1 for 2016, which gives me the 5305 with null membership (what I've been trying to get but included with the rest of the data output, so thank you for that!)

Will try that.
Need to see current year AND future years. why i was trying to do >=

Gotten a lot further for sure