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

techsupport15 · August 20, 2015, 11:30pm

Right - can't use startdate.
Yes on the start date is when the membership starts and i believe is inclusive of transfers.

EndYear could be a formulated result based on IF EndDate is between 7/1/2014-6/30/215 it's season 2015 and so on so the correct EndYear is reflecting the season?
Not sure how to do that though. haha

techsupport15 · August 20, 2015, 11:36pm

ok, though still can't figure out how your formula ends up with 91 invoice records for ClubNo 22 when there only exists 82?

It's still showing 92 records with 91 having invoices?? but only 82 invoice exists in personmembership for this organization

techsupport15 · August 20, 2015, 11:38pm

Oh and still can't get it to display on the Grid Pane.

These can't be saved as a View once it's figured out for it to link to the CRW for the actual reporting for the end user.

bitsmed · August 20, 2015, 11:40pm

Lets say a person has membershiptype 3 and startdate 2015-07-01. He/she would then have enddate 2019-06-30 and endyear would be 2019. If he transfers 2015-08-15, enddate would be overwritten with this date. but will endyear also be overwritten?

techsupport15 · August 20, 2015, 11:43pm

Ah, no, EndYear is a formula based on EndDate with Year(EndDate)

however it doesn't matter because the month and date of 6/30

I want to use the, now available on SQL Server Express 2012 or greater, to use
EndDate>=DateFromParts(Year(GetDate()),6,30)

which only captures 6/30/yyyy

bitsmed · August 20, 2015, 11:43pm

Please provide data (insert statements) for club 22 (only table personmembership)

techsupport15 · August 20, 2015, 11:44pm

1PER0005495900204462015-06-30 00:00:00.00020152014-07-18 00:00:00.000ORG000003801PER0005495900454702016-06-30 00:00:00.00020152015-07-01 00:00:00.000ORG000003801PER0029406200328602015-06-30 00:00:00.00020152014-10-30 00:00:00.000ORG000003801PER0038795300204462015-06-30 00:00:00.00020152014-07-18 00:00:00.000ORG000003801PER0038795300554242016-06-30 00:00:00.00020152015-07-16 00:00:00.000ORG000003801PER0038993900145682015-06-30 00:00:00.00020152014-07-01 00:00:00.000ORG000003801PER0038993900522482016-06-30 00:00:00.00020152015-07-01 00:00:00.000ORG000003801PER0040469600204462015-06-30 00:00:00.00020152014-07-18 00:00:00.000ORG000003801PER0040469600583512016-06-30 00:00:00.00020152015-08-06 00:00:00.000ORG000003801PER0040547700204462015-06-30 00:00:00.00020152014-07-18 00:00:00.000ORG000003801PER0040547700482462016-06-30 00:00:00.00020152015-07-01 00:00:00.000ORG000003801PER0050121800145682015-06-30 00:00:00.00020152014-07-01 00:00:00.000ORG000003801PER0050121800482462016-06-30 00:00:00.00020152015-07-01 00:00:00.000ORG000003801PER0072625400204462015-06-30 00:00:00.00020152014-07-18 00:00:00.000ORG000003801PER0072625400454702016-06-30 00:00:00.00020152015-07-01 00:00:00.000ORG000003801PER0080008100483132016-06-30 00:00:00.00020152015-07-01 00:00:00.000ORG000003801PER0091271900231672015-06-30 00:00:00.00020152014-08-13 00:00:00.000ORG000003801PER0091908900483132016-06-30 00:00:00.00020152015-07-01 00:00:00.000ORG000003801PER0092822200204462015-06-30 00:00:00.00020152014-07-18 00:00:00.000ORG000003801PER0092822200483132016-06-30 00:00:00.00020152015-07-01 00:00:00.000ORG000003801PER0095162300213452015-06-30 00:00:00.00020152014-07-27 00:00:00.000ORG000003801PER0095996200328602015-06-30 00:00:00.00020152014-10-30 00:00:00.000ORG000003801PER0095996200454702016-06-30 00:00:00.00020152015-07-01 00:00:00.000ORG000003801PER0095998600482462016-06-30 00:00:00.00020152015-07-01 00:00:00.000ORG000003801PER0101526900482462016-06-30 00:00:00.00020152015-07-01 00:00:00.000ORG000003801PER0101550700328602015-06-30 00:00:00.00020152014-10-30 00:00:00.000ORG000003801PER0101550700454702016-06-30 00:00:00.00020152015-07-01 00:00:00.000ORG000003801PER0103694600204462015-06-30 00:00:00.00020152014-07-18 00:00:00.000ORG000003801PER0103694600554242016-06-30 00:00:00.00020152015-07-16 00:00:00.000ORG000003801PER0108918600328602015-06-30 00:00:00.00020152014-10-30 00:00:00.000ORG000003801PER0108918600554242016-06-30 00:00:00.00020152015-07-16 00:00:00.000ORG000003801PER0118237600483132016-06-30 00:00:00.00020152015-07-01 00:00:00.000ORG000003801PER0121538100145682015-06-30 00:00:00.00020152014-07-01 00:00:00.000ORG000003801PER0122989100204462015-06-30 00:00:00.00020152014-07-18 00:00:00.000ORG000003801PER0125602000204462015-06-30 00:00:00.00020152014-07-18 00:00:00.000ORG000003801PER0125602000454702016-06-30 00:00:00.00020152015-07-01 00:00:00.000ORG000003801PER0125602100483132016-06-30 00:00:00.00020152015-07-01 00:00:00.000ORG000003801PER0126444100204462015-06-30 00:00:00.00020152014-07-18 00:00:00.000ORG000003801PER0128210200328602015-06-30 00:00:00.00020152014-10-30 00:00:00.000ORG000003801PER0128210200482462016-06-30 00:00:00.00020152015-07-01 00:00:00.000ORG000003801PER0133620400204462015-06-30 00:00:00.00020152014-07-18 00:00:00.000ORG000003801PER0135544200454702016-06-30 00:00:00.00020152015-07-01 00:00:00.000ORG000003802PER0080008100204462015-06-30 00:00:00.00020152014-07-18 00:00:00.000ORG000003802PER0095998600328602015-06-30 00:00:00.00020152014-10-30 00:00:00.000ORG000003802PER0101526600482462016-06-30 00:00:00.00020152015-07-01 00:00:00.000ORG000003802PER0108525900204462015-06-30 00:00:00.00020152014-07-18 00:00:00.000ORG000003802PER0108525900454702016-06-30 00:00:00.00020152015-07-01 00:00:00.000ORG000003802PER0108918500391302015-06-30 00:00:00.00020152015-02-05 00:00:00.000ORG000003802PER0108918500583512016-06-30 00:00:00.00020152015-08-06 00:00:00.000ORG000003802PER0110273500328602015-06-30 00:00:00.00020152014-10-30 00:00:00.000ORG000003802PER0111106600583512016-06-30 00:00:00.00020152015-08-06 00:00:00.000ORG000003802PER0118237600204462015-06-30 00:00:00.00020152014-07-18 00:00:00.000ORG000003802PER0118237800204462015-06-30 00:00:00.00020152014-07-18 00:00:00.000ORG000003802PER0118237800483132016-06-30 00:00:00.00020152015-07-01 00:00:00.000ORG000003802PER0120240000204462015-06-30 00:00:00.00020152014-07-18 00:00:00.000ORG000003802PER0126444600213452015-06-30 00:00:00.00020152014-07-27 00:00:00.000ORG000003802PER0126444800204462015-06-30 00:00:00.00020152014-07-18 00:00:00.000ORG000003802PER0126444800454702016-06-30 00:00:00.00020152015-07-01 00:00:00.000ORG000003802PER0128617900454702016-06-30 00:00:00.00020152015-07-01 00:00:00.000ORG000003802PER0133620400483132016-06-30 00:00:00.00020152015-07-01 00:00:00.000ORG000003802PER0134142300204462015-06-30 00:00:00.00020152014-07-18 00:00:00.000ORG000003802PER0134142300454702016-06-30 00:00:00.00020152015-07-01 00:00:00.000ORG000003802PER0141732900391262015-06-30 00:00:00.00020152015-02-05 00:00:00.000ORG000003802PER0141732900454702016-06-30 00:00:00.00020152015-07-01 00:00:00.000ORG000003802PER0150076600454702016-06-30 00:00:00.00020152015-07-01 00:00:00.000ORG000003802PER0150252100482462016-06-30 00:00:00.00020152015-07-01 00:00:00.000ORG000003802PER0150765300554242016-06-30 00:00:00.00020152015-07-16 00:00:00.000ORG000003803PER0005795800236242018-06-30 00:00:00.00020152014-08-18 00:00:00.000ORG000003803PER0021471500298282018-06-30 00:00:00.00020152014-09-28 00:00:00.000ORG000003803PER00294036Legacy2017-06-30 00:00:00.00020152013-07-01 00:00:00.000ORG000003803PER00495790Legacy2016-06-30 00:00:00.00020152012-07-01 00:00:00.000ORG000003804PER0100239600204462015-06-30 00:00:00.00020152014-07-18 00:00:00.000ORG000003804PER0101526900391282015-06-30 00:00:00.00020152015-02-05 00:00:00.000ORG000003804PER0102656300328602015-06-30 00:00:00.00020152014-10-30 00:00:00.000ORG000003804PER0108918800482462016-06-30 00:00:00.00020152015-07-01 00:00:00.000ORG000003804PER0110273100583512016-06-30 00:00:00.00020152015-08-06 00:00:00.000ORG000003804PER0111106600391302015-06-30 00:00:00.00020152015-02-05 00:00:00.000ORG000003804PER0112976000483132016-06-30 00:00:00.00020152015-07-01 00:00:00.000ORG000003804PER0125602100346442015-06-30 00:00:00.00020152014-11-22 00:00:00.000ORG000003804PER0125602300328602015-06-30 00:00:00.00020152014-10-30 00:00:00.000ORG000003804PER0135544200231672015-06-30 00:00:00.00020152014-08-13 00:00:00.000ORG000003804PER0143589100583512016-06-30 00:00:00.00020152015-08-06 00:00:00.000ORG00000380

