September 22, 2015 at 4:47 pm
Ok, I'll try to explain what needs to happen as best as I can, so please bear with me 😀
There are Competitors that compete in Competitions 😉
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?
September 22, 2015 at 5:18 pm
CREATE TABLE scripts?
INSERT scripts?
can't fix what we can't see.
September 22, 2015 at 5:18 pm
Two Views sounds a bit complicated. Just do a COUNT(columnname) OVER (PARTITION BY columnname) in a CTE and SELECT only those items that have a count of 1... no GROUP BY required so you can return any/all columns that you require.
--Jeff Moden
Change is inevitable... Change for the better is not.
September 22, 2015 at 5:36 pm
Ok, example of how to do that?
I only know how to do the GroupBy then change the type on the field to Count then did Filter on that Count to equal 1.
here's the down and dirty since I'm not able to access the SQL Server at the moment.
The Competition Registration View "ALLCompetitionRegistrations"
SELECT vCompetitionRegistrations.CompetitionName, vCompetitionRegistrations.SeasonEndDate, vCompetitionRegistrations.CompetitorID
FROM vCompetitionRegistrations
GROUP BY Count(vCompetitionRegistrations.CompetitorID)
HAVING Count(vCompetitionRegistrations.CompetitorID) = 1
[/code]
The Current Competitions View "CurrentCompetitionRegsitrations"
SELECT vCompetitionRegistrations.CompetitionName, vCompetitionRegistrations.SeasonEndDate, vCompetitionRegistrations.CompetitorID
FROM vCompetitionRegistrations
WHERE vCompetitionRegistrations.SeasonEndDate >=DateFromParts(Year(GetDate()),6,30))
Linked View "Competitor1stTimeCompetitions"
JOIN ON CompetitorID
September 22, 2015 at 5:56 pm
September 22, 2015 at 6:03 pm
I got this so far
SELECT *
FROM competitions c
WHERE c.season>=DateFromParts(Year(GetDate()),6,30))
AND NOT EXISTS (
SELECT *
FROM competitions x
WHERE x.season<> DateFromParts(Year(GetDate()),6,30))
AND x.CompetitorID = c.CompetitorID
)
will be trying a modification this tomorrow with the actual fields and tables when I can access the SQL Server.
If anyone sees something not quite correct with this, do please me know also. Thank you
September 22, 2015 at 8:21 pm
serviceaellis (9/22/2015)
Ok, example of how to do that?
Understand that until you posted code, I didn't have table names, column names, nothing... it's important to post the whole problem if you want coded answers instead of prose answers like the one I posted, which turns out to look like this based on what you finally posted for code. 😉 And, no... I haven't tested it because I don't have your data either. 😀
WITH cteFirstTime AS
(
SELECT *
,RegistrationCount = COUNT(*) OVER (PARTITION BY CompetitorID)
FROM dbo.Competitions
WHERE Season >= DATEFROMPARTS(YEAR(GETDATE()),6,30))
)
SELECT *
FROM cteFirstTime
WHERE RegistrationCount = 1
;
Do take the time to read the article that pietlinden cited. It'll really help folks help you better in the future.
--Jeff Moden
Change is inevitable... Change for the better is not.
September 24, 2015 at 1:25 pm
So this worked! Or so it seems to be producing results as expected.
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
September 25, 2015 at 1:22 pm
After further testing the last shared code is only valid for the season that ended this year on 6/30/2015.
I tried changing the parameters so it will show 'current season' which is seasonenddate of 6/30/2016.
All seasons end on 6/30/yy.
So if we're in the month of June or earlier the code works.
But after June in the actual current year it should be looking at the 6/30/2016 as the "current season".
SELECT CompetitionName, CompetitionCategory, EventType, SkaterMbrNo, FirstName, LastName, Email, HomeClub, PartnerFName, PartnerLName, PartnerEmail,
PartnerHomeClub, SkaterID
FROM dbo.v004CompetitionRegistrations AS CompCur
WHERE (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 <> GetDate()) 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.
What is the correct use of the date parameter to handle that seasons end on 6/30/yy so that it always returns the true 'current season'?
I also tried >=DateFromParts(Year(GetDate())+1,6,30) and <> DateFromParts(Year(GetDate())+1,6,30) but don't know enough about this usage to know if that's accurate.
September 25, 2015 at 1:42 pm
Does this calculates the date as you want?
SELECT somedate, DATEADD( YY, (DATEDIFF( MM, '19000630', somedate)-1)/12, '19010630')
FROM (VALUES(CAST('20150625' AS datetime)),
(CAST('20150626' AS datetime)),
(CAST('20150627' AS datetime)),
(CAST('20150628' AS datetime)),
(CAST('20150629' AS datetime)),
(CAST('20150630' AS datetime)),
(CAST('20150701' AS datetime)),
(CAST('20150702' AS datetime)),
(CAST('20150703' AS datetime)),
(CAST('20150704' AS datetime)),
(CAST('20150705' AS datetime)))x(somedate)
September 25, 2015 at 2:07 pm
Luis,
Not sure what that's for.
The code I shared produces 6/30/2015 + SeasonEndDate with >=DateFromParts(Year(GetDate()),6,30)
I need it to produce 6/30/2016 +
but in any given year it needs to see that GetDate() is 6/30/yy and depending the current mm/dd, that would change.
so on 7/1/2015 or greater it needs to show 6/30/2016 and so on.
On 6/30/2015 or prior dates it needs to show 6/30/2015.
I tried >=DateFromParts(Year(GetDate())+1,6,30) with <> DateFromParts(Year(GetDate())+1,6,30)
and I am getting 2016 Competitions. However I'm not sure if that'll be still true come 1/1/2016
This DOES return the season ending 2016 BUT what happens come 1/1/16 with the Year(GetDate())+1? It won't show 2016 even though 6/30/2016 hasn't passed yet.
Hope this made sense
September 26, 2015 at 7:48 am
If you want a tested solution, please take the time to read the article pietlinden and Jeff mentioned. There's also a link to it in my signature. Please remember that we can't see what you see. We only know what you tell us. Without DDL, sample data and desired results, anything you get as an answer will not only be a guess, but also untested. In sort, help us help you.
September 26, 2015 at 11:44 am
Ed Wagner (9/26/2015)
If you want a tested solution, please take the time to read the article pietlinden and Jeff mentioned. There's also a link to it in my signature. Please remember that we can't see what you see. We only know what you tell us. Without DDL, sample data and desired results, anything you get as an answer will not only be a guess, but also untested. In sort, help us help you.
Hi Ed,
I appreciate the article that's shared regarding DDL sample data, etc ...
Though it may be more helpful, me trying to figure out how to get that to you vs. trying to get an untested / "theoretical" solution is ok with me at the moment.
And given that I would imagine there have been cases where someone is looking for the 1st time doing something and from a historical file, there's a way to find that out.
I don't I'm the first person inquiring to see how to get that type of result?
I can try to simplify, let's focus on the parameter used with DateFromParts function. Unless there's a better function or another way to get the data result needed?
** All seasons are from 7/1/yy - 6/30/yy
>=DateFromParts(Year(GetDate()), 6, 30)
Produces records from 6/30/2015. But 6/30/2015 is in the past so they should be excluded.
If I add "+1" (see below), come the new year, it won't show 6/30/2016 and it should, at least until 6/30/2016 then starting 7/1/2016 it should show 6/30/2017 records.
WHERE (SeasonEndDate >= DateFromParts(YEAR(GETDATE()) + 1, 6, 30)) AND
(NOT EXISTS
(SELECT *
FROM dbo.v004CompetitionRegistrations AS CompHist
WHERE (SeasonEndDate >=DateFromParts(YEAR(GETDATE()) + 1, 6, 30)) AND (CompCur.SkaterID = SkaterID)))
Then I thought this
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)))
This might be it but would appreciate feedback on that logic.
What would be the correct parameter or function that'll result in:
1. 1st time competitors in the 'current' season?
September 26, 2015 at 4:29 pm
have a play......maybe help / may not
DECLARE @yourdate datetime;
SET @yourdate = '20150620';
SELECT
CASE
WHEN DATEPART(M , @yourdate) >= 7 THEN DateFromParts(YEAR(@yourdate) , 7 , 1)
ELSE DateFromParts(YEAR(@yourdate) - 1 , 7 , 1)
END;
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
September 26, 2015 at 5:00 pm
That produces If then result. Is there a way to apply to produce the result of 1st Timers?
Using it against sample table I have locally, I applied it as such.
SELECT PersonId, OrganizationId, CASE WHEN DATEPART(M, EndDate) >= 7 THEN DateFromParts(YEAR(EndDate), 7, 1) ELSE DateFromParts(YEAR(EndDate) - 1, 7, 1)
END AS Expr1, EndDate
FROM dbo.PersonMembership
SkaterID OrgID Expr1 EndDate
PER00633872ORG000000822015-07-01 2016-06-30
PER00542813ORG000001102016-07-01 2017-06-30
PER01267404ORG000000022014-07-01 2015-06-30
It's converting the EndDate with the IF Then Else Case.
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply