OUTER JOIN Not Producing Non Existent Record with IS NULL

Question

Post reply

OUTER JOIN Not Producing Non Existent Record with IS NULL

Viewing 15 posts - 61 through 75 (of 93 total)

You must be logged in to reply to this topic. Login to reply

serviceaellis SSCommitted Points: 1865 More actions · Answer 1

sgmunson,

thanks for trying ... unfortunately it's not completely correct.

it's the same thing, 5305 is NOT showing 0 for 2016.

also, it cannot do the count for this details.

They will "drill-down" to see each invoice by PersonID.

I'll do the grouping and count. The crystal will do that.

the details need to stay grouped in full detail for each club with their invoice, personid, membertypeid, enddate

sgmunson SSC Guru Points: 110640 More actions · Answer 2

serviceaellis (8/20/2015)
sgmunson,
thanks for trying ... unfortunately it's not completely correct.
it's the same thing, 5305 is NOT showing 0 for 2016.
also, it cannot do the count for this details.
They will "drill-down" to see each invoice by PersonID.
I'll do the grouping and count. The crystal will do that.
the details need to stay grouped in full detail for each club with their invoice, personid, membertypeid, enddate

Easy peasy... We take out the group by and the count, and voila - 131 records instead of 49. You can group using Crystal Reports.

DECLARE @YEAR AS char(4) = CAST(YEAR(GETDATE()) AS char(4));

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

),