techsupport15 · August 20, 2015, 11:46pm

GROUP BY dbo.PersonMembership.MembershipTypeId, dbo.PersonMembership.PersonId, dbo.PersonMembership.InvoiceNumber, dbo.PersonMembership.EndDate, dbo.PersonMembership.StartDate, dbo.PersonMembership.OrganizationId, dbo.v060ClubOfficersPresOrNot.ClubNo HAVING (dbo.PersonMembership.EndDate >= DateFromParts(YEAR(GETDATE()), 6, 30) OR dbo.PersonMembership.EndDate IS NULL) ORDER BY dbo.v060ClubOfficersPresOrNot.ClubNo, dbo.PersonMembership.PersonId, dbo.PersonMembership.InvoiceNumber)

bitsmed · August 20, 2015, 11:48pm

Please format as insert statement so I can load into my db to test.

techsupport15 · August 20, 2015, 11:49pm

Yea, i can't figure out how to format the results.
just know how to quote and add the code in that format but not table result.
how do you do that?

There are a total of 131 records with or without invoices with that simple formula.

Why i'm seeking help to get the records without "current" membership to show like 8004 is showing.

there are only two invoice records without a 6/30 enddate
1PER0126522200211582015-02-05 00:00:00.0002014-07-24 00:00:00.000ORG00000201161721PER0039988100151122015-03-25 00:00:00.0002014-07-01 00:00:00.000ORG0000461953053

bitsmed · August 20, 2015, 11:56pm

Do the same as you did, when you posted the person data for clubno 5305.

techsupport15 · August 20, 2015, 11:59pm

I am going to try and use the Import and Export function from SQL Server 2014

I've tried to paste DDL but it's too long and won't allow me to do it

ugh, i still can't upload anything plus looks like i'm limited to images only

techsupport15 · August 21, 2015, 12:11am

INSERT INTO PersonMembership(Id,PersonId,OrganizationId,MembershipTypeId,StartDate,EndDate,InvoiceNumber,XfrRequestedToClubId,IsReleased,CreatedById,CreatedDate,ModifiedById,PaymentDate,EndYear) VALUES
 (2909193,'PER00633872','ORG00000082',3,'2012-07-01 00:00:00.000','2016-06-30 00:00:00.000','Legacy',NULL,0,'PER01175838','2012-09-10 16:56:28.000','PER01175838','2012-09-10 00:00:00.000',2016)
