1st Time?

I think it's working.
Giving the same results.

Nope, sorry, it's not :frowning:

It's pulling in records for competitors who have competed before.

They can't have a record prior to the current season to be considered 1st Time Competitors.

Do you think another approach?
records in the current season but previously / historically has no record.

can you post your latest query ? with some sample data of various scenario ?

SELECT CompetitionName, CompetitionCategory, EventType, SkaterMbrNo, FirstName, LastName, Email, HomeClub, PartnerFName, PartnerLName, PartnerEmail, PartnerHomeClub, SkaterID, CASE WHEN EndDate <= DateFromParts(year(GetDate()), 6, 30) THEN DateFromParts(year(GetDate()), 6, 30) ELSE DateFromParts(year(GetDate()) + 1, 6, 30) END AS EndDate FROM dbo.v004CompetitionRegistrations AS CompCur WHERE (NOT EXISTS (SELECT * FROM dbo.v004CompetitionRegistrations AS CompHist WHERE (EndDate <= DateFromParts(YEAR(GETDATE()), 6, 30)) AND (SkaterID = CompCur.SkaterID))) AND (CASE WHEN EndDate <= DateFromParts(year(GetDate()), 6, 30) THEN DateFromParts(year(GetDate()), 6, 30) ELSE DateFromParts(year(GetDate()) + 1, 6, 30) END >= DateFromParts(YEAR(GETDATE()), 6, 30))

It seems like it's working. There are over 2k registrations for competitions in the current season and this code is producing 622 records as being "1st Timers" HOWEVER some are not. They have prior year registrations?
Can't figure out why these few are showing.

So this competitor is showing up BUT she has prior years of competition registrations:

2016 Midwestern Sectional ChampionshipsSectionalsIntermediate DanceSC of Novi8695197IsabellaAmoiaCja1729@excite.comFemaleNULLCoryFraimanmyangelplus3@gmail.comMalePhiladelphia SC & HS2015 - 20162015-07-012016-06-30

HOLD

Looking at the details further and just the registration tables on that personID it stores, Isabella only registered once to compete.

The registrations is NOT her.

I looked back on her person record for the last 10 years.

So perhaps their criteria will change with this discovery. Thanks. Will report back.

try this

SELECT      CompetitionName, CompetitionCategory, EventType, SkaterMbrNo, FirstName, LastName, Email, HomeClub, PartnerFName, PartnerLName, PartnerEmail, 
            PartnerHomeClub, SkaterID, 
        -- Changes here
        CASE     WHEN GetDate() <= DateFromParts ( year( GetDate() ), 6, 30)
                 THEN DateFromParts ( year( GetDate() ), 6, 30)
                 ELSE DateFromParts ( year( GetDate() ) + 1, 6, 30)
                 END AS EndDate
FROM            dbo.v004CompetitionRegistrations AS CompCur
WHERE            (
            NOT EXISTS
                        (
                SELECT      *
                                   FROM            dbo.v004CompetitionRegistrations AS CompHist
                                   WHERE            (
                            -- Changes here
                            EndDate <  CASE WHEN GetDate() >= DateFromParts ( year( GetDate() ), 7, 1)
                                            THEN DateFromParts ( year( GetDate() ), 7, 1)
                                             ELSE DateFromParts ( year( GetDate() ) - 1, 7, 1)
                                             END
                        )
                AND         (
                            SkaterID = CompCur.SkaterID
                        )
            )
        ) 
AND         (
            -- Changes here
            StartDate     >= CASE    WHEN GetDate() >= DateFromParts ( year( GetDate() ), 7, 1)
                                    THEN DateFromParts ( year( GetDate() ), 7, 1)
                                    ELSE DateFromParts ( year( GetDate() ) - 1, 7, 1)
                                    END
        )

Ok, the issue:
Isbella was showing as 1st Timer even though she is not really.
Because she has competed as a Partner with another competitor in the past.

The Partner fields in the code.

So now, how to exclude the SkaterID if it also exists as a PartnerID if the person competed as a Partner??