Memberships AS (

SELECT OrganizationId, 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,

PM.EndYear AS YearEnd, PM.MembershipTypeId, COP.URL, COP.Certified,

COP.FacilityLastUpdate, COP.ByLawsUploadDate, COP.ProgramStatusId, COP.[Status]--, COUNT(PM.EndYear) AS TheCount

FROM CLUBS AS COP

LEFT OUTER JOIN Memberships AS PM

ON COP.ClubID = PM.OrganizationId

ORDER BY COP.ClubNo, PM.EndYear, PM.MembershipTypeId, RTRIM(PM.InvoiceNumber);

Results:

ClubNoSortNameClubNameBSProgramClubSectionCodeInvNoPresidentEmailPhoneFacilityNameStreetOneStreetTwoCityStatePostalCodeYearEndMembershipTypeIdURLCertifiedFacilityLastUpdateByLawsUploadDateProgramStatusIdStatus

22ADIRONDACKSSC of the Adirondacks, Inc.YesEasternNA0014568Jeanette Woodruffchristma@plattsburgh.eduNULLStafford ArenaRuger StNULLPlattsburgNY1290120151NULLYes2014-07-06 17:16:00.000NULL3Active

22ADIRONDACKSSC of the Adirondacks, Inc.YesEasternNA0020446Jeanette Woodruffchristma@plattsburgh.eduNULLStafford ArenaRuger StNULLPlattsburgNY1290120151NULLYes2014-07-06 17:16:00.000NULL3Active

22ADIRONDACKSSC of the Adirondacks, Inc.YesEasternNA0021345Jeanette Woodruffchristma@plattsburgh.eduNULLStafford ArenaRuger StNULLPlattsburgNY1290120151NULLYes2014-07-06 17:16:00.000NULL3Active

22ADIRONDACKSSC of the Adirondacks, Inc.YesEasternNA0023167Jeanette Woodruffchristma@plattsburgh.eduNULLStafford ArenaRuger StNULLPlattsburgNY1290120151NULLYes2014-07-06 17:16:00.000NULL3Active

22ADIRONDACKSSC of the Adirondacks, Inc.YesEasternNA0032860Jeanette Woodruffchristma@plattsburgh.eduNULLStafford ArenaRuger StNULLPlattsburgNY1290120151NULLYes2014-07-06 17:16:00.000NULL3Active

22ADIRONDACKSSC of the Adirondacks, Inc.YesEasternNA0020446Jeanette Woodruffchristma@plattsburgh.eduNULLStafford ArenaRuger StNULLPlattsburgNY1290120152NULLYes2014-07-06 17:16:00.000NULL3Active

22ADIRONDACKSSC of the Adirondacks, Inc.YesEasternNA0021345Jeanette Woodruffchristma@plattsburgh.eduNULLStafford ArenaRuger StNULLPlattsburgNY1290120152NULLYes2014-07-06 17:16:00.000NULL3Active

22ADIRONDACKSSC of the Adirondacks, Inc.YesEasternNA0032860Jeanette Woodruffchristma@plattsburgh.eduNULLStafford ArenaRuger StNULLPlattsburgNY1290120152NULLYes2014-07-06 17:16:00.000NULL3Active

22ADIRONDACKSSC of the Adirondacks, Inc.YesEasternNA0039126Jeanette Woodruffchristma@plattsburgh.eduNULLStafford ArenaRuger StNULLPlattsburgNY1290120152NULLYes2014-07-06 17:16:00.000NULL3Active

22ADIRONDACKSSC of the Adirondacks, Inc.YesEasternNA0039130Jeanette Woodruffchristma@plattsburgh.eduNULLStafford ArenaRuger StNULLPlattsburgNY1290120152NULLYes2014-07-06 17:16:00.000NULL3Active

22ADIRONDACKSSC of the Adirondacks, Inc.YesEasternNA0020446Jeanette Woodruffchristma@plattsburgh.eduNULLStafford ArenaRuger StNULLPlattsburgNY1290120154NULLYes2014-07-06 17:16:00.000NULL3Active

22ADIRONDACKSSC of the Adirondacks, Inc.YesEasternNA0023167Jeanette Woodruffchristma@plattsburgh.eduNULLStafford ArenaRuger StNULLPlattsburgNY1290120154NULLYes2014-07-06 17:16:00.000NULL3Active

22ADIRONDACKSSC of the Adirondacks, Inc.YesEasternNA0032860Jeanette Woodruffchristma@plattsburgh.eduNULLStafford ArenaRuger StNULLPlattsburgNY1290120154NULLYes2014-07-06 17:16:00.000NULL3Active

22ADIRONDACKSSC of the Adirondacks, Inc.YesEasternNA0034644Jeanette Woodruffchristma@plattsburgh.eduNULLStafford ArenaRuger StNULLPlattsburgNY1290120154NULLYes2014-07-06 17:16:00.000NULL3Active

22ADIRONDACKSSC of the Adirondacks, Inc.YesEasternNA0039128Jeanette Woodruffchristma@plattsburgh.eduNULLStafford ArenaRuger StNULLPlattsburgNY1290120154NULLYes2014-07-06 17:16:00.000NULL3Active

22ADIRONDACKSSC of the Adirondacks, Inc.YesEasternNA0039130Jeanette Woodruffchristma@plattsburgh.eduNULLStafford ArenaRuger StNULLPlattsburgNY1290120154NULLYes2014-07-06 17:16:00.000NULL3Active

22ADIRONDACKSSC of the Adirondacks, Inc.YesEasternNA0045470Jeanette Woodruffchristma@plattsburgh.eduNULLStafford ArenaRuger StNULLPlattsburgNY1290120161NULLYes2014-07-06 17:16:00.000NULL3Active

22ADIRONDACKSSC of the Adirondacks, Inc.YesEasternNA0048246Jeanette Woodruffchristma@plattsburgh.eduNULLStafford ArenaRuger StNULLPlattsburgNY1290120161NULLYes2014-07-06 17:16:00.000NULL3Active

22ADIRONDACKSSC of the Adirondacks, Inc.YesEasternNA0048313Jeanette Woodruffchristma@plattsburgh.eduNULLStafford ArenaRuger StNULLPlattsburgNY1290120161NULLYes2014-07-06 17:16:00.000NULL3Active

22ADIRONDACKSSC of the Adirondacks, Inc.YesEasternNA0052248Jeanette Woodruffchristma@plattsburgh.eduNULLStafford ArenaRuger StNULLPlattsburgNY1290120161NULLYes2014-07-06 17:16:00.000NULL3Active

22ADIRONDACKSSC of the Adirondacks, Inc.YesEasternNA0055424Jeanette Woodruffchristma@plattsburgh.eduNULLStafford ArenaRuger StNULLPlattsburgNY1290120161NULLYes2014-07-06 17:16:00.000NULL3Active

22ADIRONDACKSSC of the Adirondacks, Inc.YesEasternNA0058351Jeanette Woodruffchristma@plattsburgh.eduNULLStafford ArenaRuger StNULLPlattsburgNY1290120161NULLYes2014-07-06 17:16:00.000NULL3Active

22ADIRONDACKSSC of the Adirondacks, Inc.YesEasternNA0045470Jeanette Woodruffchristma@plattsburgh.eduNULLStafford ArenaRuger StNULLPlattsburgNY1290120162NULLYes2014-07-06 17:16:00.000NULL3Active

22ADIRONDACKSSC of the Adirondacks, Inc.YesEasternNA0048246Jeanette Woodruffchristma@plattsburgh.eduNULLStafford ArenaRuger StNULLPlattsburgNY1290120162NULLYes2014-07-06 17:16:00.000NULL3Active

22ADIRONDACKSSC of the Adirondacks, Inc.YesEasternNA0048313Jeanette Woodruffchristma@plattsburgh.eduNULLStafford ArenaRuger StNULLPlattsburgNY1290120162NULLYes2014-07-06 17:16:00.000NULL3Active

22ADIRONDACKSSC of the Adirondacks, Inc.YesEasternNA0055424Jeanette Woodruffchristma@plattsburgh.eduNULLStafford ArenaRuger StNULLPlattsburgNY1290120162NULLYes2014-07-06 17:16:00.000NULL3Active

22ADIRONDACKSSC of the Adirondacks, Inc.YesEasternNA0058351Jeanette Woodruffchristma@plattsburgh.eduNULLStafford ArenaRuger StNULLPlattsburgNY1290120162NULLYes2014-07-06 17:16:00.000NULL3Active

22ADIRONDACKSSC of the Adirondacks, Inc.YesEasternNALegacyJeanette Woodruffchristma@plattsburgh.eduNULLStafford ArenaRuger StNULLPlattsburgNY1290120163NULLYes2014-07-06 17:16:00.000NULL3Active

22ADIRONDACKSSC of the Adirondacks, Inc.YesEasternNA0048246Jeanette Woodruffchristma@plattsburgh.eduNULLStafford ArenaRuger StNULLPlattsburgNY1290120164NULLYes2014-07-06 17:16:00.000NULL3Active

22ADIRONDACKSSC of the Adirondacks, Inc.YesEasternNA0048313Jeanette Woodruffchristma@plattsburgh.eduNULLStafford ArenaRuger StNULLPlattsburgNY1290120164NULLYes2014-07-06 17:16:00.000NULL3Active

22ADIRONDACKSSC of the Adirondacks, Inc.YesEasternNA0058351Jeanette Woodruffchristma@plattsburgh.eduNULLStafford ArenaRuger StNULLPlattsburgNY1290120164NULLYes2014-07-06 17:16:00.000NULL3Active

22ADIRONDACKSSC of the Adirondacks, Inc.YesEasternNALegacyJeanette Woodruffchristma@plattsburgh.eduNULLStafford ArenaRuger StNULLPlattsburgNY1290120173NULLYes2014-07-06 17:16:00.000NULL3Active

22ADIRONDACKSSC of the Adirondacks, Inc.YesEasternNA0023624Jeanette Woodruffchristma@plattsburgh.eduNULLStafford ArenaRuger StNULLPlattsburgNY1290120183NULLYes2014-07-06 17:16:00.000NULL3Active

22ADIRONDACKSSC of the Adirondacks, Inc.YesEasternNA0029828Jeanette Woodruffchristma@plattsburgh.eduNULLStafford ArenaRuger StNULLPlattsburgNY1290120183NULLYes2014-07-06 17:16:00.000NULL3Active

1617NULLCedar Valley FSCNoMidwesternNULL0021158NULLNULLNULLNULLNULLNULLNULLNULLNULL20151NULLNULLNULLNULLNULLNULL

1617NULLCedar Valley FSCNoMidwesternNULL0030959NULLNULLNULLNULLNULLNULLNULLNULLNULL20151NULLNULLNULLNULLNULLNULL

1617NULLCedar Valley FSCNoMidwesternNULL0021158NULLNULLNULLNULLNULLNULLNULLNULLNULL20152NULLNULLNULLNULLNULLNULL

1617NULLCedar Valley FSCNoMidwesternNULL0030959NULLNULLNULLNULLNULLNULLNULLNULLNULL20152NULLNULLNULLNULLNULLNULL

1617NULLCedar Valley FSCNoMidwesternNULL0021158NULLNULLNULLNULLNULLNULLNULLNULLNULL20154NULLNULLNULLNULLNULLNULL

1617NULLCedar Valley FSCNoMidwesternNULL0056033NULLNULLNULLNULLNULLNULLNULLNULLNULL20161NULLNULLNULLNULLNULLNULL

1617NULLCedar Valley FSCNoMidwesternNULL0056033NULLNULLNULLNULLNULLNULLNULLNULLNULL20162NULLNULLNULLNULLNULLNULL

5305ShorelineShoreline Skating ClubNoEasternNENULLMandy CurtinNULL(508)229-2700New England Sports Center121 Donald Lynch Blvd.NULLMarlboroughMA0175220151http://www.scboston.orgYes2014-07-02 13:44:00.000NULLNULLActive

5305ShorelineShoreline Skating ClubNoEasternNE0011069Mandy CurtinNULL(508)229-2700New England Sports Center121 Donald Lynch Blvd.NULLMarlboroughMA0175220151http://www.scboston.orgYes2014-07-02 13:44:00.000NULLNULLActive

5305ShorelineShoreline Skating ClubNoEasternNE0014744Mandy CurtinNULL(508)229-2700New England Sports Center121 Donald Lynch Blvd.NULLMarlboroughMA0175220151http://www.scboston.orgYes2014-07-02 13:44:00.000NULLNULLActive

5305ShorelineShoreline Skating ClubNoEasternNE0015085Mandy CurtinNULL(508)229-2700New England Sports Center121 Donald Lynch Blvd.NULLMarlboroughMA0175220151http://www.scboston.orgYes2014-07-02 13:44:00.000NULLNULLActive

5305ShorelineShoreline Skating ClubNoEasternNE0015112Mandy CurtinNULL(508)229-2700New England Sports Center121 Donald Lynch Blvd.NULLMarlboroughMA0175220151http://www.scboston.orgYes2014-07-02 13:44:00.000NULLNULLActive

5305ShorelineShoreline Skating ClubNoEasternNE0015141Mandy CurtinNULL(508)229-2700New England Sports Center121 Donald Lynch Blvd.NULLMarlboroughMA0175220151http://www.scboston.orgYes2014-07-02 13:44:00.000NULLNULLActive

5305ShorelineShoreline Skating ClubNoEasternNE0015112Mandy CurtinNULL(508)229-2700New England Sports Center121 Donald Lynch Blvd.NULLMarlboroughMA0175220152http://www.scboston.orgYes2014-07-02 13:44:00.000NULLNULLActive

8004NULLFlorida Interclub Skating CouncilNoEasternNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULL

Let me know if these are the correct results.

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

serviceaellis SSCommitted Points: 1865 More actions · Answer 3

sgmunson,

thanks again ... however that's ONLY reproducing the original number of records in the table.

I have an easy query, which I shared at the beginning that produces this result. It's cleaner than my original post since I've learned about the DateFromParts() available in SQL Server 2012 or above as I was using 2008.

SELECT p.MembershipTypeId, p.PersonId, p.InvoiceNumber, p.EndDate, p.StartDate, p.OrganizationId, c.ClubNo, c.ClubID

FROM dbo.PersonMembership AS p RIGHT OUTER JOIN

dbo.v060ClubOfficersPresOrNot AS c ON p.OrganizationId = c.ClubID

GROUP BY p.MembershipTypeId, p.PersonId, p.InvoiceNumber, p.EndDate, p.StartDate, p.OrganizationId, c.ClubNo, c.ClubID

HAVING (p.EndDate >= DateFromParts(YEAR(GETDATE()), 6, 30)) OR

(p.EndDate IS NULL)

ORDER BY c.ClubNo, p.PersonId, p.InvoiceNumber, p.EndDate

I think you missed why I'm on here seeking help.

I need to produce results that also includes the clubs without memberships like ClubNo 5305 who has memberships in 2015, expired by the way, since all memberships end on 6/30/yyyy.

Need to see their Club info with another row that shows like 8004 who doesn't have any memberships.

And for all other clubs who happens to not have memberships in the future years.

Because they still want to see ALL Club info whether they have or had memberships or no memberships.

So ALL of the Clubs from v060ClubOfficersPresOrNot must show AND if they have memberships from the PersonMemership, those need to show, if not still show the Club info but with NULL in the PersonMembership fields. Like you see for ClubNo 8004, displays whatever Club it has from the clubs table then the personmembership fields are all NULL.

That is what's not happening, the combination of the two.

I am only getting one or the other from all the formulas shared by all who has attempted.

The only difference I see is that 8004 has no membership info AT ALL whereas the other Clubs has membership info.

Why I need help to figure this out. How to include that for ALL the clubs that's had membership but doesn't have one in 2016 or in the future.

Of course they'll show if they've had it in the year 2015.

sgmunson SSC Guru Points: 110640 More actions · Answer 4

serviceaellis (8/20/2015)
sgmunson,
thanks again ... however that's ONLY reproducing the original number of records in the table.
I have an easy query, which I shared at the beginning that produces this result. It's cleaner than my original post since I've learned about the DateFromParts() available from SQL Server 2012 or above as I was using 2008.
SELECT dbo.PersonMembership.MembershipTypeId, dbo.PersonMembership.PersonId, dbo.PersonMembership.InvoiceNumber,
dbo.PersonMembership.EndDate, dbo.PersonMembership.StartDate, dbo.PersonMembership.OrganizationId, dbo.v060ClubOfficersPresOrNot.ClubNo,
dbo.v060ClubOfficersPresOrNot.ClubID
FROM dbo.PersonMembership RIGHT OUTER JOIN
dbo.v060ClubOfficersPresOrNot ON dbo.PersonMembership.OrganizationId = dbo.v060ClubOfficersPresOrNot.ClubID
GROUP BY dbo.PersonMembership.MembershipTypeId, dbo.PersonMembership.PersonId, dbo.PersonMembership.InvoiceNumber, dbo.PersonMembership.EndDate,
dbo.PersonMembership.StartDate, dbo.PersonMembership.OrganizationId, dbo.v060ClubOfficersPresOrNot.ClubNo, dbo.v060ClubOfficersPresOrNot.ClubID
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
I think you missed why I'm on here seeking help.
I need to produce results that also includes the clubs without memberships like ClubNo 5305 who has memberships in 2015, expired by the way, since all memberships end on 6/30/yyyy.
Need to see their Club info with another row that shows like 8004 who doesn't have any memberships.
And for all other clubs who happens to not have memberships in the future years.
Because they still want to see ALL Club info whether they have or had memberships or no memberships.
So ALL of the Clubs from v060ClubOfficersPresOrNot must show AND if they have memberships from the PersonMemership, those need to show, if not still show the Club info but with NULL in the PersonMembership fields.
That is what's not happening, the combination of the two.
I am only getting one or the other from all the formulas shared by all who has attempted.

So how are you determining that my query doesn't do that? Please tell me you are NOT using Crystal Reports to make that verification. If you are not using SSMS and copying my query exactly as posted, then we'll keep going back and forth. Did you even LOOK at the results that I posted, that clearly show results for club 5305 ? It also shows a row for club 8004, so I have to dispute your finding. Also, you have NOT disclosed exactly what you do to anyone's query when you place it into Crystal. What, EXACTLY, are you doing with the parameter? We can't just guess... That might well be the cause of the problem.

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

serviceaellis SSCommitted Points: 1865 More actions · Answer 5

sgmunson (8/20/2015)
So how are you determining that my query doesn't do that? Please tell me you are NOT using Crystal Reports to make that verification. If you are not using SSMS and copying my query exactly as posted, then we'll keep going back and forth. Did you even LOOK at the results that I posted, that clearly show results for club 5305 ? It also shows a row for club 8004, so I have to dispute your finding. Also, you have NOT disclosed exactly what you do to anyone's query when you place it into Crystal. What, EXACTLY, are you doing with the parameter? We can't just guess... That might well be the cause of the problem.

yes of course, how else can I say it's wrong.

it's exactly the same number of records as my simple query and it is NOT showing a row of the clubno 5305 without membership info for the 0 membership record it needs to show for 5305.

if you look at it it's the same result as my original code.

and it's clear, right there, the result you posted as well as running on SQL, the last line is 8004 without membership but if you look at every line for 5305, they all have membership info.

there is no row of data like 8004 for 5305 without membership info.

The goal since this post started.

sgmunson SSC Guru Points: 110640 More actions · Answer 6

serviceaellis (8/20/2015)
sgmunson (8/20/2015)
So how are you determining that my query doesn't do that? Please tell me you are NOT using Crystal Reports to make that verification. If you are not using SSMS and copying my query exactly as posted, then we'll keep going back and forth. Did you even LOOK at the results that I posted, that clearly show results for club 5305 ? It also shows a row for club 8004, so I have to dispute your finding. Also, you have NOT disclosed exactly what you do to anyone's query when you place it into Crystal. What, EXACTLY, are you doing with the parameter? We can't just guess... That might well be the cause of the problem.
yes of course, how else can I say it's wrong.
it's exactly the same number of records as my simple query and it is NOT showing a row of the clubno 5305 without membership info for the 0 membership record it needs to show for 5305.
if you look at it it's the same result as my original code.
and it's clear, right there, the result you posted as well as running on SQL, the last line is 8004 without membership but if you look at every line for 5305, they all have membership info.
there is no row of data like 8004 for 5305 without membership info.
The goal since this post started.

Boy are we on two different pages. Why would you need a record for club 5305 that shows 0 memberships? And how does the parameter in Crystal figure into this. I'm going to need to know...

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

serviceaellis SSCommitted Points: 1865 More actions · Answer 7

sgmunson (8/20/2015)
Boy are we on two different pages. Why would you need a record for club 5305 that shows 0 memberships? And how does the parameter in Crystal figure into this. I'm going to need to know...

Yup, somewhere in all the posts you must have missed the reason why I posted this, though the original post that started it explains it. As well as the post before this. Hopefully you read the explanation as I'm repeating it multiple times throughout this entire thread. oie.

Forget Crystal for the sake of this formula needing to be figured out, the formula must produce the full details. Which must include ALL Club info from the v060ClubOfficersPresOrNot.

Doesn't matter if they had a membership or not. They still want to see the Club information AND to then see any membership IF or if the club does NOT have any. So if a club had membership last year but this year they don't they still want to see the list of Clubs, the membership info fields would just be null and their report will count 0 membership for them.

The ones who doesn't have any does show though. why I said this is the only difference between 8004 and 5305 who does have memberships in 2015.

I shared the sample data specifically of these Clubs because they have the information necessary to work with in order to come up with the formula to produce this particular result.

For some reason the basic and simple query that says show me all the records from the Clubs table and if there's any Club memberships from the PersonMembership table for memberships from EndDate >= This year [2015].

why I'm on here trying to see if someone who might know how to do what needs to be done here.

Since they'll want to see details (drill-down on the summarized report), the query from SSMS must be the full details as explained that can be grouped on later. So don't worry about that part of it. What's important is that the full data, details available.

Take a look at your result for ClubNo 8004. See how it shows the club info but when your scroll over to the membership fields they are NULL.

That's how it should show for 5305 after showing their memberships in 2015.

because this details is for 2015+4 years.

So in 2015 they may have had # of memberships but year after 2015 they do not. And since the end-user will enter 2016 or any other future year up to 4 years for reporting, 5305 must show when they enter 2016 for the report because they are a club but does not have memberships in 2016.

serviceaellis SSCommitted Points: 1865 More actions · Answer 8

so another way to explain what I've been trying to state, think I'm saying the same thing though ...

need current and future years AND when there is no membership, including when they had it in the past.

current year = expired enddate of 6/30/2015. Future year (AKA: "Current" membership) are those with enddate 6/30/2016+3. Because if they have memberships that ended this year on 6/30/2015 are no longer considered "current". The clubs memberships are considered "Current" IF their enddate is in the future. In this case, 6/30/2016 + 3

Now 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 are 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.

BUT also needs to show, for EndYear 2016, with no membership.

Like it is for 8004.

serviceaellis SSCommitted Points: 1865 More actions · Answer 9

I prefer this format. This result seems only for 2016, like most of the other formulas, it works when it's a specific year but not when it's a range of the years needed in the details.

SELECT c.ClubID, 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,

p.PersonId, RTRIM(p.InvoiceNumber) AS InvNo, p.MembershipTypeId, p.EndDate

FROM dbo.v060ClubOfficersPresOrNot AS c LEFT OUTER JOIN

dbo.PersonMembership AS p ON p.OrganizationId = c.ClubID AND p.EndDate >= GETDATE() AND GETDATE() >= DATEADD(month,

- 6 - CASE WHEN p.MembershipTypeId = 3 THEN 4 ELSE 0 END, DATEADD(year, DATEDIFF(year, 1, DATEADD(month, DATEDIFF(month, 0, p.EndDate) + 6, 0)), 0)) AND

p.MembershipTypeId IN (1, 2, 3, 4)

GROUP BY c.ClubID, 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), p.PersonId,