,(3427888,'PER01507653','ORG00000380',2,'2015-07-16 00:00:00.000','2016-06-30 00:00:00.000','0055424',NULL,0,'PER01341423','2015-07-16 13:05:43.000','PER01341423','2015-07-16 00:00:00.000',2016)
,(3074905,'PER00294036','ORG00000380',3,'2013-07-01 00:00:00.000','2017-06-30 00:00:00.000','Legacy',NULL,0,'PER01340061','2013-09-13 13:57:50.000','PER01340061','2013-08-30 00:00:00.000',2017)
,(3234598,'PER01336204','ORG00000380',1,'2014-07-18 00:00:00.000','2015-06-30 00:00:00.000','0020446',NULL,0,'PER01085259','2014-07-18 16:25:30.000','PER01085259','2014-07-19 00:00:00.000',2015)
,(3234609,'PER01002396','ORG00000380',4,'2014-07-18 00:00:00.000','2015-06-30 00:00:00.000','0020446',NULL,0,'PER01085259','2014-07-18 16:25:31.000','PER01085259','2014-07-19 00:00:00.000',2015)
,(3237642,'PER00026045','ORG00000201',1,'2014-07-24 00:00:00.000','2015-06-30 00:00:00.000','0021158',NULL,0,'PER00404665','2014-07-24 16:14:31.000','PER00404665','2014-07-24 00:00:00.000',2015)
,(3202383,'PER01215381','ORG00000380',1,'2014-07-01 00:00:00.000','2015-06-30 00:00:00.000','0014568',NULL,0,'PER01085259','2014-06-24 10:09:09.000','PER01085259','2014-06-24 00:00:00.000',2015)
,(3202384,'PER00501218','ORG00000380',1,'2014-07-01 00:00:00.000','2015-06-30 00:00:00.000','0014568',NULL,0,'PER01085259','2014-06-24 10:09:09.000','PER01085259','2014-06-24 00:00:00.000',2015)
,(3234603,'PER00928222','ORG00000380',1,'2014-07-18 00:00:00.000','2015-06-30 00:00:00.000','0020446',NULL,0,'PER01085259','2014-07-18 16:25:31.000','PER01085259','2014-07-19 00:00:00.000',2015)
,(3234604,'PER01202400','ORG00000380',2,'2014-07-18 00:00:00.000','2015-06-30 00:00:00.000','0020446',NULL,0,'PER01085259','2014-07-18 16:25:31.000','PER01085259','2014-07-19 00:00:00.000',2015)
,(3234594,'PER01085259','ORG00000380',2,'2014-07-18 00:00:00.000','2015-06-30 00:00:00.000','0020446',NULL,0,'PER01085259','2014-07-18 16:25:30.000','PER01085259','2014-07-19 00:00:00.000',2015)
,(3234596,'PER01229891','ORG00000380',1,'2014-07-18 00:00:00.000','2015-06-30 00:00:00.000','0020446',NULL,0,'PER01085259','2014-07-18 16:25:30.000','PER01085259','2014-07-19 00:00:00.000',2015)
,(3234595,'PER00054959','ORG00000380',1,'2014-07-18 00:00:00.000','2015-06-30 00:00:00.000','0020446',NULL,0,'PER01085259','2014-07-18 16:25:30.000','PER01085259','2014-07-19 00:00:00.000',2015)
,(3234597,'PER01182376','ORG00000380',2,'2014-07-18 00:00:00.000','2015-06-30 00:00:00.000','0020446',NULL,0,'PER01085259','2014-07-18 16:25:30.000','PER01085259','2014-07-19 00:00:00.000',2015)
,(3202382,'PER00389939','ORG00000380',1,'2014-07-01 00:00:00.000','2015-06-30 00:00:00.000','0014568',NULL,0,'PER01085259','2014-06-24 10:09:09.000','PER01085259','2014-06-24 00:00:00.000',2015)
,(3234602,'PER00387953','ORG00000380',1,'2014-07-18 00:00:00.000','2015-06-30 00:00:00.000','0020446',NULL,0,'PER01085259','2014-07-18 16:25:30.000','PER01085259','2014-07-19 00:00:00.000',2015)
,(3234605,'PER01256020','ORG00000380',1,'2014-07-18 00:00:00.000','2015-06-30 00:00:00.000','0020446',NULL,0,'PER01085259','2014-07-18 16:25:31.000','PER01085259','2014-07-19 00:00:00.000',2015)
,(3234606,'PER01341423','ORG00000380',2,'2014-07-18 00:00:00.000','2015-06-30 00:00:00.000','0020446',NULL,0,'PER01085259','2014-07-18 16:25:31.000','PER01085259','2014-07-19 00:00:00.000',2015)
,(3234599,'PER00405477','ORG00000380',1,'2014-07-18 00:00:00.000','2015-06-30 00:00:00.000','0020446',NULL,0,'PER01085259','2014-07-18 16:25:30.000','PER01085259','2014-07-19 00:00:00.000',2015)
,(3234601,'PER01264441','ORG00000380',1,'2014-07-18 00:00:00.000','2015-06-30 00:00:00.000','0020446',NULL,0,'PER01085259','2014-07-18 16:25:30.000','PER01085259','2014-07-19 00:00:00.000',2015)
,(3234608,'PER01264448','ORG00000380',2,'2014-07-18 00:00:00.000','2015-06-30 00:00:00.000','0020446',NULL,0,'PER01085259','2014-07-18 16:25:31.000','PER01085259','2014-07-19 00:00:00.000',2015)
,(3234610,'PER00404696','ORG00000380',1,'2014-07-18 00:00:00.000','2015-06-30 00:00:00.000','0020446',NULL,0,'PER01085259','2014-07-18 16:25:31.000','PER01085259','2014-07-19 00:00:00.000',2015)
,(3237641,'PER01265222','ORG00000201',1,'2014-07-24 00:00:00.000','2015-02-05 00:00:00.000','0021158','ORG00001458',1,'PER00404665','2014-07-24 16:14:31.000','PER00938933','2014-07-24 00:00:00.000',2015)
,(3237643,'PER01099711','ORG00000201',1,'2014-07-24 00:00:00.000','2015-06-30 00:00:00.000','0021158',NULL,0,'PER00404665','2014-07-24 16:14:31.000','PER00404665','2014-07-24 00:00:00.000',2015)
,(3238350,'PER00951623','ORG00000380',1,'2014-07-27 00:00:00.000','2015-06-30 00:00:00.000','0021345',NULL,0,'PER01085259','2014-07-27 09:33:10.000','PER01085259','2014-07-27 00:00:00.000',2015)
,(2887323,'PER00495790','ORG00000380',3,'2012-07-01 00:00:00.000','2016-06-30 00:00:00.000','Legacy',NULL,0,'PER01085259','2012-07-15 16:32:19.000','PER01085259','2012-07-15 00:00:00.000',2016)
,(3234593,'PER00726254','ORG00000380',1,'2014-07-18 00:00:00.000','2015-06-30 00:00:00.000','0020446',NULL,0,'PER01085259','2014-07-18 16:25:30.000','PER01085259','2014-07-19 00:00:00.000',2015)
,(3234600,'PER00800081','ORG00000380',2,'2014-07-18 00:00:00.000','2015-06-30 00:00:00.000','0020446',NULL,0,'PER01085259','2014-07-18 16:25:30.000','PER01085259','2014-07-19 00:00:00.000',2015)
,(3234607,'PER01182378','ORG00000380',2,'2014-07-18 00:00:00.000','2015-06-30 00:00:00.000','0020446',NULL,0,'PER01085259','2014-07-18 16:25:31.000','PER01085259','2014-07-19 00:00:00.000',2015)
,(3238351,'PER01264446','ORG00000380',2,'2014-07-27 00:00:00.000','2015-06-30 00:00:00.000','0021345',NULL,0,'PER01085259','2014-07-27 09:33:10.000','PER01085259','2014-07-27 00:00:00.000',2015)
,(3245603,'PER00912719','ORG00000380',1,'2014-08-13 00:00:00.000','2015-06-30 00:00:00.000','0023167',NULL,0,'PER01085259','2014-08-13 17:37:13.000','PER01085259','2014-08-14 00:00:00.000',2015)
,(3273706,'PER01280879','ORG00000201',1,'2014-10-10 00:00:00.000','2015-06-30 00:00:00.000','0030959',NULL,0,'PER00404665','2014-10-10 21:17:34.000','PER00404665','2014-10-10 00:00:00.000',2015)
,(3273717,'PER01091933','ORG00000201',1,'2014-10-10 00:00:00.000','2015-06-30 00:00:00.000','0030959',NULL,0,'PER00404665','2014-10-10 21:17:36.000','PER00404665','2014-10-10 00:00:00.000',2015)
,(3273724,'PER01433760','ORG00000201',2,'2014-10-10 00:00:00.000','2015-06-30 00:00:00.000','0030959',NULL,0,'PER00404665','2014-10-10 21:17:37.000','PER00404665','2014-10-10 00:00:00.000',2015)
,(3283452,'PER00294062','ORG00000380',1,'2014-10-30 00:00:00.000','2015-06-30 00:00:00.000','0032860',NULL,0,'PER01085259','2014-10-30 18:38:58.000','PER01085259','2014-10-31 00:00:00.000',2015)
,(3380934,'PER01355442','ORG00000380',1,'2015-07-01 00:00:00.000','2016-06-30 00:00:00.000','0045470',NULL,0,'PER01085259','2015-06-06 05:45:03.000','PER01085259','2015-06-06 00:00:00.000',2016)
,(3392073,'PER00959986','ORG00000380',1,'2015-07-01 00:00:00.000','2016-06-30 00:00:00.000','0048246',NULL,0,'PER01341423','2015-06-17 14:55:42.000','PER01341423','2015-06-17 00:00:00.000',2016)
,(3392541,'PER01256021','ORG00000380',1,'2015-07-01 00:00:00.000','2016-06-30 00:00:00.000','0048313',NULL,0,'PER01341423','2015-06-17 18:43:20.000','PER01341423','2015-06-18 00:00:00.000',2016)
,(3392548,'PER01129760','ORG00000380',4,'2015-07-01 00:00:00.000','2016-06-30 00:00:00.000','0048313',NULL,0,'PER01341423','2015-06-17 18:43:21.000','PER01341423','2015-06-18 00:00:00.000',2016)
,(3237644,'PER00404665','ORG00000201',1,'2014-07-24 00:00:00.000','2015-06-30 00:00:00.000','0021158',NULL,0,'PER00404665','2014-07-24 16:14:31.000','PER00404665','2014-07-24 00:00:00.000',2015)
,(3237647,'PER01304940','ORG00000201',4,'2014-07-24 00:00:00.000','2015-06-30 00:00:00.000','0021158',NULL,0,'PER00404665','2014-07-24 16:14:31.000','PER00404665','2014-07-24 00:00:00.000',2015)
,(3273711,'PER00566478','ORG00000201',1,'2014-10-10 00:00:00.000','2015-06-30 00:00:00.000','0030959',NULL,0,'PER00404665','2014-10-10 21:17:35.000','PER00404665','2014-10-10 00:00:00.000',2015)
,(3273712,'PER01062102','ORG00000201',1,'2014-10-10 00:00:00.000','2015-06-30 00:00:00.000','0030959',NULL,0,'PER00404665','2014-10-10 21:17:35.000','PER00404665','2014-10-10 00:00:00.000',2015)
,(3283453,'PER00959986','ORG00000380',2,'2014-10-30 00:00:00.000','2015-06-30 00:00:00.000','0032860',NULL,0,'PER01085259','2014-10-30 18:38:58.000','PER01085259','2014-10-31 00:00:00.000',2015)
,(3283454,'PER01282102','ORG00000380',1,'2014-10-30 00:00:00.000','2015-06-30 00:00:00.000','0032860',NULL,0,'PER01085259','2014-10-30 18:38:58.000','PER01085259','2014-10-31 00:00:00.000',2015)
,(3380939,'PER01417329','ORG00000380',2,'2015-07-01 00:00:00.000','2016-06-30 00:00:00.000','0045470',NULL,0,'PER01085259','2015-06-06 05:45:03.000','PER01085259','2015-06-06 00:00:00.000',2016)
,(3380940,'PER01500766','ORG00000380',2,'2015-07-01 00:00:00.000','2016-06-30 00:00:00.000','0045470',NULL,0,'PER01085259','2015-06-06 05:45:04.000','PER01085259','2015-06-06 00:00:00.000',2016)
,(3273705,'PER01351981','ORG00000201',2,'2014-10-10 00:00:00.000','2015-06-30 00:00:00.000','0030959',NULL,0,'PER00404665','2014-10-10 21:17:34.000','PER00404665','2014-10-10 00:00:00.000',2015)
,(3273707,'PER01280881','ORG00000201',2,'2014-10-10 00:00:00.000','2015-06-30 00:00:00.000','0030959',NULL,0,'PER00404665','2014-10-10 21:17:34.000','PER00404665','2014-10-10 00:00:00.000',2015)
,(3273709,'PER00915271','ORG00000201',2,'2014-10-10 00:00:00.000','2015-06-30 00:00:00.000','0030959',NULL,0,'PER00404665','2014-10-10 21:17:34.000','PER00404665','2014-10-10 00:00:00.000',2015)
,(3273714,'PER00651985','ORG00000201',1,'2014-10-10 00:00:00.000','2015-06-30 00:00:00.000','0030959',NULL,0,'PER00404665','2014-10-10 21:17:35.000','PER00404665','2014-10-10 00:00:00.000',2015)
,(3273716,'PER01351982','ORG00000201',2,'2014-10-10 00:00:00.000','2015-06-30 00:00:00.000','0030959',NULL,0,'PER00404665','2014-10-10 21:17:36.000','PER00404665','2014-10-10 00:00:00.000',2015)
,(3380933,'PER01264448','ORG00000380',2,'2015-07-01 00:00:00.000','2016-06-30 00:00:00.000','0045470',NULL,0,'PER01085259','2015-06-06 05:45:03.000','PER01085259','2015-06-06 00:00:00.000',2016)
,(3380935,'PER00054959','ORG00000380',1,'2015-07-01 00:00:00.000','2016-06-30 00:00:00.000','0045470',NULL,0,'PER01085259','2015-06-06 05:45:03.000','PER01085259','2015-06-06 00:00:00.000',2016)
,(3392072,'PER01282102','ORG00000380',1,'2015-07-01 00:00:00.000','2016-06-30 00:00:00.000','0048246',NULL,0,'PER01341423','2015-06-17 14:55:42.000','PER01341423','2015-06-17 00:00:00.000',2016)
,(3392074,'PER00405477','ORG00000380',1,'2015-07-01 00:00:00.000','2016-06-30 00:00:00.000','0048246',NULL,0,'PER01341423','2015-06-17 14:55:42.000','PER01341423','2015-06-17 00:00:00.000',2016)
,(3392542,'PER00800081','ORG00000380',1,'2015-07-01 00:00:00.000','2016-06-30 00:00:00.000','0048313',NULL,0,'PER01341423','2015-06-17 18:43:20.000','PER01341423','2015-06-18 00:00:00.000',2016)
,(3237645,'PER00647280','ORG00000201',2,'2014-07-24 00:00:00.000','2015-06-30 00:00:00.000','0021158',NULL,0,'PER00404665','2014-07-24 16:14:31.000','PER00404665','2014-07-24 00:00:00.000',2015)
,(3237646,'PER01201935','ORG00000201',2,'2014-07-24 00:00:00.000','2015-06-30 00:00:00.000','0021158',NULL,0,'PER00404665','2014-07-24 16:14:31.000','PER00404665','2014-07-24 00:00:00.000',2015)
,(3244998,'PER00569564','ORG00000198',1,'2014-08-11 00:00:00.000','2015-06-30 00:00:00.000','0022905',NULL,0,'PER01336186','2014-08-11 17:27:49.000','PER01336186','2014-08-11 00:00:00.000',2015)
,(3245602,'PER01355442','ORG00000380',4,'2014-08-13 00:00:00.000','2015-06-30 00:00:00.000','0023167',NULL,0,'PER01085259','2014-08-13 17:37:13.000','PER01085259','2014-08-14 00:00:00.000',2015)
,(3255629,'PER01018383','ORG00000198',1,'2014-09-11 00:00:00.000','2015-06-30 00:00:00.000','0028305',NULL,0,'PER01336186','2014-09-11 15:18:54.000','PER01336186','2014-09-11 00:00:00.000',2015)
,(3265957,'PER01211208','ORG00000198',1,'2014-09-29 00:00:00.000','2015-06-30 00:00:00.000','0030019',NULL,0,'PER01336186','2014-09-29 19:55:26.000','PER01336186','2014-09-29 00:00:00.000',2015)
,(3265958,'PER00923423','ORG00000198',1,'2014-09-29 00:00:00.000','2015-06-30 00:00:00.000','0030019',NULL,0,'PER01336186','2014-09-29 19:55:26.000','PER01336186','2014-09-29 00:00:00.000',2015)
,(3265972,'PER01353975','ORG00000198',1,'2014-09-29 00:00:00.000','2015-06-30 00:00:00.000','0030019',NULL,0,'PER01336186','2014-09-29 19:55:28.000','PER01336186','2014-09-29 00:00:00.000',2015)
,(3273710,'PER00280973','ORG00000201',2,'2014-10-10 00:00:00.000','2015-06-30 00:00:00.000','0030959',NULL,0,'PER00404665','2014-10-10 21:17:34.000','PER00404665','2014-10-10 00:00:00.000',2015)
,(3273713,'PER01280918','ORG00000201',2,'2014-10-10 00:00:00.000','2015-06-30 00:00:00.000','0030959',NULL,0,'PER00404665','2014-10-10 21:17:35.000','PER00404665','2014-10-10 00:00:00.000',2015)
,(3283455,'PER01026563','ORG00000380',4,'2014-10-30 00:00:00.000','2015-06-30 00:00:00.000','0032860',NULL,0,'PER01085259','2014-10-30 18:38:58.000','PER01085259','2014-10-31 00:00:00.000',2015)
,(3283456,'PER01256023','ORG00000380',4,'2014-10-30 00:00:00.000','2015-06-30 00:00:00.000','0032860',NULL,0,'PER01085259','2014-10-30 18:38:59.000','PER01085259','2014-10-31 00:00:00.000',2015)
,(3333459,'PER01417329','ORG00000380',2,'2015-02-05 00:00:00.000','2015-06-30 00:00:00.000','0039126',NULL,0,'PER01085259','2015-02-05 18:36:19.000','PER01085259','2015-02-06 00:00:00.000',2015)
,(3333460,'PER01015269','ORG00000380',4,'2015-02-05 00:00:00.000','2015-06-30 00:00:00.000','0039128',NULL,0,'PER01085259','2015-02-05 18:40:50.000','PER01085259','2015-02-06 00:00:00.000',2015)
,(3380937,'PER01286179','ORG00000380',2,'2015-07-01 00:00:00.000','2016-06-30 00:00:00.000','0045470',NULL,0,'PER01085259','2015-06-06 05:45:03.000','PER01085259','2015-06-06 00:00:00.000',2016)
,(3380938,'PER01341423','ORG00000380',2,'2015-07-01 00:00:00.000','2016-06-30 00:00:00.000','0045470',NULL,0,'PER01085259','2015-06-06 05:45:03.000','PER01085259','2015-06-06 00:00:00.000',2016)
,(3265109,'PER00214715','ORG00000380',3,'2014-09-28 00:00:00.000','2018-06-30 00:00:00.000','0029828',NULL,0,'PER01085259','2014-09-28 13:21:11.000','PER01085259','2014-09-28 00:00:00.000',2018)
,(3273708,'PER00647282','ORG00000201',1,'2014-10-10 00:00:00.000','2015-06-30 00:00:00.000','0030959',NULL,0,'PER00404665','2014-10-10 21:17:34.000','PER00404665','2014-10-10 00:00:00.000',2015)
,(3273715,'PER00842058','ORG00000201',1,'2014-10-10 00:00:00.000','2015-06-30 00:00:00.000','0030959',NULL,0,'PER00404665','2014-10-10 21:17:35.000','PER00404665','2014-10-10 00:00:00.000',2015)
,(3273722,'PER01373934','ORG00000201',1,'2014-10-10 00:00:00.000','2015-06-30 00:00:00.000','0030959',NULL,0,'PER00404665','2014-10-10 21:17:37.000','PER00404665','2014-10-10 00:00:00.000',2015)
,(3283450,'PER00959962','ORG00000380',1,'2014-10-30 00:00:00.000','2015-06-30 00:00:00.000','0032860',NULL,0,'PER01085259','2014-10-30 18:38:58.000','PER01085259','2014-10-31 00:00:00.000',2015)
,(3298644,'PER01256021','ORG00000380',4,'2014-11-22 00:00:00.000','2015-06-30 00:00:00.000','0034644',NULL,0,'PER01085259','2014-11-22 13:39:12.000','PER01085259','2014-11-22 00:00:00.000',2015)
,(3380936,'PER00959962','ORG00000380',1,'2015-07-01 00:00:00.000','2016-06-30 00:00:00.000','0045470',NULL,0,'PER01085259','2015-06-06 05:45:03.000','PER01085259','2015-06-06 00:00:00.000',2016)
,(3392075,'PER00501218','ORG00000380',1,'2015-07-01 00:00:00.000','2016-06-30 00:00:00.000','0048246',NULL,0,'PER01341423','2015-06-17 14:55:42.000','PER01341423','2015-06-17 00:00:00.000',2016)
,(3392543,'PER00928222','ORG00000380',1,'2015-07-01 00:00:00.000','2016-06-30 00:00:00.000','0048313',NULL,0,'PER01341423','2015-06-17 18:43:20.000','PER01341423','2015-06-18 00:00:00.000',2016)
,(3273703,'PER01096476','ORG00000201',1,'2014-10-10 00:00:00.000','2015-06-30 00:00:00.000','0030959',NULL,0,'PER00404665','2014-10-10 21:17:34.000','PER00404665','2014-10-10 00:00:00.000',2015)
,(3273718,'PER00053268','ORG00000201',1,'2014-10-10 00:00:00.000','2015-06-30 00:00:00.000','0030959',NULL,0,'PER00404665','2014-10-10 21:17:36.000','PER00404665','2014-10-10 00:00:00.000',2015)
,(3283448,'PER01089186','ORG00000380',1,'2014-10-30 00:00:00.000','2015-06-30 00:00:00.000','0032860',NULL,0,'PER01085259','2014-10-30 18:38:58.000','PER01085259','2014-10-31 00:00:00.000',2015)
,(3333465,'PER01111066','ORG00000380',4,'2015-02-05 00:00:00.000','2015-06-30 00:00:00.000','0039130',NULL,0,'PER01085259','2015-02-05 18:46:07.000','PER01085259','2015-02-06 00:00:00.000',2015)
,(3380929,'PER00726254','ORG00000380',1,'2015-07-01 00:00:00.000','2016-06-30 00:00:00.000','0045470',NULL,0,'PER01085259','2015-06-06 05:45:02.000','PER01085259','2015-06-06 00:00:00.000',2016)
,(3380931,'PER01085259','ORG00000380',2,'2015-07-01 00:00:00.000','2016-06-30 00:00:00.000','0045470',NULL,0,'PER01085259','2015-06-06 05:45:02.000','PER01085259','2015-06-06 00:00:00.000',2016)
,(3392076,'PER01015269','ORG00000380',1,'2015-07-01 00:00:00.000','2016-06-30 00:00:00.000','0048246',NULL,0,'PER01341423','2015-06-17 14:55:42.000','PER01341423','2015-06-17 00:00:00.000',2016)
,(3392078,'PER01015266','ORG00000380',2,'2015-07-01 00:00:00.000','2016-06-30 00:00:00.000','0048246',NULL,0,'PER01341423','2015-06-17 14:55:43.000','PER01341423','2015-06-17 00:00:00.000',2016)
,(3392544,'PER01182376','ORG00000380',1,'2015-07-01 00:00:00.000','2016-06-30 00:00:00.000','0048313',NULL,0,'PER01341423','2015-06-17 18:43:20.000','PER01341423','2015-06-18 00:00:00.000',2016)
,(3392546,'PER01336204','ORG00000380',2,'2015-07-01 00:00:00.000','2016-06-30 00:00:00.000','0048313',NULL,0,'PER01341423','2015-06-17 18:43:21.000','PER01341423','2015-06-18 00:00:00.000',2016)
,(3273704,'PER01280871','ORG00000201',1,'2014-10-10 00:00:00.000','2015-06-30 00:00:00.000','0030959',NULL,0,'PER00404665','2014-10-10 21:17:34.000','PER00404665','2014-10-10 00:00:00.000',2015)
,(3273719,'PER00558085','ORG00000201',1,'2014-10-10 00:00:00.000','2015-06-30 00:00:00.000','0030959',NULL,0,'PER00404665','2014-10-10 21:17:36.000','PER00404665','2014-10-10 00:00:00.000',2015)
,(3333466,'PER01089185','ORG00000380',2,'2015-02-05 00:00:00.000','2015-06-30 00:00:00.000','0039130',NULL,0,'PER01085259','2015-02-05 18:46:07.000','PER01085259','2015-02-06 00:00:00.000',2015)
,(3380930,'PER01015507','ORG00000380',1,'2015-07-01 00:00:00.000','2016-06-30 00:00:00.000','0045470',NULL,0,'PER01085259','2015-06-06 05:45:02.000','PER01085259','2015-06-06 00:00:00.000',2016)
,(3380932,'PER01256020','ORG00000380',1,'2015-07-01 00:00:00.000','2016-06-30 00:00:00.000','0045470',NULL,0,'PER01085259','2015-06-06 05:45:03.000','PER01085259','2015-06-06 00:00:00.000',2016)
,(3392077,'PER01089188','ORG00000380',4,'2015-07-01 00:00:00.000','2016-06-30 00:00:00.000','0048246',NULL,0,'PER01341423','2015-06-17 14:55:43.000','PER01341423','2015-06-17 00:00:00.000',2016)
,(3392079,'PER01502521','ORG00000380',2,'2015-07-01 00:00:00.000','2016-06-30 00:00:00.000','0048246',NULL,0,'PER01341423','2015-06-17 14:55:43.000','PER01341423','2015-06-17 00:00:00.000',2016)
,(3392545,'PER01182378','ORG00000380',2,'2015-07-01 00:00:00.000','2016-06-30 00:00:00.000','0048313',NULL,0,'PER01341423','2015-06-17 18:43:20.000','PER01341423','2015-06-18 00:00:00.000',2016)
,(3392547,'PER00919089','ORG00000380',1,'2015-07-01 00:00:00.000','2016-06-30 00:00:00.000','0048313',NULL,0,'PER01341423','2015-06-17 18:43:21.000','PER01341423','2015-06-18 00:00:00.000',2016)
,(3427890,'PER01089186','ORG00000380',1,'2015-07-16 00:00:00.000','2016-06-30 00:00:00.000','0055424',NULL,0,'PER01341423','2015-07-16 13:05:44.000','PER01341423','2015-07-16 00:00:00.000',2016)
,(3431459,'PER00647280','ORG00000201',2,'2015-07-21 00:00:00.000','2016-06-30 00:00:00.000','0056033',NULL,0,'PER00404665','2015-07-21 21:40:23.000','PER00404665','2015-07-21 00:00:00.000',2016)
,(3431460,'PER00647282','ORG00000201',1,'2015-07-21 00:00:00.000','2016-06-30 00:00:00.000','0056033',NULL,0,'PER00404665','2015-07-21 21:40:23.000','PER00404665','2015-07-21 00:00:00.000',2016)
,(3439065,'PER01102731','ORG00000380',4,'2015-08-06 00:00:00.000','2016-06-30 00:00:00.000','0058351',NULL,0,'PER01341423','2015-08-06 11:20:19.000','PER01341423','2015-08-06 00:00:00.000',2016)
,(3439066,'PER01435891','ORG00000380',4,'2015-08-06 00:00:00.000','2016-06-30 00:00:00.000','0058351',NULL,0,'PER01341423','2015-08-06 11:20:19.000','PER01341423','2015-08-06 00:00:00.000',2016)
,(3431461,'PER01201935','ORG00000201',2,'2015-07-21 00:00:00.000','2016-06-30 00:00:00.000','0056033',NULL,0,'PER00404665','2015-07-21 21:40:23.000','PER00404665','2015-07-21 00:00:00.000',2016)
,(3431462,'PER00651985','ORG00000201',1,'2015-07-21 00:00:00.000','2016-06-30 00:00:00.000','0056033',NULL,0,'PER00404665','2015-07-21 21:40:24.000','PER00404665','2015-07-21 00:00:00.000',2016)
,(3439063,'PER01111066','ORG00000380',2,'2015-08-06 00:00:00.000','2016-06-30 00:00:00.000','0058351',NULL,0,'PER01341423','2015-08-06 11:20:18.000','PER01341423','2015-08-06 00:00:00.000',2016)
,(3439064,'PER00404696','ORG00000380',1,'2015-08-06 00:00:00.000','2016-06-30 00:00:00.000','0058351',NULL,0,'PER01341423','2015-08-06 11:20:18.000','PER01341423','2015-08-06 00:00:00.000',2016)
,(3409389,'PER00389939','ORG00000380',1,'2015-07-01 00:00:00.000','2016-06-30 00:00:00.000','0052248',NULL,0,'PER01341423','2015-06-30 05:31:47.000','PER01341423','2015-06-30 00:00:00.000',2016)
,(3427887,'PER01036946','ORG00000380',1,'2015-07-16 00:00:00.000','2016-06-30 00:00:00.000','0055424',NULL,0,'PER01341423','2015-07-16 13:05:42.000','PER01341423','2015-07-16 00:00:00.000',2016)
,(3431456,'PER01304940','ORG00000201',1,'2015-07-21 00:00:00.000','2016-06-30 00:00:00.000','0056033',NULL,0,'PER00404665','2015-07-21 21:40:22.000','PER00404665','2015-07-21 00:00:00.000',2016)
,(3439062,'PER01089185','ORG00000380',2,'2015-08-06 00:00:00.000','2016-06-30 00:00:00.000','0058351',NULL,0,'PER01341423','2015-08-06 11:20:17.000','PER01341423','2015-08-06 00:00:00.000',2016)

