Sql Statement Dates Prob
-
Hi i am trying to write a statement to get the players in a squad based on the StartDate, EndDate of any season and the DateEntered, DateLeft a squad
the possible situations are
Player joins squad after StartDate and is still there (DateLeft Null)
Player joins squad after StartDate and leaves before EndDate
Player joins before StartDate and is still there (DateLeft Null)
Player joins before StartDate and leaves before EndDate
this is my statement at the moment but i am gettin players that are in the squad this season and last season sp if anyone has any ideas i would really appreciate them
select spm.SquadId,Person.Id as ID, IsNull(firstName+ ' ','') + IsNull(MiddleName+ ' ','') + IsNull(LastName,'') as Name, spm.DateEntered, spm.DateLeft
From Person
LEFT JOIN SquadPlayerMapping spm on spm.PlayerId = Person.Id
Where spm.SquadId = 2927
And ((spm.DateLeft IS NULL) Or (spm.DateEntered >= (Select StartDate From Seasons Where ID = 50)))
And spm.DateEntered <= (Select EndDate From Seasons Where ID = 50)
Order by LastName
thanks in advance
Tim
-
Hi there,
Would it work if you jsut needed to know if the player was still there, or if they left during the season, I think this might take care of all your situations:
Where spm.SquadId = 2927
And
(spm.DateLeft IS NULL) --Player hasn't left
Or
((spm.DateLeft = (Select StartDate From Seasons Where ID = 50))) --Player left during the season
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley:w00t:
SQL-4-Life
Posting Best Practices[/url]
Numbers / Tally Tables[/url] -
I'm not sure I understand the requirement fully... so I'll try to describe it and you tell me where I'm wrong.
You have a table of persons, and a table which says at which day they joined or left which team. Then you have a table of seasons, which says for each season when it starts and ends.
Your query should - for a defined team and season - show all persons, that were part of the team for at least one day of the season.
Is that right? Is it all? Or do you need some other info there, like separating/marking those that left before end of season, entered during season etc.? Or some info about whether they were on the team in previous season (previous to the entered season, not to current), or are on team until today.... and so on.
Then we can tailor the query to your requirements.
-
thats actually the way its workin at the moment(fine by me) but some smart man thought to check last seasons players and that list includes this seasons players aswell which led to this problem
some of last seasons players have a date for dateLeft but the ones still there do not
-
perfect thats exactly what i need its a simple as selectin a season(past and present) and squad then the players that are mapped to the squad for the season are displayed
Thanks
-
Try this... it might need some tweaking, I'm not 100% sure about your data structure - but the idea should be clear. We want all players that ENTERED the team before the season FINISHED, and at the same time either didn't leave yet, or left after the season started.
DECLARE @season INT, @team INT
SET @team = 2927
SET @season = 50
select spm.SquadId,Person.Id as ID, IsNull(firstName+ ' ','') + IsNull(MiddleName+ ' ','') + IsNull(LastName,'') as Name, spm.DateEntered, spm.DateLeft
From Person
LEFT JOIN SquadPlayerMapping spm on spm.PlayerId = Person.Id
JOIN seasons sea on sea.ID=@season
Where spm.SquadId = @team
AND spm.dateentered sea.StartDate OR spm.dateleft IS NULL)
-
i reckon thats its thanks very much guys that was fast im impressed
how come u joined the season table instead of using the dates the way i did it?????
thanks alot guys its really appreciated
Tim
-
It was fast because I'm using similar queries pretty often at work, to find out how many customers had "active" status during certain period.
You might need to change < to <= and so on - everything depends on what precisely are the requirements and how the data are stored. For example, if DateEntered contains precise date (including time), you will probably need to add one day to the EndDate of the period, and leave the "less than"; otherwise those who entered the team during the last day will not be displayed (because 2007.06.30 10:15:00 is greater than 2007.06.30).
About the JOIN - well, SQL Server is optimized for joins, so I always try to use them when applicable. In this case, it is actually a cross join (you join each row with each row), but thanks to the limitation to one season this does not cause any duplication. Anything * 1 = Anything. It should perform better than your way, but both would give the right results.
In case I would be asked to create query, which will allow to check several consecutive seasons at once (like "show all who were in the team during last X seasons"), I would probably build it in a different way: declare parameters @startdate, @enddate, fill them from table Seasons according to input of X, and then use them directly against SPM in the actual query instead of joining to seasons... or maybe even do it some other way at all.
There are always many paths you can take - that's also why it is so hard to find the best one if one doesn't know precise data structures and precise requirements, and why I always bother the unlucky posters with questions about everything
-
nice one that date with the time has solved another problem of mine, your a gentleman thanks very much for taking the time to give me a bit of an education i really appreciate it
Tim
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply