August 20, 2015 at 10:03 pm
Thank you!
Unfortunately I am getting an error. Syntax on GO
I'm using SSMS Express 2014.
? Odd, I did reply to the use of Crystal, also multiple times.
Here it is again, the end-user enters a specific year for the report whenever it is they are running it for.
Since a View is what you can link with CRW to run a report, I don't know why, plus my original post stating View ..., it wasn't a given that it needs to be remain a View.
It seems this to be an assumption on those that provided possible solutions not in View format. Considering the originating formula and was stated:
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.
As you can see the 1st 3 lines of my originating post states
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.
Then
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.
Which states that the View, which is the only way Crystal could link to that I know of, so again I do not know why solutions were not provided so Crystal can access it?
I also provided examples of the result expected with explanation for each example.
Can I explain this better? If so how, so I know in the future to be more clear from the beginning. Thank you
August 21, 2015 at 1:59 am
sgmunson (8/20/2015)
serviceaellis (8/20/2015)
sgmunson,thank you! This is closer than ever! It's producing the results in the detail format ...
Like J Livingston SQL came up with EXCEPT it was in a crosstab format which does not work.
AND it can't be saved as a View.
Which is what's also an issue with yours as well 🙁
As mentioned, it needs to be so it can be saved as a View that can be linked from CRW to report on.
And prefer to use >=DateFromParts(Year(GetDate()),6,30) on EndDate as it's cleaner and you don't have to Declare anything.
So excited it's closer than ever!
Just needs to be savable in View so it can be used in a report tool for the end user this is for.
If you need a view, then you can't have a parameter in Crystal that's going to do you any good unless the view ignores date ranges entirely. Are you sure you can't just use a stored procedure? Also, if you had mentioned you needed a view back in your original post, we might have gotten there are a lot sooner... It seems like I'm getting spoon fed a bite at a time, and that is not a good way to win friends and influence people, ... at least not on this site anyway, and probably not anywhere else either. Please keep in mind: that is the impression you are creating...
Crystal works just fine with parameterised stored procedures, and this statement isn't a guess.
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 21, 2015 at 5:57 am
Which states that the View, which is the only way Crystal could link to that I know of, so again I do not know why solutions were not provided so Crystal can access it?
suggest that the following article may explain about using stored procedures in Crystal
http://www.pdssoftware.com/newsletter/mar07/page6.htm
from rereading this thread, I believe ....please correct me if I am wrong ...that some of the confusion occurred around your comments like "The report is ran using Crystal Reports with a EndYear parameter where the end-user enters the membership ending year."
It appears your original SQL view is based on delivering data filtered on YEAR(GETDATE()) and similar calcs using GETDATE()...so i can only assume that the "parameter" Endyear is just filtering the Crystal recordsource created by your SQL view.......you are not passing any parameters from Crystal to SQL. Is this correct?
so moving on please and for my own curiosity what data are you expecting to get for Club 5305 when this report is run on 1 Jan 2016?
edit >typo
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
August 21, 2015 at 6:55 am
serviceaellis (8/20/2015)
Thank you!Unfortunately I am getting an error. Syntax on GO
I'm using SSMS Express 2014.
? Odd, I did reply to the use of Crystal, also multiple times.
Here it is again, the end-user enters a specific year for the report whenever it is they are running it for.
Since a View is what you can link with CRW to run a report, I don't know why, plus my original post stating View ..., it wasn't a given that it needs to be remain a View.
It seems this to be an assumption on those that provided possible solutions not in View format. Considering the originating formula and was stated:
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.
As you can see the 1st 3 lines of my originating post states
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.
Then
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.
Which states that the View, which is the only way Crystal could link to that I know of, so again I do not know why solutions were not provided so Crystal can access it?
I also provided examples of the result expected with explanation for each example.
Can I explain this better? If so how, so I know in the future to be more clear from the beginning. Thank you
I've known all along that you are using Crystal Reports, and as I've worked with that tool in various versions over the last 20 years, I can tell you with no guessing involved, that any recent version is quite capable of dealing with any of the code supplied so far, whether it's a view, a stored procedure with parameters, or even just a parameterized query. The problem here is that you insisted on hiding the details behind your use of Crystal because you didn't think it mattered. It DOES matter, and how you set things up in the query, view, or stored procedure is HIGHLY DEPENDENT on how you plan to parameterize your Crystal Report. Given my view code, I had no trouble sucessfully creating the view in a SQL 2008 database, and I used SSMS 2012 as installed by SQL Server 2012 Developer Edition. If GO is causing an issue, take it out and run without it. The view should still get created.
Here's how you can change the way you communicate to better explain your situation. Start by talking about WHY you are doing any particular thing in a query, and tell people what a record in any given table in your query represents. It's this "conceptual knowledge" that you repeatedly avoided getting into. Insisting that you know your data doesn't change the fact that with the data supplied, it appears most everyone had trouble getting it right. When you can't reproduce the same result someone else posted as successfuly getting, ask the question as "what am I doing wrong" instead of just telling everyone else that they have it wrong. You didn't explain at all that you NEEDED a view, just that you HAD one, and the name of the objects you were working with suggested that one of the two "tables" is already a view, so everyone reasonably assumed that the v060 object was the view you were talking about. Because we can't just look over your shoulder and see EXACTLY what you are doing, we have to make at least SOME assumptions, and those need to be questioned at times. Not everyone communicates the same way, and the same sentence, interpreted by several people, can end up meaning completely different things to every individual involved, so getting into detail has to be something one gets particularly anal about, or the likelihood of misunderstanding or miscommunication goes up rather quickly. I hope that helps. Have you tried running the query that is within the view I supplied as a standalone query in SSMS, and does it return the same results as I posted? I need a yes or no answer on that. Then, try pasting the query into Crystal, and remove the parameter entirely, and see what results you get. I've asked a number of times to know EXACTLY what the parameter in Crystal is supposed to do to change the resulting output, and I still don't have any answer. Please get into the details and explain exactly how you think it's supposed to work.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
August 21, 2015 at 8:24 am
Ah, yes, I did not know you can use Stored Procedure in Crystal.
As explained, Crystal is linking to a View on the SQL Server.
And was thinking saving the solution as a stored procedure in SQL Server and linking it to that. Which I do not know or anyone pointed this out that I recall.
I was trying to keep it working in the way I knew how, linking to the View in Crystal and wanting it to work that way since that's the way I was using.
I didn't see anything that suggested using it Crystal. Since all the why trying to see if any of the solutions is producing the results, I'm using the SQL Query and trying to save it as View.
It would have been tremendous help if someone mentioned that since I know I kept saying I can't save it as a View. And knowing that is the way I was working with this from the originating post and throughout.
There was lack of information and assumptions for sure.
I did explain what Clubs represents and what PersonMembership consists of ...?
Thank you for clarifying.
I will try the CREATE VIEW solution again.
I will also try in Crystal. Hopefully it goes well, i've have not worked with Stored Procedures in Crystal. Since they prefer Views too btw, so they can see or make changes easier as a layman.
August 21, 2015 at 9:20 am
J Livingston SQL,
Thank you for the link on Using SP!
This is also to sgmunson,
On the question of Crystal, again I thought I answered it.
The end-user enters the year they want to see membership counts for.
Their season starts on 7/1 and ends on 6/30. Memberships can be purchased throughout the year. So a couple of months ago in June, your membership is "Current". In July it is no longer current. They want the option to specify 2015 or 2016 and so on to see the number of memberships in various years. Using Report Select Expert. So the prompt is handled there.
NOTE: MembershipTypeID 3 is again good for 4 years so the counts of memberships must include memberships ending 4 years from the current year [2015+4, ending in 2019]
This was mentioned as well, that the grouping is done on the Crystal side. They need to see the details of each invoice for other reasons.
---
sgmunson,
THANK YOU! Not sure why I got the error last night on the CREATE VIEW but it worked today.
Ran it and it created it and looks to be producing the results.
I was trying to apply this on the Live server to test it and got an error on the EndYear.
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 your code.
There are a few and though it allowed me to do this here:
(SELECT P.OrganizationId, P.InvoiceNumber, Year(P.EndDate) AS EndYear, P.MembershipTypeId
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
August 21, 2015 at 11:02 am
serviceaellis (8/21/2015)
J Livingston SQL,Thank you for the link on Using SP!
This is also to sgmunson,
On the question of Crystal, again I thought I answered it.
The end-user enters the year they want to see membership counts for.
Their season starts on 7/1 and ends on 6/30. Memberships can be purchased throughout the year. So a couple of months ago in June, your membership is "Current". In July it is no longer current. They want the option to specify 2015 or 2016 and so on to see the number of memberships in various years. Using Report Select Expert. So the prompt is handled there.
NOTE: MembershipTypeID 3 is again good for 4 years so the counts of memberships must include memberships ending 4 years from the current year [2015+4, ending in 2019]
This was mentioned as well, that the grouping is done on the Crystal side. They need to see the details of each invoice for other reasons.
---
sgmunson,
THANK YOU! Not sure why I got the error last night on the CREATE VIEW but it worked today.
Ran it and it created it and looks to be producing the results.
I was trying to apply this on the Live server to test it and got an error on the EndYear.
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 your code.
There are a few and though it allowed me to do this here:
(SELECT P.OrganizationId, P.InvoiceNumber, Year(P.EndDate) AS EndYear, P.MembershipTypeIdbut 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 JOINCLUBS AS co LEFT OUTER JOIN
Memberships AS pm ON co.ClubID = pm.OrganizationId AND Y.THE_YEAR = pm.EndYear
I am not sure how many times you have edited the post above, but it is several...from when I first read this to now your post has changed...for example what the heck is "Using Report Select Expert"...only assume this is Crystal?.......by continuing to edit your previous posts and not indicating what changes you have made can lead to confusion to responses, especially when they are replying to a prior edit.
moving on again... I did ask, and will ask again please.....if you run Steve's query....which I believe you are happy with......on the 1st of Jan 2016...what results are you expecting for Club 5305
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
August 21, 2015 at 12:41 pm
J Livingston SQL (8/21/2015)
serviceaellis (8/21/2015)
J Livingston SQL,Thank you for the link on Using SP!
This is also to sgmunson,
On the question of Crystal, again I thought I answered it.
The end-user enters the year they want to see membership counts for.
Their season starts on 7/1 and ends on 6/30. Memberships can be purchased throughout the year. So a couple of months ago in June, your membership is "Current". In July it is no longer current. They want the option to specify 2015 or 2016 and so on to see the number of memberships in various years. Using Report Select Expert. So the prompt is handled there.
NOTE: MembershipTypeID 3 is again good for 4 years so the counts of memberships must include memberships ending 4 years from the current year [2015+4, ending in 2019]
This was mentioned as well, that the grouping is done on the Crystal side. They need to see the details of each invoice for other reasons.
I am not sure how many times you have edited the post above, but it is several...from when I first read this to now your post has changed...for example what the heck is "Using Report Select Expert"...only assume this is Crystal?.......by continuing to edit your previous posts and not indicating what changes you have made can lead to confusion to responses, especially when they are replying to a prior edit.
moving on again... I did ask, and will ask again please.....if you run Steve's query....which I believe you are happy with......on the 1st of Jan 2016...what results are you expecting for Club 5305
Yes, trying to keep the replies in one post and trying to provide information as much as I can in order reduce lack of information.
Not to mention trying to read all separate posts. It could be possible that I did not read each one's responses, etc ... I end up getting many notifications from the different posters.
I'm sorry, I didn't realize you do not know what Report Select Expert is.
In Crystal you go to the menu to Report, then the very first option is Select Expert. We're using Record with a Parameter Field for the year prompt.
{v060ClubOfficersPresidentMbrCounts.MembershipTypeId} in [1, 2, 4] AND
{v060ClubOfficersPresidentMbrCounts.YearEnd} = {?Enter Season Year} OR
{v060ClubOfficersPresidentMbrCounts.YearEnd} in {?Enter Season Year} to {?Enter Season Year}+3 AND
{v060ClubOfficersPresidentMbrCounts.MembershipTypeId} = 3
As i've mentioned membershiptypeid 3 is good for 4 years. so current year 2015+4, through 2019 is needed. Which was noted in several posts.
And the season duration. It's not the standard year but from July - June.
All memberships end on 6/30/yyyy.
Steve's seems to be working, in my test db environment. But due to the EndYear not being a field and I can't figure out how to edit it as posted, I'm not 100% positive to mark it as the Solution as of yet.
I didn't realize you didn't know what would be expected if it was ran in Jan 2016.
It would be the same thing, depends on what the user enters on the CRW side.
Most likely they'll want to see if the number of memberships ending in season 2016 has changed AND perhaps might want to see how many might have purchased 2017 memberships, etc ...
Hope that satisfies your question. Steve's latest solution, again seems to be producing in the details correctly. just need to fix the EndYear issue since that fields does not exist in PersonMembership and is a derived field from EndDate that is in the table.
Year(EndDate) AS EndYear is where the sample table I provided is rom.
August 21, 2015 at 12:48 pm
So, I am going to ask again, can (and does) EndDate have dates that AREN'T 06/30/YYYY where YYYY is any given year? If so, and you don't want those rows of data, please explain why.
August 21, 2015 at 1:25 pm
Steve's seems to be working, in my test db environment. But due to the EndYear not being a field and I can't figure out how to edit it as posted, I'm not 100% positive to mark it as the Solution as of yet.
I didn't realize you didn't know what would be expected if it was ran in Jan 2016.
It would be the same thing, depends on what the user enters on the CRW side.
Most likely they'll want to see if the number of memberships ending in season 2016 has changed AND perhaps might want to see how many might have purchased 2017 memberships, etc ...
far from for me to question your rationale....but if I run Steve's code with a date of 1st Jan 2016 and for club 5305
these are the rows returned.....I am not sure this is what you are expecting???
+--------------------------------------------------------------+
¦ ClubNo ¦ ClubName ¦ YearEnd ¦ MembershipTypeId ¦
¦--------+------------------------+---------+------------------¦
¦ 5305 ¦ Shoreline Skating Club ¦ 2016 ¦ NULL ¦
¦ 5305 ¦ Shoreline Skating Club ¦ 2017 ¦ NULL ¦
¦ 5305 ¦ Shoreline Skating Club ¦ 2018 ¦ NULL ¦
¦ 5305 ¦ Shoreline Skating Club ¦ 2019 ¦ NULL ¦
¦ 5305 ¦ Shoreline Skating Club ¦ 2020 ¦ NULL ¦
+--------------------------------------------------------------+
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
August 21, 2015 at 1:35 pm
J Livingston SQL (8/21/2015)
Steve's seems to be working, in my test db environment. But due to the EndYear not being a field and I can't figure out how to edit it as posted, I'm not 100% positive to mark it as the Solution as of yet.
I didn't realize you didn't know what would be expected if it was ran in Jan 2016.
It would be the same thing, depends on what the user enters on the CRW side.
Most likely they'll want to see if the number of memberships ending in season 2016 has changed AND perhaps might want to see how many might have purchased 2017 memberships, etc ...
far from for me to question your rationale....but if I run Steve's code with a date of 1st Jan 2016 and for club 5305
these are the rows returned.....I am not sure this is what you are expecting???
+--------------------------------------------------------------+¦ ClubNo ¦ ClubName ¦ YearEnd ¦ MembershipTypeId ¦
¦--------+------------------------+---------+------------------¦
¦ 5305 ¦ Shoreline Skating Club ¦ 2016 ¦ NULL ¦
¦ 5305 ¦ Shoreline Skating Club ¦ 2017 ¦ NULL ¦
¦ 5305 ¦ Shoreline Skating Club ¦ 2018 ¦ NULL ¦
¦ 5305 ¦ Shoreline Skating Club ¦ 2019 ¦ NULL ¦
¦ 5305 ¦ Shoreline Skating Club ¦ 2020 ¦ NULL ¦
+--------------------------------------------------------------+
I'm not sure what our disconnect is 🙁
steve's code is producing the details requested except the issue with EndYear field not being an actual field in the PersonMembership so the formula is erroring when attempting to apply to the Live system. Since the sample table included the derived field when I shared it.
So other than being stuck with that at the moment, all is good in the test db environment.
And as long as those update if memberships are paid for those years later on. Right now it's NULL (AKA: do not have any memberships for those years).
Sorry, it seems that when I post an issue that comes up with a solution, I'm asked something and it's confusing rather than the issue I'm inquiring on.
And some questions do not really apply here due to the filters listed in order to get the results sought. Perhaps that could be another reason I may not have answered it because it doesn't affect what's sought based on the filters that needs to be in place. RE: 6/30/yyyy. All other EndDates do not matter for this.
August 21, 2015 at 1:46 pm
Lynn Pettis (8/21/2015)
So, I am going to ask again, can (and does) EndDate have dates that AREN'T 06/30/YYYY where YYYY is any given year? If so, and you don't want those rows of data, please explain why.
Well, apparently the respond didn't submit? I'm not seeing it.
So to be sure you get a response on this question ...
Yes, there are other EndDate than 6/30/yyyy. Those would be quits/transfers and should not be in this. Hence the filter on EndDate to limit only 6/30/yyyy information.
August 22, 2015 at 6:02 am
EDIT: I missed several posts so this post of mine, as posted, became completely irrelevant.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
August 22, 2015 at 6:16 am
serviceaellis (8/21/2015)
J Livingston SQL,Thank you for the link on Using SP!
This is also to sgmunson,
On the question of Crystal, again I thought I answered it.
The end-user enters the year they want to see membership counts for.
Their season starts on 7/1 and ends on 6/30. Memberships can be purchased throughout the year. So a couple of months ago in June, your membership is "Current". In July it is no longer current. They want the option to specify 2015 or 2016 and so on to see the number of memberships in various years. Using Report Select Expert. So the prompt is handled there.
NOTE: MembershipTypeID 3 is again good for 4 years so the counts of memberships must include memberships ending 4 years from the current year [2015+4, ending in 2019]
This was mentioned as well, that the grouping is done on the Crystal side. They need to see the details of each invoice for other reasons.
---
sgmunson,
THANK YOU! Not sure why I got the error last night on the CREATE VIEW but it worked today.
Ran it and it created it and looks to be producing the results.
I was trying to apply this on the Live server to test it and got an error on the EndYear.
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 your code.
There are a few and though it allowed me to do this here:
(SELECT P.OrganizationId, P.InvoiceNumber, Year(P.EndDate) AS EndYear, P.MembershipTypeIdbut 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 JOINCLUBS AS co LEFT OUTER JOIN
Memberships AS pm ON co.ClubID = pm.OrganizationId AND Y.THE_YEAR = pm.EndYear
Okay, now we have something to work with. Here's the modified view, which only needs to incorporate a change for the first of those two listed code segments:
CREATE VIEW dbo.VW_ClubsAndMemberships
AS
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, DATEADD(year, 1, END_DATE)
FROM YEARS
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) = Y.THE_YEAR
AND P.EndDate = Y.END_DATE
)
SELECT TOP 100 PERCENT 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);
GO
SELECT *
FROM dbo.VW_ClubsAndMemberships
It produces the same results as before. Having seen all the posts now, as well as your selection formula, I honestly think you should dispose of all of that. There's no need to have a parameter with this particular view, so unless you have some clubs getting memberships paid for well into the future, I doubt it would really make much sense.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
August 22, 2015 at 7:07 pm
Thank you very much!
Data seems to be resulting the way it's expected on the reporting side with an edit in the Report | Select Expert on Record.
{v060ClubOfficersPresidentMbrCounts.YearEnd} in {?Enter Season Year} to {?Enter Season Year}+4
The end-user needs to see for the year they are wanting to get the total memberships for.
Formula on YearEnd to show between entered Year and Year+4. So that the totals will show for those membershiptypeid = 3. Since they are 4 year memberships. If entered 2016, the total from 2016-2019 is required to report to show "current" memberships. Example clubno 22.
Total from 2015-2019 is 82. Total from 2016-2019 is 42. and son.
Without the YearEnd prompt they'd just see 82 until Jan 2016. Which isn't really correct for what they are looking for.
Oh and I had to add back this to the Join to the updated View. Somewhere it got left out. There are several membership types and need to filter on these only. In the sample data set, these were the only ones in it 🙂
AND P.MembershipTypeId IN (1,2,3,4)
Thanks again for your patience and working with me to the very end. Hopefully nothing comes up. Whew!
Viewing 15 posts - 76 through 90 (of 93 total)
You must be logged in to reply to this topic. Login to reply