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

EDIT: I missed several posts so this post of mine, as posted, became completely irrelevant.

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

serviceaellis (8/20/2015)

---

sgmunson (8/20/2015)

---

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

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

serviceaellis (8/20/2015)

---

sgmunson (8/20/2015)

---

Boy are we on two different pages. Why would you need a record for club 5305 that shows 0 memberships? And how does the...

serviceaellis (8/20/2015)

---

sgmunson (8/20/2015)

---

So how are you determining that my query doesn't do that? Please tell me you are NOT using Crystal Reports to make that verification. If...

serviceaellis (8/20/2015)

---

sgmunson,

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

I have an easy query, which I shared at the beginning that produces this result....

serviceaellis (8/20/2015)

---

sgmunson,

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

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

also, it cannot do the count for this details.

They will "drill-down"...

serviceaellis (8/20/2015)

---

ok, here's a snapshot before the changes requested.

You won't see 5305 or 8004 or 1617 b/c these clubs do...

Bruceo (8/20/2015)

---

In case you were wondering, this was the final solution.

The other scripts partially worked, if you tested with ID 198, it did not return the full list of relations,...

cwatson 81945 (8/19/2015)

---

Thanks ZZartin, that returned the correct results. Now I'll review it and make sure I understand what it's doing. Thanks again, I knew there had to...

serviceaellis (8/19/2015)

---

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

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)...

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