August 17, 2015 at 4:24 pm
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.
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 report is ran using Crystal Reports with a EndYear parameter where the end-user enters the membership ending year.
They are entering to see all of the clubs with current memberships and would like to see the clubs without current memberships.
Example result is basically:
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 Club 5305 but it would either say 0 or null memberships but still listing Club 5305. It is not showing Club 5305 at all right now.
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 in SQL.
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.
August 18, 2015 at 1:56 am
-- Note the use of aliases to reduce noise and make your query more clear
-- avoid right joins, most humans can't read them
-- don't put a column from an outer-joined table into the WHERE clause (unless
-- checking for NULL) because it will become an INNER join
-- I can't make head or tail of your date filter. Can we have it in English please?
SELECT
c.ClubNo, c.SortName, c.ClubName,
c.BSProgram, c.ClubSection, c.Code,
RTRIM(pm.InvoiceNumber) AS InvNo,
c.President, c.Email,
c.Phone, c.FacilityName, c.StreetOne,
c.StreetTwo, c.City, c.State, c.PostalCode,
YEAR(pm.EndDate) AS YearEnd,
pm.MembershipTypeId, c.URL,
c.Certified, c.FacilityLastUpdate, c.ByLawsUploadDate,
c.ProgramStatusId, c.Status
FROM dbo.v060ClubOfficersPresOrNot c
LEFT JOIN attribute.PersonMembership pm
ON pm.OrganizationId = c.ClubID
AND pm.EndDate >= DATEADD(DAY,-365,GETDATE())
AND pm.EndDate <= GETDATE()
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
August 18, 2015 at 6:56 am
Here's another alternative, based on the assumption that ALL memberships you're looking to report on end on June 30th of a given year:
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);
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,
YEAR(PM.EndDate) AS YearEnd, PM.MembershipTypeId, COP.URL, COP.Certified,
COP.FacilityLastUpdate, COP.ByLawsUploadDate, COP.ProgramStatusId, COP.[Status]
FROM dbo.v060ClubOfficersPresOrNot AS COP
LEFT OUTER JOIN attribute.PersonMembership AS PM
ON COP.ClubID = PM.OrganizationId
AND PM.EndDate IN (@START_DATE, @END_DATE1,
CASE PM.MembershipTypeId WHEN 3 THEN @END_DATE2 END,
CASE PM.MembershipTypeId WHEN 3 THEN @END_DATE3 END,
CASE PM.MembershipTypeId WHEN 3 THEN @END_DATE4 END)
Let us know if this works or if that assumption is inaccurate.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
August 18, 2015 at 7:20 am
I will try this.
As for the date range
Need to see all Clubs memberships ending on 6/30
of current year (2015) or next year (2016) when the MemberTypeID = 1, 2, or 4
of current year + 4 (2015 - 2019) when the MemberTypeID = 3
AND to see all Clubs without without memberships
August 18, 2015 at 7:20 am
I will try this.
Yes, all memberships that end on 6/30 of any given year.
Need to see all Clubs memberships ending on 6/30
of current year (2015) or next year (2016) when the MemberTypeID = 1, 2, or 4
of current year + 4 (2015 - 2019) when the MemberTypeID = 3
AND to see all Clubs without memberships
August 18, 2015 at 8:04 am
Thank you both ...
however ...
ChrisM@Work,
yours is showing 2014.
and both are not showing ClubNo 5305 with a null membership info like ClubNo 8004.
NOTE: 5305 has memberships in 2015 and prior years.
8004 has none at all.
Need to see 5305 memberships in 2015 and the null row like 8004 with no membership.
August 18, 2015 at 8:10 am
serviceaellis (8/18/2015)
Thank you both ...however ...
ChrisM@Work,
yours is showing 2014.
and both are not showing ClubNo 5305 with a null membership info like ClubNo 8004.
NOTE: 5305 has memberships in 2015 and prior years.
8004 has none at all.
Need to see 5305 memberships in 2015 and the null row like 8004 with no membership.
If ClubNo 5305 isn't visible in the output of my query then it's not in the parent table, hence either bad data or bad assumption.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
August 18, 2015 at 8:26 am
It is showing but showing 2014 and 2015.
It should show 2015 AND a null row like ClubNo 8004
So neither solutions are showing ClubNo 5305 null row like ClubNo 8004.
Only difference I see is that ClubNo 8004 has no memberships at all whereas ClubNo 5305?
Since it's expected to report on ALL Clubs with memberships in the current year AND if they don't have a membership with the next year enddate of 6/30/2016, it should show.
Which ClubNo 8004 is showing up but not ClubNo 5305.
August 18, 2015 at 8:31 am
serviceaellis (8/18/2015)
It is showing but showing 2014 and 2015.It should show 2015 AND a null row like ClubNo 8004
So neither solutions are showing ClubNo 5305 null row like ClubNo 8004.
Only difference I see is that ClubNo 8004 has no memberships at all whereas ClubNo 5305?
Since it's expected to report on ALL Clubs with memberships in the current year AND if they don't have a membership with the next year enddate of 6/30/2016, it should show.
Which ClubNo 8004 is showing up but not ClubNo 5305.
Is it in table dbo.v060ClubOfficersPresOrNot ?
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
August 18, 2015 at 11:26 am
The Club info is in that table (or View)
The attribute.PersonMembership is the one with the invoices (membership info)
August 18, 2015 at 12:51 pm
Do not use your filter in the where clause. Even though you have a right join to bring in all of the clubid columns. the where clause will filter it out by when bringing in the date time constraints. I think you should use a case statement for the column in question in the select statement. The below case statement may not fit what exactly you need but I hope it points you in the right direction.
Case When attribute.PersonMembership.EndDate <= @EndDate Then attribute.PersonMembership.EndDate
When attribute.PersonMembership.EndDate > @EndDate Then 0
else NULL
End
August 18, 2015 at 2:17 pm
serviceaellis (8/18/2015)
Thank you both ...however ...
ChrisM@Work,
yours is showing 2014.
and both are not showing ClubNo 5305 with a null membership info like ClubNo 8004.
NOTE: 5305 has memberships in 2015 and prior years.
8004 has none at all.
Need to see 5305 memberships in 2015 and the null row like 8004 with no membership.
I have to agree with ChrisM@Work ... if that view/table does not have a record for the clubs that don't appear, then joining to the memberships table isn't going to help. You may need to "de-construct" that view and eliminate any criteria it might have that keeps such clubs from appearing, and use the modified query that you create from that view as part of your query for this data. My query can be used to help you figure out if this might be necessary. Simply compare the results of my query as is, to the results of my query after removing the second condition of the LEFT OUTER JOIN. If you see no records for the "missing clubs" after running my query as modified, then the problem is probably inside that view.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
August 18, 2015 at 2:50 pm
Interesting someone came up with this, which shows the NULL memberships for these clubs BUT I'm missing the 15 memberships for the clubno 5305.
Can't seem to get the 15 membership for 5305 and no membership record for 8004 that you guy's formula is showing to show the no membership record for 5305 as well?
Can you modify this to show those 15 membership records for 5305 AND what this formula is showing?
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)
Parameters:
1. ALL Clubs must show
2. ALL Clubs with or without memberships must show
3. >=DateSerial(Year(GetDate()), 6,30) seems to show 2015-2019, which is what I need since MemberTypeID = 3 is a 4 year membership.
4. When the end-user enters 2015 in the EndYear prompt your formula is correct, showing:
5305 club info 15 memberships
8004 club info 0 memberships
5. When the end-user enters 2016 in the EndYear prompt your formula isn't showing but the formula above shows it.
HOWEVER neither formulas are set to limit to one year or the other. that is done on the end-user's side.
So trying to see how one or the other formula can include both results in one formula.
August 18, 2015 at 3:23 pm
Do you know what would really help? DDL for the tables/views involved, sample data that represents the problem domain (includes data that will be returned as well as some data that won't) for the tables involved, and expected results based on the sample data.
August 18, 2015 at 3:53 pm
Please see attached.
The worksheet is the result that needs to be produced with the highlighted row.
in the Access file the two tables are:
1. PersonMembership - with all the membership info
2. v060ClubOfficersPresOrNot - will all the club info which ALL of these should show and show when there is/are membership(s) or not.
3. for the duration of current year and future years where the MemberTypeID =3 is a 4 year membership.
Working with ClubNo 5305 and 8004 as these are the data sets that can be used to verify the correct result.
When the end-user enters 2015 they should see:
1. ClubNo 5305 club info with 15 memberships
2. ClubNo 8004 club info with 0 memberships
When the end-user enters 2016 they should see:
1. ClubNo 5305 club info with 0 memberships
2. ClubNo 8004 club info with 0 memberships
Viewing 15 posts - 1 through 15 (of 93 total)
You must be logged in to reply to this topic. Login to reply