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

techsupport15 · August 17, 2015, 9:16pm

I have this View and want to also see Clubs that do not have current memberships.

I have the IS NULL but not seeing the Clubs that do NOT have memberships.

attribute.PersonMembership is a SQL table that has membership information where the criteria are set for.

SELECT dbo.v060ClubOfficersPresOrNot.ClubNo, dbo.v060ClubOfficersPresOrNot.SortName, dbo.v060ClubOfficersPresOrNot.ClubName, dbo.v060ClubOfficersPresOrNot.BSProgram, dbo.v060ClubOfficersPresOrNot.ClubSection, dbo.v060ClubOfficersPresOrNot.Code, RTRIM(attribute.PersonMembership.InvoiceNumber) AS InvNo, dbo.v060ClubOfficersPresOrNot.President, dbo.v060ClubOfficersPresOrNot.Email, dbo.v060ClubOfficersPresOrNot.Phone, dbo.v060ClubOfficersPresOrNot.FacilityName, dbo.v060ClubOfficersPresOrNot.StreetOne, dbo.v060ClubOfficersPresOrNot.StreetTwo, dbo.v060ClubOfficersPresOrNot.City, dbo.v060ClubOfficersPresOrNot.State, dbo.v060ClubOfficersPresOrNot.PostalCode, YEAR(attribute.PersonMembership.EndDate) AS YearEnd, attribute.PersonMembership.MembershipTypeId, dbo.v060ClubOfficersPresOrNot.URL, dbo.v060ClubOfficersPresOrNot.Certified, dbo.v060ClubOfficersPresOrNot.FacilityLastUpdate, dbo.v060ClubOfficersPresOrNot.ByLawsUploadDate, dbo.v060ClubOfficersPresOrNot.ProgramStatusId, dbo.v060ClubOfficersPresOrNot.Status FROM attribute.PersonMembership RIGHT OUTER JOIN dbo.v060ClubOfficersPresOrNot ON attribute.PersonMembership.OrganizationId = dbo.v060ClubOfficersPresOrNot.ClubID WHERE (DAY(attribute.PersonMembership.EndDate) = 30 OR DAY(attribute.PersonMembership.EndDate) IS NULL) AND (MONTH(attribute.PersonMembership.EndDate) = 6 OR MONTH(attribute.PersonMembership.EndDate) IS NULL) AND (YEAR(attribute.PersonMembership.EndDate) BETWEEN YEAR(GETDATE()) AND YEAR(GETDATE()) + 1 OR YEAR(attribute.PersonMembership.EndDate) IS NULL) AND (attribute.PersonMembership.MembershipTypeId IN (1, 2, 4) OR attribute.PersonMembership.MembershipTypeId IS NULL) OR (DAY(attribute.PersonMembership.EndDate) = 30 OR DAY(attribute.PersonMembership.EndDate) IS NULL) AND (MONTH(attribute.PersonMembership.EndDate) = 6 OR MONTH(attribute.PersonMembership.EndDate) IS NULL) AND (YEAR(attribute.PersonMembership.EndDate) BETWEEN YEAR(GETDATE()) AND YEAR(GETDATE()) + 4 OR YEAR(attribute.PersonMembership.EndDate) IS NULL) AND (attribute.PersonMembership.MembershipTypeId = 3 OR attribute.PersonMembership.MembershipTypeId IS NULL)
There's probably a better way to limit the month, day and year range than the way I have it too ...

The final report is in Crystal Reports with a EndYear parameter where the end-user enters the membership ending year.

Right now they are entering to see all of the clubs with current
memberships and would like to see the clubs without current memberships.

So the result basically they are seeing are:

Club 1 has 15 memberships
Club 2 has 10 memberships
Club 3 has 0 memberships or Null memberships <-- trying to get
this to show. Knowing that Club 5305 had 15 memberships in 2015 so when
the end-user enters 2015 in the prompt they would see Club 5305 with 15
memberships but when they enter 2016 they would still see 5305 but it
would either say 0 or null memberships but still listing Club 5305

The "v060ClubOfficersPresOrNot" table consists of the following clubs as the sampling:

ClubNo 5305 - one at issue, has memberships in 2015 but none in 2016 yet not showing up
ClubNo 1617 - has no president but clubs should show and any memberships within the parameters should show
ClubNo 22 - has president and clubs should show with any memberships within the parameters
ClubNo 8004 - has no memberships in the period and does show up

The "PersonMembership" has all the membership records from 2015 through 2019 of membertypeid 1-4 for the sampling.

Since the syntax used in Access do not carry over without modifications to SQL, would appreciate the SQL syntax to make it work.

And if you know the proper SQL syntax for "Between
Year(Date())+IIf(Month(Date())>=7,1,0) And
Year(Date())+IIf(Month(Date())>=7,1,0)+4" instead of what I currently
have in SQL, that would be wonderful.

