December 6, 2007 at 1:01 pm
Thanks for looking at this!
I have two views: one view lists all the games that person x played:
date, player_id, game_id, level_id, location_id, played_hours
12/1/2007, 16000, 101, 102, 100, 8
12/2/2007, 16000, 110, 102, 100, 10
The other view is the same, only it lists all the games played by person y:
date, game_id, level_id, location_id, played_hours
12/1/2007, 16001, 101, 102, 100, 6
I have 3rd view that compares these two views. I get a comparison of every instance where person y played the same games as person x. How do I add or account for the instances where person y played a game that person x did not play or visa versa?
So the data combined should look like this:
date, game_id, level_id, location_id, played_hours_person_x, played_hours_person_y
12/1/2007, 101, 102, 100, 8, 6
12/2/2007, 110, 102, 100, 10, 0
I am having trouble figuring out how to add the row with the game information that is not included in both views and include a 0 value if one or the other player times is null.
Thanks for any input
December 6, 2007 at 1:10 pm
join the tables/views with a full outer join
December 6, 2007 at 1:35 pm
Sorry, I jumped the gun in answer your question without reading your required results. I stopped at how do I :hehe:
Let me look at it a little harder.
December 6, 2007 at 1:54 pm
This should do the trick.
declare @Player1 table
(
date datetime ,
player_id int,
game_id int,
level_id int,
location_id int,
played_hours int
)
insert into @Player1
select '12/1/2007', 16000, 101, 102, 100, 8 union all
select '12/2/2007', 16000, 110, 102, 100, 10
declare @Player2 table
(
date datetime ,
player_id int,
game_id int,
level_id int,
location_id int,
played_hours int)
insert into @Player2
select '12/1/2007', 16001, 101, 102, 100, 6
--EXPECTED RESULTS:
--===================================================================================
--date, game_id, level_id, location_id, played_hours_person_x, played_hours_person_y
--12/1/2007, 101, 102, 100, 8, 6
--12/2/2007, 110, 102, 100, 10, 0
select a.date,
a.game_id,
a.level_id,
a.location_id,
ISNULL(a.played_hours,0) AS [PLAYER1],
ISNULL(b.played_hours,0) AS [PLAYER2]
from @Player1 a full outer join @Player2 b on a.date = b.date
group by a.date, a.game_id, a.level_id, a.location_id, a.played_hours, b.played_hours
December 7, 2007 at 7:16 am
This works great one way...
If in table a, we have instances where player b did not play, we get the correct response. Now we need to add instances where there will be a record in table b where player a did not play.
select a.date,
a.game_id,
a.level_id,
a.location_id,
ISNULL(a.played_hours,0) AS [PLAYER1],
ISNULL(b.played_hours,0) AS [PLAYER2]
from @Player1 a full outer join @Player2 b on a.date = b.dategroup by a.date, a.game_id, a.level_id, a.location_id, a.played_hours, b.played_hours
returns:
--EXPECTED RESULTS:--=============================================================================--date, game_id, level_id, location_id, played_hours_person_x, played_hours_person_y
--12/1/2007, 101, 102, 100, 8, 6
--12/2/2007, 110, 102, 100, 10, 0
But a record exists:
date, player_id, game_id, level_id, location_id, played_hours
12/1/2007, 16001, 132, 102, 100, 2
This displays as:
null,null,null,null,0,2
I tried to include the colums from table b as well, but then the results table has twice the columns and is just a mess!
Thanks again!
December 7, 2007 at 7:28 am
You need to aggreage the hours for each game,
SELECTCOALESCE( a.date, b.date ) AS Date,
COALESCE( a.game_id, b.game_id ) AS GameID,
COALESCE( a.level_id, b.level_id ) AS LevelID,
COALESCE( a.location_id, b.location_id ) AS LocationID,
SUM( COALESCE( a.played_hours, 0 ) ) AS [PLAYER1],
SUM( COALESCE( b.played_hours, 0 ) ) AS [PLAYER2]
FROM@Player1 a
FULL OUTER JOIN @Player2 b ON a.date = b.date
GROUP BY a.date, b.date, a.game_id, b.game_id, a.level_id, b.level_id, a.location_id, b.location_id
--Ramesh
December 7, 2007 at 8:15 am
I think I got it. I used this as part of the query still using a full outer join:
coalesce(a. ) as columnName
Thanks again!
December 8, 2007 at 12:00 am
Cool. Glad you got everything worked out. 😀
Viewing 8 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply