August 19, 2015 at 2:23 pm
Ugh, just realized that the DECLARE states only 2015.
I changed it to 2016 and is in fact seeing 5305 with NULL values in the membership fields.
What I was looking for. Thank you!
I think I can work with what's there now to include all the fields needed.
August 19, 2015 at 2:27 pm
J Livingston SQL (8/19/2015)
as a starter....does this get you the required results....??
DECLARE @yr AS int;
SET @yr = 2015;
WITH cteclub
AS (
SELECT DISTINCT
ClubID
, ClubNo
FROM v060ClubOfficersPresOrNot
)
,
ctebydate
AS (
SELECT
OrganizationId
, PersonId
, EndDate
, MembershipTypeId
FROM PersonMembership AS p
WHERE (YEAR(EndDate) >= @yr)
AND (MONTH(EndDate) = 6)
AND (DAY(EndDate) = 30)
)
SELECT
c.ClubID
, c.ClubNo
, cd.OrganizationId
, cd.PersonId
, cd.EndDate
, cd.MembershipTypeId
FROM cteclub AS c LEFT OUTER JOIN
ctebydate AS cd ON c.ClubID = cd.OrganizationId
WHERE (c.ClubNo = 5305)
ORDER BY
c.ClubNo;
Yes! And when I changed it to 2016 I am getting 5305 with null membership info. What I was looking for AND the membership info when it's declared 2015.
Now to incorporate what you've provided the rest of the fields. Assuming I can just remove the DECLARE portion and add (YEAR(EndDate) >>=Year(GetDate())?
August 19, 2015 at 2:27 pm
serviceaellis (8/19/2015)
got on to the SQL Server.ok, 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+4. 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 + 4
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 were current and not current now or didn't have any like 8004.
Okay, now that I've had a chance to consume the data that JLivingstonSQL posted that appears to be the same data that was in the Access database you posted, I've found that chances are, what you are looking for in the way of a result probably shouldn't be coming from that view without a rather large GROUP BY. That view appears to be appropriately named, because it lists ALL the club officers, so just joining it to the memberships doesn't really work very well because each and every club record then joins to each and every membership record, and the results are wasteful, if nothing else.
You really should be going after a Club table whose primary key is the ORGxxxxxxx value or the ClubId, instead of this view. However, given the data, I was able to add a GROUP BY to my query that ends up outputting 17 records, including clubs 22, 1617, 5305, and 8004. Let me know if this improves your results:
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,
MAX(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 dbo.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)
GROUP BY COP.ClubNo, COP.SortName, COP.ClubName, COP.BSProgram, COP.ClubSection, COP.Code,
COP.President, COP.Email, COP.Phone, COP.FacilityName, COP.StreetOne, COP.StreetTwo, COP.City,
COP.[State], COP.PostalCode, YEAR(PM.EndDate), PM.MembershipTypeId, COP.URL, COP.Certified,
COP.FacilityLastUpdate, COP.ByLawsUploadDate, COP.ProgramStatusId, COP.[Status]
ORDER BY COP.ClubNo, YEAR(PM.EndDate);
Here are my results:
ClubNoSortNameClubNameBSProgramClubSectionCodeInvNoPresidentEmailPhoneFacilityNameStreetOneStreetTwoCityStatePostalCodeYearEndMembershipTypeIdURLCertifiedFacilityLastUpdateByLawsUploadDateProgramStatusIdStatus
22ADIRONDACKSSC of the Adirondacks, Inc.YesEasternNA0032860Jeanette Woodruffchristma@plattsburgh.eduNULLStafford ArenaRuger StNULLPlattsburgNY1290120151NULLYes2014-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.YesEasternNA0039130Jeanette Woodruffchristma@plattsburgh.eduNULLStafford ArenaRuger StNULLPlattsburgNY1290120154NULLYes2014-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.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.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.YesEasternNA0029828Jeanette Woodruffchristma@plattsburgh.eduNULLStafford ArenaRuger StNULLPlattsburgNY1290120183NULLYes2014-07-06 17:16:00.000NULL3Active
1617NULLCedar Valley FSCNoMidwesternNULL0030959NULLNULLNULLNULLNULLNULLNULLNULLNULL20151NULLNULLNULLNULLNULLNULL
1617NULLCedar Valley FSCNoMidwesternNULL0030959NULLNULLNULLNULLNULLNULLNULLNULLNULL20152NULLNULLNULLNULLNULLNULL
1617NULLCedar Valley FSCNoMidwesternNULL0021158NULLNULLNULLNULLNULLNULLNULLNULLNULL20154NULLNULLNULLNULLNULLNULL
1617NULLCedar Valley FSCNoMidwesternNULL0056033NULLNULLNULLNULLNULLNULLNULLNULLNULL20161NULLNULLNULLNULLNULLNULL
1617NULLCedar Valley FSCNoMidwesternNULL0056033NULLNULLNULLNULLNULLNULLNULLNULLNULL20162NULLNULLNULLNULLNULLNULL
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
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
August 19, 2015 at 4:53 pm
serviceaellis (8/19/2015)
J Livingston SQL (8/19/2015)
as a starter....does this get you the required results....??
DECLARE @yr AS int;
SET @yr = 2015;
WITH cteclub
AS (
SELECT DISTINCT
ClubID
, ClubNo
FROM v060ClubOfficersPresOrNot
)
,
ctebydate
AS (
SELECT
OrganizationId
, PersonId
, EndDate
, MembershipTypeId
FROM PersonMembership AS p
WHERE (YEAR(EndDate) >= @yr)
AND (MONTH(EndDate) = 6)
AND (DAY(EndDate) = 30)
)
SELECT
c.ClubID
, c.ClubNo
, cd.OrganizationId
, cd.PersonId
, cd.EndDate
, cd.MembershipTypeId
FROM cteclub AS c LEFT OUTER JOIN
ctebydate AS cd ON c.ClubID = cd.OrganizationId
WHERE (c.ClubNo = 5305)
ORDER BY
c.ClubNo;
Yes! And when I changed it to 2016 I am getting 5305 with null membership info. What I was looking for AND the membership info when it's declared 2015.
Now to incorporate what you've provided the rest of the fields. Assuming I can just remove the DECLARE portion and add (YEAR(EndDate) >>=Year(GetDate())?
Apparently you cannot use a > because SET @yr = 2015 needs to be SET @yr>=Year(GetDate())
And is Declarations necessary? I cannot see the grid pane view with these statements.
I removed the Declarations from
WHERE (YEAR(EndDate) >= @yr)
AND (MONTH(EndDate) = 6)
AND (DAY(EndDate) = 30)
and created Function to replicate DateSerial() from Access. This has been verified and works great. Then used that in the WHERE.
Back to where I started. I do not get the 5305 null record like 8004.
August 19, 2015 at 4:58 pm
sgmunson (8/19/2015)
Okay, now that I've had a chance to consume the data that JLivingstonSQL posted that appears to be the same data that was in the Access database you posted, I've found that chances are, what you are looking for in the way of a result probably shouldn't be coming from that view without a rather large GROUP BY. That view appears to be appropriately named, because it lists ALL the club officers, so just joining it to the memberships doesn't really work very well because each and every club record then joins to each and every membership record, and the results are wasteful, if nothing else.
You really should be going after a Club table whose primary key is the ORGxxxxxxx value or the ClubId, instead of this view. However, given the data, I was able to add a GROUP BY to my query that ends up outputting 17 records, including clubs 22, 1617, 5305, and 8004. Let me know if this improves your results:
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,
MAX(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 dbo.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)
GROUP BY COP.ClubNo, COP.SortName, COP.ClubName, COP.BSProgram, COP.ClubSection, COP.Code,
COP.President, COP.Email, COP.Phone, COP.FacilityName, COP.StreetOne, COP.StreetTwo, COP.City,
COP.[State], COP.PostalCode, YEAR(PM.EndDate), PM.MembershipTypeId, COP.URL, COP.Certified,
COP.FacilityLastUpdate, COP.ByLawsUploadDate, COP.ProgramStatusId, COP.[Status]
ORDER BY COP.ClubNo, YEAR(PM.EndDate);
Here are my results:
ClubNoSortNameClubNameBSProgramClubSectionCodeInvNoPresidentEmailPhoneFacilityNameStreetOneStreetTwoCityStatePostalCodeYearEndMembershipTypeIdURLCertifiedFacilityLastUpdateByLawsUploadDateProgramStatusIdStatus
22ADIRONDACKSSC of the Adirondacks, Inc.YesEasternNA0032860Jeanette Woodruffchristma@plattsburgh.eduNULLStafford ArenaRuger StNULLPlattsburgNY1290120151NULLYes2014-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.YesEasternNA0039130Jeanette Woodruffchristma@plattsburgh.eduNULLStafford ArenaRuger StNULLPlattsburgNY1290120154NULLYes2014-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.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.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.YesEasternNA0029828Jeanette Woodruffchristma@plattsburgh.eduNULLStafford ArenaRuger StNULLPlattsburgNY1290120183NULLYes2014-07-06 17:16:00.000NULL3Active
1617NULLCedar Valley FSCNoMidwesternNULL0030959NULLNULLNULLNULLNULLNULLNULLNULLNULL20151NULLNULLNULLNULLNULLNULL
1617NULLCedar Valley FSCNoMidwesternNULL0030959NULLNULLNULLNULLNULLNULLNULLNULLNULL20152NULLNULLNULLNULLNULLNULL
1617NULLCedar Valley FSCNoMidwesternNULL0021158NULLNULLNULLNULLNULLNULLNULLNULLNULL20154NULLNULLNULLNULLNULLNULL
1617NULLCedar Valley FSCNoMidwesternNULL0056033NULLNULLNULLNULLNULLNULLNULLNULLNULL20161NULLNULLNULLNULLNULLNULL
1617NULLCedar Valley FSCNoMidwesternNULL0056033NULLNULLNULLNULLNULLNULLNULLNULLNULL20162NULLNULLNULLNULLNULLNULL
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
Thank you for trying to help. Everyone's attempt is greatly appreciated!!
Issue here.
I want to see full details of ALL the Clubs from the v060ClubOfficersPresOrNot
then full details of any memberships the Club has from all the fields from v060ClubOfficersPresOrNot.
I have no problems grouping and doing the count on how it needs to be done. I'm trying to work out getting the full details from both files meeting the criteria explained.
That said the ClubNo 5305 must show 15 membership records detail for EndYear 2015 and 0 membership record of Club info for EndYear 2016 like it does for ClubNo 8004.
I do not know how many different ways to state this very important piece that I'm struggling with.
August 19, 2015 at 5:13 pm
I do have a Function I am using now that mimics the DateSeial() in Access using this:
CREATE FUNCTION dbo.DateSerial
(
@year int ,
@month int ,
@day bigint
)
RETURNS datetime
AS
BEGIN
DECLARE @date datetime
-- catch invalid year entries and default appropriately
SET @year =
CASE WHEN @year < 1900 THEN 1900
WHEN @year > 9999 THEN year(getdate())
ELSE @year
END
-- convert date by adding together like yyyymmdd
SET @date = cast(@year * 10000 + 101 AS char(8))
;
-- Add to date the proper months subtracting 1,
-- since we used 1 as start instead of zero.
SET @date = dateadd(mm , @month - 1 , @date)
-- Add to date the proper days subtracting 1,
-- since we used 1 as start instead of zero.
SET @date = dateadd(dd , @day - 1 , @date)
;
RETURN @date ;
END
;
then
>= dbo.DateSerial(YEAR(GETDATE()), 6, 30)
on EndDate.
It works great in another View ... but
I tried to incorporate that into your formula for the date issue but failing.
Can you use this in place of the Declare?
August 19, 2015 at 8:38 pm
serviceaellis (8/19/2015)
Thank you for trying to help. Everyone's attempt is greatly appreciated!!
Issue here.
I want to see full details of ALL the Clubs from the v060ClubOfficersPresOrNot
then full details of any memberships the Club has from all the fields from v060ClubOfficersPresOrNot.
I have no problems grouping and doing the count on how it needs to be done. I'm trying to work out getting the full details from both files meeting the criteria explained.
That said the ClubNo 5305 must show 15 membership records detail for EndYear 2015 and 0 membership record of Club info for EndYear 2016 like it does for ClubNo 8004.
I do not know how many different ways to state this very important piece that I'm struggling with.
Okay, but I have to ask WHY ??? Do you realize that the view you are using has one record for each individual club officer, and thus any given club may have multiple records in that view? Because you have that scenario going on, and then you join to the memberships table, where you may have multiple qualifying memberships, you'll get a rather crazy mishmash of records, as for each club, there will be a number of records equal to the product of the number of officers times the number of memberships, unless you perform some kind of grouping. I don't understand what the value is in displaying information that way. As I have a roller-skating background, I have a pretty good idea of what an Ice Skating club is all about, and I can't see how the number of officers should have any bearing on the number of membership records you seek, so just trying to combine that view and that table just doesn't make any sense. It may make some sense to potentially grab each officers name and stuff it into a comma delimited field in a single record for a given club, and then the join to the memberships won't get so crazy, and given that you seem to have up to 5 different years that could come into play for a given club, perhaps even doing the same for the club membership years as well. The question is why you actually need to display the membership types? Strangely, some clubs seem to have multiple membership types for the same year. If you could explain that background, it would be a LOT easier to figure out what might be the best way to achieve your goal. Right now, what you seem to want just doesn't make any logical sense. Please educate me and it will be a lot easier for me to figure out the how.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
August 19, 2015 at 8:47 pm
sgmunson,
Each person could have an individual membership in the Club on the same invoice by different MemberTypeID. Where as MemberTypeID = 3, their memberships is good for 3 years. Hence as shown in the original post of the +3 on the Year(EndDate) for this member type.
Once I have the full details I then do my final step to group in order to get the proper count of invoices for the Club.
Hope that makes sense? What I know is those are the fields that needs to be grouped on when linked to the memberships table in order to get the correct count of memberships.
And at the same time include those without memberships.
It just so odd to me that 8004 does show where they don't have memberships in 2015 AND 2016 while 5305 has memberships in 2015 and none in 2016 BUT won't show and yet 8004 does show??
so for the grouping I need these fields in order to get the correct count.
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
,p.PersonId
,rtrim(p.InvoiceNumber) as InvNo
,p.MembershipTypeId
FROM v060ClubOfficersPresOrNot AS c LEFT OUTER JOIN
attribute.PersonMembership AS p
GROUP BY 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
Order by c.clubno, p.personid
with the parameters for date range and MemberTypeIDs
You'll notice that the club has repeated invoices but with different PersonID. As mentioned one invoice could be for several position in the club.
August 19, 2015 at 9:08 pm
serviceaellis (8/19/2015)
sgmunson,Each person could have an individual membership in the Club on the same invoice by different MemberTypeID. Where as MemberTypeID = 3, their memberships is good for 3 years. Hence as shown in the original post of the +3 on the Year(EndDate) for this member type.
Once I have the full details I then do my final step to group in order to get the proper count of invoices for the Club.
Hope that makes sense? What I know is those are the fields that needs to be grouped on when linked to the memberships table in order to get the correct count of memberships.
And at the same time include those without memberships.
It just so odd to me that 8004 does show where they don't have memberships in 2015 AND 2016 while 5305 has memberships in 2015 and none in 2016 BUT won't show and yet 8004 does show??
Okay, so you're saying that the membership is associated with the club officer, and not the club itself, right? If that's the case, then how do we link the individual officer with a given membership, as the join so far is only based on the club identifier ? I tried using PersonId, but that fails to link to more than 1 membership for club 5305, as follows in terms of the count:
22 - 10 records
1617 - 5 records
5305 - 1 record
8004 - 4 records (all NULL for memberships, because there are 4 officers in that club, none of whom have memberships)
Based on what I'm seeing now, this appears to be a data problem, in that the individual club officers are not necessarily linked to the membership table by PersonId, OR, using this view just isn't the right way to go about this. Let us know...
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
August 19, 2015 at 9:13 pm
No, you can't link on personid from the Club side. This is because this list is of Officers and NOT all members in the Club.
However your count by club for number of officers in the club is correct
Okay, so you're saying that the membership is associated with the club officer, and not the club itself, right? If that's the case, then how do we link the individual officer with a given membership, as the join so far is only based on the club identifier ? I tried using PersonId, but that fails to link to more than 1 membership for club 5305, as follows in terms of the count:
22 - 10 records
1617 - 5 records
5305 - 1 record
8004 - 4 records (all NULL for memberships, because there are 4 officers in that club, none of whom have memberships)
Based on what I'm seeing now, this appears to be a data problem, in that the individual club officers are not necessarily linked to the membership table by PersonId, OR, using this view just isn't the right way to go about this. Let us know...
You just get all the memberships based on the ClubID to OrganizationID then group it by the invoice, personid, and membertypeid from the personmembership table in order to get the proper count of memberships for the club.
That works. Believe me it's been individually verified enough know that is HOW it has to be done.
Issue here is not getting the correct data when doing a range with the formulas provided when trying to do the date range.
August 19, 2015 at 9:23 pm
Another possibility is that the data just doesn't agree with what you're expecting for results, ... possibly because the folks asking you to get this information don't understand it well enough themselves... but that's all just speculation at this point.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
August 19, 2015 at 9:35 pm
sgmunson (8/19/2015)
Another possibility is that the data just doesn't agree with what you're expecting for results, ... possibly because the folks asking you to get this information don't understand it well enough themselves... but that's all just speculation at this point.
lol! certainly that could be the case and I've tested this theory.
alas, it seems possible but for some reason it hasn't been figured out with the date range.
Only when on individual year at a time.
Hopefully someone can figure out how to make it work with the >=Year(GetDate()) on EndYear where I got the data that shows when the EndYear = 2015 as well as when it = 2016 for 5305.
8004 shows in both.
Again, the difference being 8004 has no data in either year whereas 5305 has data in 2015 but not in 2016.
August 19, 2015 at 9:36 pm
serviceaellis (8/19/2015)
No, you can't link on personid from the Club side. This is because this list is of Officers and NOT all members in the Club.However your count by club for number of officers in the club is correct
Okay, so you're saying that the membership is associated with the club officer, and not the club itself, right? If that's the case, then how do we link the individual officer with a given membership, as the join so far is only based on the club identifier ? I tried using PersonId, but that fails to link to more than 1 membership for club 5305, as follows in terms of the count:
22 - 10 records
1617 - 5 records
5305 - 1 record
8004 - 4 records (all NULL for memberships, because there are 4 officers in that club, none of whom have memberships)
Based on what I'm seeing now, this appears to be a data problem, in that the individual club officers are not necessarily linked to the membership table by PersonId, OR, using this view just isn't the right way to go about this. Let us know...
You just get all the memberships based on the ClubID to OrganizationID then group it by the invoice, personid, and membertypeid from the personmembership table in order to get the proper count of memberships for the club.
That works. Believe me it's been individually verified enough know that is HOW it has to be done.
Issue here is not getting the correct data when doing a range with the formulas provided when trying to do the date range.
Well, the following MIGHT be a part of the problem:
SELECT DISTINCT EndDate
FROM dbo.PersonMembership
with results:
EndDate
2015-02-05 00:00:00.000
2015-03-25 00:00:00.000
2015-04-13 00:00:00.000
2015-06-03 00:00:00.000
2015-06-30 00:00:00.000
2016-06-30 00:00:00.000
2017-06-30 00:00:00.000
2018-06-30 00:00:00.000
2019-06-30 00:00:00.000
Note the dates that are NOT having June 30th in them...
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
August 19, 2015 at 9:41 pm
Hence needing to limit to 6/30/yyyy
but that's not the problem however.
since the count on 2015 for 5305 is correct as well as it showing up like 8004 when changed to 2016
August 19, 2015 at 9:42 pm
And consider the following, so you'll know where the data is coming from:
SELECT DISTINCT PM.OrganizationId AS ClubID, CO.ClubNo, PM.EndDate
FROM dbo.PersonMembership AS PM
LEFT OUTER JOIN (SELECT DISTINCT ClubID, ClubNo FROM dbo.v060ClubOfficersPresOrNot) AS CO
ON PM.OrganizationId = CO.ClubID
WHERE YEAR(EndDate) = 2015
AND MONTH(EndDate) <> 6
Results:
ClubIDClubNoEndDate
ORG00000082NULL2015-04-13 00:00:00.000
ORG0000020116172015-02-05 00:00:00.000
ORG0000461953052015-03-25 00:00:00.000
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 15 posts - 31 through 45 (of 93 total)
You must be logged in to reply to this topic. Login to reply