SQLTeam.com | Weblogs | Forums

1st Time?


#1

Ok, I'll try to explain what needs to happen as best as I can, so please bear with me :smiley:

There are Competitors that compete in Competitions :wink:

Trying to get a report / View that'll display the following information.

From all the competition registrations which competitors is it their first time competing in the current season.

So there's a table that stores competition registrations, a table of the competitors, etc ...

My thought process so far:

  1. Create a View and do a GroupBy then do a count on competitorID = 1 for all the competition registrations. Which should give me ALL competitors that's competed only once. It be back in 2000 or last year or this year, etc ...
  2. then another View on the Competition Registration for the current season competitions by Competitor.
  3. Link these two views on CompetitorID to get the list of 1st time competitors.

I don't think this thought is correct? I only got 3 records when there should be few - several hundred records.

Any ideas on how to get the result of 1st time competitors in the current season?


#2
SELECT *
FROM competitions c
WHERE c.session = 'current session'
	AND NOT EXISTS (
		SELECT *
		FROM competitions x
		WHERE x.session <> 'current session'
			AND x.CompetitorID = c.CompetitorID
		)

#3

Ok, thank you, I'll try that tomorrow. I can't access the server tonight.


#4

not working

SELECT CompetitionName, CompetitionCategory, EventType, SkaterMbrNo, FirstName, LastName, Email, HomeClub AS [Home Club], PartnerFName, PartnerLName, PartnerEmail, PartnerHomeClub, SkaterID FROM dbo.v004CompetitionRegistrations as CompCur WHERE (CompCur.SeasonEndDate >= DateFromParts(YEAR(GETDATE()), 6, 30)) AND NOT EXISTS ( SELECT CompetitionName, CompetitionCategory, EventType, SkaterMbrNo, FirstName, LastName, Email, HomeClub AS [Home Club], PartnerFName, PartnerLName, PartnerEmail, PartnerHomeClub, SkaterID FROM dbo.v004CompetitionRegistrations as CompHist WHERE (CompHist.SeasonEndDate <> DateFromParts(YEAR(GETDATE()), 6, 30)) AND CompHist.SkaterID = CompCur.SkaterID


#5

Which error description do you get?

A quick look at your query, you need to end ) to the "exists" section


#6

How do you mean?


#7

What I meant to write was: A quick look at your query, you are missing bracket at the end:

SELECT CompetitionName, CompetitionCategory, EventType, SkaterMbrNo, FirstName, LastName, Email, HomeClub AS [Home Club], PartnerFName, 
                                                     PartnerLName, PartnerEmail, PartnerHomeClub, SkaterID
FROM            dbo.v004CompetitionRegistrations as CompCur
WHERE        (CompCur.SeasonEndDate >= DateFromParts(YEAR(GETDATE()), 6, 30))
                     AND NOT EXISTS (
                                                     SELECT CompetitionName, CompetitionCategory, EventType, SkaterMbrNo, FirstName, LastName, Email, HomeClub AS [Home Club], PartnerFName, 
                                                     PartnerLName, PartnerEmail, PartnerHomeClub, SkaterID
                                                     FROM            dbo.v004CompetitionRegistrations as CompHist
                                                     WHERE         (CompHist.SeasonEndDate <> DateFromParts(YEAR(GETDATE()), 6, 30))
                                                                            AND CompHist.SkaterID = CompCur.SkaterID
                                    ) /* HERE IS THE MISSING BRACKET */

#8

Lol! Of course I missed that!

It works. Or it seems like it is! Thank you!

I need have to do a Group By however. As the records were repeating.

SELECT CompetitionName, CompetitionCategory, EventType, SkaterMbrNo, FirstName, LastName, Email, HomeClub, PartnerFName, PartnerLName, PartnerEmail, PartnerHomeClub, SkaterID FROM dbo.v004CompetitionRegistrations AS CompCur WHERE (SeasonEndDate >= DateFromParts(YEAR(GETDATE()), 6, 30)) AND (NOT EXISTS (SELECT CompetitionName, CompetitionCategory, EventType, SkaterMbrNo, FirstName, LastName, Email, HomeClub AS [Home Club], PartnerFName, PartnerLName, PartnerEmail, PartnerHomeClub, SkaterID FROM dbo.v004CompetitionRegistrations AS CompHist WHERE (SeasonEndDate <> DateFromParts(YEAR(GETDATE()), 6, 30)) AND (SkaterID = CompCur.SkaterID))) GROUP BY CompetitionName, CompetitionCategory, EventType, SkaterMbrNo, FirstName, LastName, Email, HomeClub, PartnerFName, PartnerLName, PartnerEmail, PartnerHomeClub, SkaterID


#9

Sorry, not actually correct after all.

the SeasonEndDate >=DateFromParts(Year(GetDate()),6,3)) isn't correct because this is saying that 6/30/2015 and 6/30/2015 is past.

So I added SeasonEndDate >=GetDate() to it to get dates after 6/30/2015.

NOTE: Seasons all end on 6/30/yy

I modified it to this and it doesn't seem quite right.
What is best?
All competition registrations where the seasonenddate is >=GetDate() are "current" and less than GetDate() is prior registrations? Is that correct?