techsupport15 · August 21, 2015, 3:45am

Ok, so I got this, which works.
Now can you help change it so that it can be saved as a View.
And perhaps utilize EndDate>=DateFromParts(Year(GetDate()),6,30) instead of the multiple date declarations and still produce the same results?

[code]
DECLARE @YEAR AS char(4) = DateFromParts(Year(GetDate()),6,30);
DECLARE @START_DATE AS date = CAST(@YEAR + '-06-30' AS date);
DECLARE @END_DATE1 AS date = DATEADD(year, 1, @START_DATE);
DECLARE @END_DATE2 AS date = DATEADD(year, 2, @START_DATE);
DECLARE @END_DATE3 AS date = DATEADD(year, 3, @START_DATE);
DECLARE @END_DATE4 AS date = DATEADD(year, 4, @START_DATE);

WITH CLUBS AS (

SELECT DISTINCT ClubID, ClubNo, SortName, ClubName, BSProgram, ClubSection, Code,
    President, Email, Phone, FacilityName, StreetOne, StreetTwo, City, [State],
    PostalCode, URL, Certified, FacilityLastUpdate, ByLawsUploadDate, ProgramStatusId,
    [Status]
FROM dbo.v060ClubOfficersPresOrNot

),
YEARS AS (

    SELECT CAST(@YEAR AS int) AS THE_YEAR
    UNION ALL
    SELECT THE_YEAR + 1
    FROM YEARS
    WHERE THE_YEAR + 1 < CAST(@YEAR AS int) + 5

),
Memberships AS (

    SELECT OrganizationId, PersonID, InvoiceNumber, EndYear, MembershipTypeId
    FROM dbo.PersonMembership
    WHERE EndDate IN (@START_DATE, @END_DATE1,
        CASE WHEN MembershipTypeId = 3 THEN @END_DATE2 END,
        CASE WHEN MembershipTypeId = 3 THEN @END_DATE3 END,
        CASE WHEN MembershipTypeId = 3 THEN @END_DATE4 END)

)
SELECT COP.ClubNo, COP.SortName, COP.ClubName, COP.BSProgram, COP.ClubSection, COP.Code,
RTRIM(PM.InvoiceNumber) AS InvNo, COP.President, COP.Email, COP.Phone, COP.FacilityName,
COP.StreetOne, COP.StreetTwo, COP.City, COP.[State], COP.PostalCode,
COP.URL, COP.Certified, COP.FacilityLastUpdate, COP.ByLawsUploadDate, COP.ProgramStatusId, COP.[Status], PM.MembershipTypeId, PM.PersonId, PM.InvoiceNumber, Y.THE_YEAR AS YearEnd
FROM YEARS AS Y
CROSS JOIN CLUBS AS COP
LEFT OUTER JOIN Memberships AS PM
ON COP.ClubID = PM.OrganizationId
AND Y.THE_YEAR = PM.EndYear
ORDER BY COP.ClubNo, Y.THE_YEAR, PM.PersonId, RTRIM(PM.InvoiceNumber), PM.MembershipTypeId;[/code]

