SQL Server: how do I get data from a history table?

Question

Could you help me build an SQL query to retrieve data from the history table?

I am new to only weekly coding. I have tried simple SELECT statements so far, but hit a stumbling block.

My football club database has three tables. The first connects the balls with the players:

BallDetail | BallID | PlayerID | TeamID | |-------------------|--------| | 1 | 11 | 21 | | 2 | 12 | 22 | 

The second lists things that happen to balls:

 BallEventHistory | BallID | Event | EventDate | |--------|------ |------------| | 1 | Pass | 2012-01-01 | | 1 | Shoot | 2012-02-01 | | 1 | Miss | 2012-03-01 | | 2 | Pass | 2012-01-01 | | 2 | Shoot | 2012-02-01 | 

And the third is a table of changes in history. After the ball changes hands, a story is written:

 HistoryChanges | BallID | ColumnName | ValueOld | ValueNew | |--------|------------|----------|----------| | 2 | PlayerID | 11 | 12 | | 2 | TeamID | 21 | 22 | 

I am trying to get a table listing all the passes and shootings that player 11 made for all balls before the balls went to other players. Like this:

 | PlayerID | BallID | Event | Month | |----------|--------|-------|-------| | 11 | 1 | Pass | Jan | | 11 | 1 | Shoot | Feb | | 11 | 2 | Pass | Jan | 

I start like this:

 SELECT PlayerID, BallID, Event, DateName(month, EventDate) FROM BallDetail bd INNER JOIN BallEventHistory beh ON bd.BallID = beh.BallID WHERE PlayerID = 11 AND Event IN (Pass, Shoot) ... 

But how to make sure that Ball 2 is also turned on, despite the fact that he is now with another player?

Answer 1

 Select PlayerID,BallID,Event,datename(month,EventDate) as Month,Count(*) as cnt from ( Select Coalesce( (Select ValueNew from #HistoryChanges where ChangeDate=(Select max(ChangeDate) from #HistoryChanges h2 where h2.BallID=h.BallID and ColumnName='PlayerID' and ChangeDate<=EventDate) and BallID=h.BallID and ColumnName='PlayerID') ,(Select PlayerID from #BallDetail where BallID=h.BallID) ) as PlayerID, h.BallID,h.Event,EventDate from #BallEventHistory h ) a Group by PlayerID, BallID, Event,datename(month,EventDate) 

Answer 2

 SELECT d.PlayerID, d.BallID, h.Event, DATENAME(mm, h.EventDate) AS Month FROM BallDetail d JOIN BallEventHistory h ON d.BallID = h.BallID WHERE h.Event IN ('Pass', 'Shoot') AND d.PlayerID = 11 OR EXISTS (SELECT 1 FROM dbo.HistoryChanges c WHERE c.ValueOld = 11 AND c.ValueNew = d.PlayerID AND c.ColumnName = 'PlayerID' and c.ChangeDate = h.EventDate)