1st Time?

Transact-SQL
14

Note, for EXISTS ( ), you don't have to list the column in the SELECT clause. simply SELECT * will do

15

["khtan, post:13, topic:3344"]

Ok thank you.
i'm getting an error on ')' and can't figure out which one it is?

; WITH CurrentSeason as (select season_start = case when getdate() >= DateFromParts ( year( getdate() ), 7, 1) then DateFromParts ( year( getdate() ), 7, 1) else DateFromParts ( year( getdate() ) - 1, 7, 1) end, season_end = case when getdate() <= DateFromParts ( year( getdate() ), 6, 30) then DateFromParts ( year( getdate() ), 6, 30) else DateFromParts ( year( getdate() ) + 1, 6, 30) end) SELECT CompetitionName, CompetitionCategory, EventType, SkaterMbrNo, FirstName, LastName, Email, HomeClub, PartnerFName, PartnerLName, PartnerEmail, PartnerHomeClub, SkaterID FROM dbo.v004CompetitionRegistrations AS CompCur CROSS JOIN CurrentSeason cs WHERE (SeasonStartDate >= cs.season_start AND (NOT EXISTS (SELECT * FROM dbo.v004CompetitionRegistrations AS CompHist WHERE (SeasonEndDate < cs.season_start) AND (SkaterID = CompCur.SkaterID))

16

Gotcha! Thank you!

Also, wouldn't this work?

WHERE (SeasonEndDate >= DateFromParts(YEAR(GETDATE()), 6, 30)) AND SeasonEndDate >=GetDate() AND (NOT EXISTS (SELECT * FROM dbo.v004CompetitionRegistrations AS CompHist WHERE (SeasonEndDate <=DateFromParts(YEAR(GETDATE()), 6, 30)) AND (CompCur.SkaterID = SkaterID)))

17

isn't this the same as your initial query ?

what if today date is before June 30 and what if it is after June 30 ? Does your query gives the result that you required ?

18

There's the change from what you had originally provided with the

It seems to be working. I checked on a couple of of Skaters and the ones with multiple entries do not show. Which would mean those are the 1st Timers?

Wouldn't the >= satisfy that? BUT yes, that is my concern.

Because the original was looking at 6/30/2015 as the 'current season' and that is incorrect since 6/30/2015 has passed with the >=DateFromParts(Year(GetDate()),6,30) but then add the AND >=GetDate() skips 6/30/2015 and looks at the next EndDate, which is 6/30/2016. Because that is the next >= today [GetDate()].

Correct?

Then the NOT EXIST part with the <=DateFromParts(Year(GetDate()),6,30) looks at all prior records since 6/30/2015 and does NOT include those records, correct?

19

Yes. That's why one of my earlier query checks for current date. If it is before June 30 then current year else take next year June 30 as end of season

season_end     = case when [date] <= DateFromParts ( year( [date] ), 6, 30)
                      then DateFromParts ( year( [date] ), 6, 30)
                      else DateFromParts ( year( [date] ) + 1, 6, 30)
                      end
20

will try this and compare in the AM here.

thank you and will report back

23

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

24

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.

25

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

26

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.

27

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

28

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.

29

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

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

31

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.

32

in the NOT EXISTS query

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

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.

35

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.

36

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