Kristen · August 21, 2015, 12:25pm

I strongly recommend that you use "yyyymmdd" for string-to-date conversion, and not anything with punctuation. SQL treats "yyyymmdd" as unambiguous - if it sees 8 digits it ALWAYS assumed "yyyymmdd" format, whereas for anything else it uses the Locale of the server (which might not change ... but it might!) and then the Language of the currently connected user and so on - all sorts that could go wrong / change over time in that direction!!

I would declare @YEAR as INT (and CAST it for your string manipulation) in order that there is NOT Cast in the FROM clause - that might make the FROM section more likely to be SARGable and, if so (might be a long shot!) it will perform better.

Might not be something to concern you, but we always try to program-out the use of DISTINCT in a SELECT statement - i.e. to avoid SQL having to Sort, and then De-DUPE the results. Sometime the JOINs can be sufficiently complex as to make that tricky to achieve though ...

techsupport15 · August 21, 2015, 3:39pm

I apologize, the sample PersonMembership table I provided consisted of the derived EndYear from Year(EndDate).

Unfortunately I'm unsure as to what to change to derive it in this code. Could you help with that?

There are a few and though it allowed me to do this here:

but not here:FROM dbo.PersonMembership AS P INNER JOIN YEARS AS Y ON P.EndYear = Y.THE_YEAR AND P.EndDate = Y.END_DATE)