RTRIM(p.InvoiceNumber), p.MembershipTypeId, p.EndDate

ORDER BY c.ClubNo, YearEnd, p.PersonId, InvNo

I am unsuccessful in changing this part

p.EndDate >= GETDATE() AND GETDATE() >= DATEADD(month,

- 6 - CASE WHEN p.MembershipTypeId = 3 THEN 4 ELSE 0 END, DATEADD(year, DATEDIFF(year, 1, DATEADD(month, DATEDIFF(month, 0, p.EndDate) + 6, 0)), 0))

to use p.EndDate >=DateFromParts(Year(GetDate()),6,30) AND to produce the results with the NULL membership info as it is showing when it's for the more than one year. A year range.

Any ideas on how to change that part to make it work perhaps?

sgmunson SSC Guru Points: 110640 More actions · Answer 10

serviceaellis (8/20/2015)
sgmunson (8/20/2015)
Boy are we on two different pages. Why would you need a record for club 5305 that shows 0 memberships? And how does the parameter in Crystal figure into this. I'm going to need to know...
Yup, somewhere in all the posts you must have missed the reason why I posted this, though the original post that started it explains it. As well as the post before this. Hopefully you read the explanation as I'm repeating it multiple times throughout this entire thread. oie.
Forget Crystal for the sake of this formula needing to be figured out, the formula must produce the full details. Which must include ALL Club info from the v060ClubOfficersPresOrNot.
Doesn't matter if they had a membership or not. They still want to see the Club information AND to then see any membership IF or if the club does NOT have any. So if a club had membership last year but this year they don't they still want to see the list of Clubs, the membership info fields would just be null and their report will count 0 membership for them.
The ones who doesn't have any does show though. why I said this is the only difference between 8004 and 5305 who does have memberships in 2015.
I shared the sample data specifically of these Clubs because they have the information necessary to work with in order to come up with the formula to produce this particular result.
For some reason the basic and simple query that says show me all the records from the Clubs table and if there's any Club memberships from the PersonMembership table for memberships from EndDate >= This year [2015].
why I'm on here trying to see if someone who might know how to do what needs to be done here.
Since they'll want to see details (drill-down on the summarized report), the query from SSMS must be the full details as explained that can be grouped on later. So don't worry about that part of it. What's important is that the full data, details available.
Take a look at your result for ClubNo 8004. See how it shows the club info but when your scroll over to the membership fields they are NULL.
That's how it should show for 5305 after showing their memberships in 2015.
because this details is for 2015+4 years.
So in 2015 they may have had # of memberships but year after 2015 they do not. And since the end-user will enter 2016 or any other future year up to 4 years for reporting, 5305 must show when they enter 2016 for the report because they are a club but does not have memberships in 2016.

So, if I understand correctly, you need to show at least an empty record for every year in a 5 year window, for each club. Here you go:

DECLARE @YEAR AS char(4) = CAST(YEAR(GETDATE()) AS char(4));

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, 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,

Y.THE_YEAR AS YearEnd, PM.MembershipTypeId, COP.URL, COP.Certified,

COP.FacilityLastUpdate, COP.ByLawsUploadDate, COP.ProgramStatusId, COP.[Status]

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.MembershipTypeId, RTRIM(PM.InvoiceNumber);