To summarize issue,

when the formula is changed to handle >=EndYear it does not produce the correct information for ClubNo 5305.

the correct information is only produced when doing an =EndYear.

The details...

Jacob Wilkins (8/20/2015)

---

My apologies, but I'm not really following what you're saying.

Perhaps it would be best if you posted a small sample data set for this thread that illustrates the...

Lynn Pettis (8/20/2015)

---

First, can EndDate have any date other than YYYY/06/30? If not and you want all dates greater than or equal to 2015/06/30 why aren't you just doing...

SELECT c.ClubID, 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.PersonId, p.MembershipTypeId

FROM dbo.v060ClubOfficersPresOrNot AS...

Jacob Wilkins (8/20/2015)

---

I might be missing something, but it doesn't seem so mysterious why those don't return the same results.

I'm assuming here that you're actually comparing the second version to...

ChrisM@Work (8/20/2015)

---

The single most important piece of information here, without which every answer can only be a guess, is the value returned to @yr by...

J Livingston SQL (8/19/2015)

---

I am confused.....

your original post mentions

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

They are entering to...

for ClubNo 5305 or ClubID ending in 4619 has 16 memberships for 2015 and 0 for 2016

for ClubNo 8004, it has 0 for 2015 and 0 for 2016

how do you...

? any memberships not ending in 6/30/yyyy are not relevant for this, hence the limit to 6/30/yyyy.

the membership count for 5305 does not include EndDate other than 6/30/2015.

Seems to be...

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

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

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

lol

yes on the date range.

not sure what you're asking on the GetDate()

other then using it with Year in order to get the current year by using Year(GetDate())

the issue i'm having...

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

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