ScottPletcher · August 17, 2015, 10:22pm

I suspect that rather than just "month = 6" on both, there should be a check where month 1-6 is year, and month past 6 is year + 1. But, sorry, I don't have time to fully sort that bit out now. But you can rewrite the rest for clarity and accuracy:

SELECT    vcopon.ClubNo, vcopon.SortName, vcopon.ClubName, 
                      vcopon.BSProgram, vcopon.ClubSection, vcopon.Code, 
                      RTRIM(pm.InvoiceNumber) AS InvNo, vcopon.President, vcopon.Email, 
                      vcopon.Phone, vcopon.FacilityName, vcopon.StreetOne, 
                      vcopon.StreetTwo, vcopon.City, vcopon.State, vcopon.PostalCode, 
                      YEAR(pm.EndDate) AS YearEnd, pm.MembershipTypeId, vcopon.URL, 
                      vcopon.Certified, vcopon.FacilityLastUpdate, vcopon.ByLawsUploadDate, 
                      vcopon.ProgramStatusId, vcopon.Status
FROM         attribute.PersonMembership pm RIGHT OUTER JOIN
                      dbo.v060ClubOfficersPresOrNot vcopon ON pm.OrganizationId = vcopon.ClubID AND
                      (DAY(pm.EndDate) = 30) AND 
                      (MONTH(pm.EndDate) = 6) AND /*of course this will change if the month conditions are actually different*/
                      ((YEAR(pm.EndDate) BETWEEN YEAR(GETDATE()) AND YEAR(GETDATE()) + 1 AND 
                        pm.MembershipTypeId IN (1, 2, 4)) OR
                       (YEAR(pm.EndDate) BETWEEN YEAR(GETDATE()) AND YEAR(GETDATE()) + 4 AND
                        pm.MembershipTypeId = 3))

techsupport15 · August 17, 2015, 10:33pm

Thank you.

Unfortunately I am getting the same result.

ClubNo 5305 has 15 memberships in 2015. That is what I am getting.

Expecting to see one extra row that shows the Club info and null values in the PersonMembership fields. Showing that this Club has no memberships in the current year.

techsupport15 · August 17, 2015, 10:43pm

Actually it works for ClubNo 8004.
This club has NO memberships at all.

However ClubNo 5305 who has memberships in the prior and none this year is NOT showing.

I would expect the same result as I am seeing for ClubNo 8004 where it shows the Club info but NULL for the membership info for ClubNo 5305.

Why isn't it?

techsupport15 · August 17, 2015, 10:46pm

On the date parameter on EndDate, this works in Access but I don't know the equivalent in SQL?

with

bitsmed · August 17, 2015, 11:37pm

In which cases is the EndDate null?
When members becomes inactive, will all null EndDate for this member be filled in?
Do you have a StartDate?

This might work for you:

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 c as c
       left outer join p as p
                    on p.OrganizationId=c.ClubID
                   and (p.EndDate<=dateadd(month
                                          ,6+case p.MembershipTypeId=3 then 4 else 0 end
                                          ,dateadd(year,datediff(year,0,dateadd(month,(datediff(month,0,getdate())+6),0)),0)
                                          )
                    or  p.EndDate is null
                       )
                   and p.MembershipTypeId in (1,2,3,4)

techsupport15 · August 17, 2015, 11:40pm

I will try this.
To answer your question, there will never be a Null EndDate. When the invoice is created it generates the enddate, which is last day of the fiscal year, 6/30/## of future year. The end of the membership invoice the member has paid.

Since it's just a storage for invoices for the memberships purchased, the inactive is determined by the date in the EndDate. So the 6/30/2015 is now all expired memberships. The current memberships has 6/30/2016 and through - 6/30/2019 only for Collegiate members.

Yes, there is a startdate but irrelevant as they always report on the EndDate. They want to see if the Clubs have memberships and if they they how many in a given year based on the EndDate.

bitsmed · August 17, 2015, 11:50pm

Regarding startdate, could there be inactive menbers with paid invoices for next season (member to become active)?

Since the EndDate is NEVER null, you can then change:

           and (p.EndDate<=dateadd(month
                                  ,6+case p.MembershipTypeId=3 then 4 else 0 end
                                  ,dateadd(year,datediff(year,0,dateadd(month,(datediff(month,0,getdate())+6),0)),0)
                                  )
            or  p.EndDate is null
               )

to

           and p.EndDate<=dateadd(month
                                 ,6+case p.MembershipTypeId=3 then 4 else 0 end
                                 ,dateadd(year,datediff(year,0,dateadd(month,(datediff(month,0,getdate())+6),0)),0)
                                 )

techsupport15 · August 18, 2015, 12:15am

? So there are invoices with EndDate of 6/30/2015, they are now inactive since their membership expired.