FROM YEARS AS Y CROSS JOIN CLUBS AS co LEFT OUTER JOIN Memberships AS pm ON co.ClubID = pm.OrganizationId AND Y.THE_YEAR = pm.EndYear

Kristen · August 21, 2015, 5:01pm

Sorry, I'm probably being thick, but I'm not sure which bit you are trying to change?

techsupport15 · August 21, 2015, 6:30pm

EndYear field does not exist in the PersonMembership table.

It is derived from EndDate by Year(EndDate) AS EndYear.

in the 3 lines of code listed, I can't seem to change it correctly to reflect this issue.

Anywhere it say p.EndYear and pm.EndYear is incorrect without deriving from Year(EndDate) AS EndYear somehow. I tried that and it's apparently wrong.

techsupport15 · August 21, 2015, 8:33pm

Here's the code that works

WITH CLUBS AS (SELECT DISTINCT ClubID, ClubNo, SortName, ClubName, BSProgram, ClubSection, Code, President, Email, Phone, FacilityName, StreetOne, StreetTwo, City, State, PostalCode, URL, Certified, FacilityLastUpdate, ByLawsUploadDate, ProgramStatusId, Status FROM dbo.v060ClubOfficersPresOrNot), YEARS AS (SELECT YEAR(GETDATE()) AS THE_YEAR, CAST(CAST(YEAR(GETDATE()) AS char(4)) + '-06-30' AS date) AS END_DATE UNION ALL SELECT THE_YEAR + 1 AS Expr1, DATEADD(year, 1, END_DATE) AS Expr2 FROM YEARS AS YEARS_1 WHERE (THE_YEAR + 1 < YEAR(GETDATE()) + 5)), Memberships AS (SELECT P.OrganizationId, P.InvoiceNumber, YEAR(P.EndDate) AS EndYear, P.MembershipTypeId FROM dbo.PersonMembership AS P INNER JOIN YEARS AS Y ON Year(P.EndDate) AS EndYear = Y.THE_YEAR AND P.EndDate = Y.END_DATE) SELECT co.ClubNo, co.SortName, co.ClubName, co.BSProgram, co.ClubSection, co.Code, co.President, co.Email, co.Phone, co.FacilityName, co.StreetOne, co.StreetTwo, co.City, co.State, co.PostalCode, co.URL, co.Certified, co.FacilityLastUpdate, co.ByLawsUploadDate, co.ProgramStatusId, co.Status, Y.THE_YEAR AS YearEnd, pm.MembershipTypeId, RTRIM(pm.InvoiceNumber) AS InvNo FROM YEARS AS Y CROSS JOIN CLUBS AS co LEFT OUTER JOIN Memberships AS pm ON co.ClubID = pm.OrganizationId AND Y.THE_YEAR = pm.EndYear ORDER BY co.ClubNo, YearEnd, pm.MembershipTypeId, InvNo

Except when I'm applying to the Live system because the PersonMembership doesn't actually have a field named EndYear. That's derived from EndDate.

I've tried changing but failed.
Are you able to make the changes to derive EndDate in the code? Since that's a valid field in the table but not EndYear.

bitsmed · August 22, 2015, 11:17am

With your latest query, persons with MembershipTypeId 3 occurs only once (the end year). Shouldn't he/she occur in all the years paid for?

To get your query to work, change this part:

ON Year(P.EndDate) AS EndYear = Y.THE_YEAR

to:

ON Year(P.EndDate) = Y.THE_YEAR

Also you might want to add this to your join:

AND P.MembershipTypeId IN (1,2,3,4)