I did another View but to get the Partner's info with the dates.
Then did a UNION ALL.
It takes a long time to process.

If there's a better, more efficiently, please post.

in the NOT EXISTS query

AND     (
              SkaterID = CompCur.SkaterID
        OR    PartnerID = CompCur.SkaterID
        )

Hi there,
Ok, so for some reason this partner is NOT showing up and she should.

SELECT CompetitionName, CompetitionCategory, EventType, HomeClubName, USFSANo, FirstName, LastName, Email, Gender, Placement, PartnerFName, PartnerLName, PartnerEmail, PartnerGender, PartnerHomeClub, PartnerUSFSANo, Season, SeasonStartDate, SeasonEndDate, SkaterID, PartnerId FROM dbo.v004CompetitionRegistrations AS CompCur WHERE (SeasonEndDate >= GETDATE()) AND (NOT EXISTS (SELECT CompetitionName, CompetitionCategory, EventType, Placement, Season, SeasonStartDate, SeasonEndDate, HomeClubName, USFSANo, FirstName, LastName, Email, PartnerFName, PartnerLName, PartnerEmail, PartnerHomeClub, SkaterID FROM dbo.v004CompetitionRegistrations AS CompHist WHERE (SeasonEndDate <= DateFromParts(YEAR(GETDATE()) - 1, 6, 30)) AND (SkaterID = CompCur.SkaterID) OR (SeasonEndDate <= DateFromParts(YEAR(GETDATE()) - 1, 6, 30)) AND (PartnerId = CompCur.SkaterID))) GROUP BY CompetitionName, CompetitionCategory, EventType, HomeClubName, USFSANo, FirstName, LastName, Email, Gender, Placement, PartnerFName, PartnerLName, PartnerEmail, PartnerGender, PartnerHomeClub, Season, SeasonStartDate, SeasonEndDate, SkaterID, PartnerId, PartnerUSFSANo HAVING (SeasonEndDate >= DateFromParts(YEAR(GETDATE()), 6, 30)) AND (PartnerUSFSANo = 8699467)

She exists in "dbo.v004CompetitionRegistrations" but NOT in the View above with the NOT EXISTS.
The Skater is not a 1st Time competitor but his partner is for the event and SHOULD show up.

And several other competitors are showing up as 1st Time when they've competed in the past. So not sure what's going on.

here's an updated code:

SELECT TOP (100) PERCENT CompetitionName, CompetitionCategory, EventType, HomeClubName, USFSANo, FirstName, LastName, Email, Gender, Placement, PartnerFName, PartnerLName, PartnerEmail, PartnerGender, PartnerHomeClubName AS PartnerHomeClub, PartnerUSFSANo, Season, SeasonStartDate, SeasonEndDate, SkaterID, PartnerId FROM dbo.v004CompetitionRegistrations AS CompCur WHERE (SeasonEndDate >= GETDATE()) AND (NOT EXISTS (SELECT CompetitionName, CompetitionCategory, EventType, Placement, Season, SeasonStartDate, SeasonEndDate, HomeClubName, USFSANo, FirstName, LastName, Email, PartnerFName, PartnerLName, PartnerEmail, PartnerHomeClubName, SkaterID FROM dbo.v004CompetitionRegistrations AS CompHist WHERE (SeasonEndDate < DateFromParts(YEAR(GETDATE()), 6, 30)) AND (PartnerId = CompCur.SkaterID))) GROUP BY CompetitionName, CompetitionCategory, EventType, HomeClubName, USFSANo, FirstName, LastName, Email, Gender, Placement, PartnerFName, PartnerLName, PartnerEmail, PartnerGender, Season, SeasonStartDate, SeasonEndDate, SkaterID, PartnerId, PartnerUSFSANo, PartnerHomeClubName HAVING (SeasonEndDate >= DateFromParts(YEAR(GETDATE()), 6, 30)) AND (USFSANo = 6630984)

Got the previous partner to show BUT now it's showing other competitors that should NOT be showing up.

can you post the sample data for the scenario that you described ?