If the Clubs are inactive but have invoices paid for next season? Perhaps, but unlikely and would be captured by the Status field. But they aren't limiting it and looking at all statuses.

I'll try the modification. Thank you

bitsmed · August 18, 2015, 12:24am

Ohh I forgot - you need to add this line:

   and p.EndDate>=getdate()

bitsmed · August 18, 2015, 12:36am

What I meant was, could person pay for next season (20160701 - 20170630)? He/she would then show up on the list, unless you check the startdate field.

Looks like I forgot the tablenames in my first Query - this is what I propose (so far):

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<=getdate()
                   and p.EndDate>=getdate()
                   and p.EndDate<=dateadd(month
                                         ,6+case p.MembershipTypeId=3 then 4 else 0 end
                                         ,dateadd(year,datediff(year,0,dateadd(month,(datediff(month,0,getdate())+6),0)),0)
                                         )
                   and p.MembershipTypeId in (1,2,3,4)

techsupport15 · August 18, 2015, 1:04am

Yes, you can pay for next season. StartDate is irrelevant as it populates with the InvoiceDate or PaymentDate.

Key field is EndDate to tell you if they are current or not.

I got a SQL Execution Error.
Looks like on "=" somewhere?

bitsmed · August 18, 2015, 1:08am

Seems I forgot "when" in case statement - sorry:

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<=getdate()
                   and p.EndDate>=getdate()
                   and p.EndDate<=dateadd(month
                                         ,6+case when p.MembershipTypeId=3 then 4 else 0 end
                                         ,dateadd(year,datediff(year,0,dateadd(month,(datediff(month,0,getdate())+6),0)),0)
                                         )
                   and p.MembershipTypeId in (1,2,3,4)

techsupport15 · August 18, 2015, 1:10am

What scottpletcher provided produces clubs with existing memberships and clubs without any memberships but not in between.

So in the instance of ClubNo 5305. They had memberships last season (2015) but not this season and they should still show like ClubNo 8004. Who never had a membership.

ClubNo 8004 shows with Club info and the membership info are either blank or null.
ClubNo 5305 only shows 2015 data and I expect to see another row for them like it shows for ClubNo 8004. Club info but the membership info is blank or null with the 2015 membership info.

techsupport15 · August 18, 2015, 1:18am

Thank you! Even closer!!

So now both ClubNo 8004 and 5305 are showing BUT 5305 still needs to show 2015 memberships. there are 15.

The EndDate must be something like this: Just that this doesn't work in SQL. Nor doing the DateSerial since that function doesn't exist in SQL

Since, until 2 months ago the EndDates with 6/30/2015 were the CURRENT memberships.

The season is 7/1/14 - 6/30/15 or now 7/1/15 - 6/30/16.
So if this was ran in 6/2015, all the EndDates with 6/30/2015 were current.

The user would either enter 2015 or 2016 for the "Season" and to see a comparison from one season to the next. Hence the range to still show prior Season, current and future seasons. As the Collegiate memberships are 4 years long.

So the range would need to show
MemberTypeID = 1, 2, 4 for 2015 and 2016
MemberTypeID = 3 for 2015 - 2019

bitsmed · August 18, 2015, 1:28am

Replace this

           and p.EndDate<=dateadd(month
                                 ,6+case when p.MembershipTypeId=3 then 4 else 0 end
                                 ,dateadd(year,datediff(year,0,dateadd(month,(datediff(month,0,getdate())+6),0)),0)
                                 )

with

           and p.EndDate<=dateadd(month
                                 ,6+case when p.MembershipTypeId=3 then 36 else 0 end
                                 ,dateadd(year,datediff(year,0,dateadd(month,(datediff(month,0,getdate())+6),0)),0)
                                 )

techsupport15 · August 18, 2015, 1:45am

Getting the same result

unfortunately I have no idea what the then 36 else 0 vs then 4 else 0 does or suppose to do?

I'm not really understanding how the date ranges work here.

[code]
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

FROM
dbo.v060ClubOfficersPresOrNot AS c LEFT OUTER JOIN

attribute.PersonMembership AS p ON p.OrganizationId = c.ClubID AND and p.EndDate<=dateadd(month
,6+case when p.MembershipTypeId=3 then 36 else 0 end
,dateadd(year,datediff(year,0,dateadd(month,(datediff(month,0,getdate())+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[/code]

bitsmed · August 18, 2015, 6:26am

Actually this ought to be enough in your join:

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

If you absolutely doesn't want to use startdate, you can calculate the startdate from the enddate:

   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)

techsupport15 · August 18, 2015, 2:30pm

getting the same results.

both 5305 and 8004 are showing club info and no membership info BUT still need to see the membership info for 5305 in 2015, the current year.

bitsmed · August 18, 2015, 5:28pm

I see you are still reluctant to use startdate (because then it would have worked).
Anyway, try this:

   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)

If this doesn't work, please show the data you are struggling with.