SELECT CompetitionName, CompetitionCategory, EventType, SkaterMbrNo, FirstName, LastName, Email, HomeClub, PartnerFName, PartnerLName, PartnerEmail, PartnerHomeClub, SkaterID, SeasonEndDate, Season FROM dbo.v004CompetitionRegistrations AS CompCur WHERE (SeasonEndDate >= DateFromParts(YEAR(GETDATE()), 6, 30)) AND (SeasonEndDate >= GETDATE()) AND (NOT EXISTS (SELECT CompetitionName, CompetitionCategory, EventType, SkaterMbrNo, FirstName, LastName, Email, HomeClub AS [Home Club], PartnerFName, PartnerLName, PartnerEmail, PartnerHomeClub, SkaterID FROM dbo.v004CompetitionRegistrations AS CompHist WHERE (SeasonEndDate <> DateFromParts(YEAR(GETDATE()), 6, 30)) AND (SeasonEndDate <> GETDATE()) AND (SkaterID = CompCur.SkaterID))) GROUP BY CompetitionName, CompetitionCategory, EventType, SkaterMbrNo, FirstName, LastName, Email, HomeClub, PartnerFName, PartnerLName, PartnerEmail, PartnerHomeClub, SkaterID, SeasonEndDate, Season


#10

Hi khtan,
what is the best use of 'current season'?

I used >=DateFromParts(Year(GetDate()),6,30) however that is records for season ending 6/30/2015.

I need it for season ending 6/30/2016.

So each season ends on 6/30/yy.
If we were in June 2015 or prior months from 6/30/15, it would be 'current' until 7/1/2015. Then it would be records with seasonenddate of 6/30/16 and so.

I tried changing it and I'm not dong something correct. I get no records?

SELECT CompetitionName, CompetitionCategory, EventType, SkaterMbrNo, FirstName, LastName, Email, HomeClub, PartnerFName, PartnerLName, PartnerEmail, PartnerHomeClub, SkaterID FROM dbo.v004CompetitionRegistrations AS CompCur WHERE (SeasonEndDate >= DateFromParts(YEAR(GETDATE()), 7, 1)) AND (NOT EXISTS (SELECT CompetitionName, CompetitionCategory, EventType, SkaterMbrNo, FirstName, LastName, Email, HomeClub AS [Home Club], PartnerFName, PartnerLName, PartnerEmail, PartnerHomeClub, SkaterID FROM dbo.v004CompetitionRegistrations AS CompHist WHERE (SeasonEndDate <> DateFromParts(YEAR(GETDATE()), 6, 30)) AND (SkaterID = CompCur.SkaterID))) GROUP BY CompetitionName, CompetitionCategory, EventType, SkaterMbrNo, FirstName, LastName, Email, HomeClub, PartnerFName, PartnerLName, PartnerEmail, PartnerHomeClub, SkaterID

Changed the dates to >=GetDate() and <> GetDate() but getting 0 records.
Then tried >=DateFromParts(Year(GetDate())+1,6,30) with <> DateFromParts(Year(GetDate())+1,6,30)

This DOES return the season ending 2016 BUT what happens come 1/1/16 with the Year(GetDate())+1? Will it still show 6/30/2016 since it's >= until 7/1/2016? Then it'll show 6/30/2017?

That is what i actually need to produce.


#11

this should give you the start and end date for a season based on below logic

; with dates
as
(
    select    [date] = '2015-06-30'    union all
    select    [date] = '2015-07-01'    union all
    select    [date] = '2016-06-30'    union all
    select    [date] = '2016-07-01'
)
select    [date], 
    season_start     = case     when [date] >= DateFromParts ( year( [date] ), 7, 1)
                then DateFromParts ( year( [date] ), 7, 1)
                else DateFromParts ( year( [date] ) - 1, 7, 1)
                end,
    season_end     = case     when [date] <= DateFromParts ( year( [date] ), 6, 30)
                then DateFromParts ( year( [date] ), 6, 30)
                else DateFromParts ( year( [date] ) + 1, 6, 30)
                end
from    dates

is this correct in your context ?

the above query is to establish the logic in determine the start and end of current season date based on current date


#12

Sorry, I'm not sure if I'm following ...

The code you provided is giving the correct result IF we're talking about the season ending in 2015.
However, that would be the 'past season' and not 'current season' since the season end date is 6/30/2015 in the current code with >=DateFromParts(Year(GetDate()),6,30)

Keeping in mind that it needs to result in 1st time competitors from ALL registrations where the registration is in the 'current season', which, after this year of 6/30 (6/30/2015) would be next year 6/30 (6/30/2016) and so on.
But before 6/30 it would be 'current season' in the current year (2015).

So I would like to know how to change the >=DateFromParts(Year(GetDate()),6,30)
and the In Select <> DateFromParts(Year(GetDate()),6,3)) to work with the information that 6/30/yy is the end of a season and based on what today is, say 5/15/15 the current code is correct, but if today is 7/1/15 the current code is incorrect.

NOTE: There is a Season Start Date field just as Season End Date in the database.

Did that make sense?


#13

sorry i was still composing part 2 of the reply...

and combining it to your query

; 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)
            )
        )
GROUP BY     CompetitionName, CompetitionCategory, EventType, SkaterMbrNo, FirstName, LastName, Email, HomeClub, PartnerFName, PartnerLName, PartnerEmail, 
        PartnerHomeClub, SkaterID

#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