Every 2 years I have a subquery problem

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Every 2 years I have a subquery problem

Author

Topic

AndyC
Yak Posting Veteran

53 Posts

Posted - 2010-10-09 : 17:40:56

Is this another one?

I want to have a list of soccer players and for each one show how many games they could potentially have played for their team

So something like this for the list of players

SELECT lastName,startDate,endDate,teamID
FROM tblPlayerClub

Then I want to use the startDate(when the player joined), endDate and teamID to calculate the matches something like this

SELECT Count(tblMatchTeam.MATCHID) AS Matches
FROM tblMatch INNER JOIN tblMatchTeam ON (tblMatch.MATCHID = tblMatchTeam.MATCHID)
WHERE tblMatchTeam.TEAMID='#teamID#' AND (tblMatch.DATE BETWEEN CONVERT(DATETIME, '#startDate#', 102) AND CONVERT(DATETIME, '#endDate#', 102))

I then want a list starting like this

Zola Chelsea 234

Any advice, much appreciated

Andrew Clark
www.premiersoccerstats.com

slimt_slimt
Aged Yak Warrior

746 Posts

Posted - 2010-10-10 : 02:02:06

hi,

can you post some sample data?

and is there any connection between playerclub table and matchteam table?

thank you

p.s.:maybe something like this will help you:
http://sqlblogcasts.com/blogs/tomaztsql/archive/2010/09/20/with-cte-calculating-difference-in-days.aspx

AndyC
Yak Posting Veteran

53 Posts

Posted - 2010-10-10 : 10:32:53

Ok
Sample data

Smith 08/01/2004 04/05/2007 Liverpool
Jones 10/15/2006

Andrew Clark
www.premiersoccerstats.com

AndyC
Yak Posting Veteran

53 Posts

Posted - 2010-10-10 : 10:50:11

Ok Sorry about earlier abbreviated post and formatting
Sample data

a) Players tenure at club

Smith 08/01/2004 04/05/2007 Liverpool
Jones 10/15/2006 11/25/2009 Liverpool

b) Matches played by team

Liverpool
07/27/04
08/03/04
08/10/04
....
11/23/09
12/03/09

I want to show that Smith was available to play in the 107 matches Liverpool played between 08/01/2004 and 04/05/2007,
Jones in 119 between 10/15/2006 and 11/25/2009 etc. Then from their actual appearances I can show what proportion of games they appeared in

The connection between playerclub table and matchteam table is via a playermatch table which has a playerteam field (like playerclub) and
a teammatchid(as does matchteam)

Andrew Clark
www.premiersoccerstats.com

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-10-10 : 21:27:56

1. Can you post the full query ?
2. Please explain how your query will not work every 2 year ?

KH
[spoiler]Time is always against us[/spoiler]

AndyC
Yak Posting Veteran

53 Posts

Posted - 2010-10-10 : 22:37:59

Well I dont have the query that is the problem. I want to combine the ones I quoted in the first posting
I want referring to this problem. I dont do much SQL and whenever I hit problems (about every 2 years) it usually relates to me having problems with subqueries. Not sure what is the case here though
cheers

Andrew Clark
www.premiersoccerstats.com

kunal.mehta
Yak Posting Veteran

83 Posts

Posted - 2010-10-11 : 03:18:42

can u post the table structure

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources