Compañero, lo que mas se me ocurrio hacer fue esto, me corre un poco mejor que antes pero en algunos casos no agrupa bien, pero si ya no se le puede hacer nada mas pues asi se queda, mira:
Código SQL:
Ver originalSELECT TIME, NumGame, Teams, Score, Tickets, Risk, win, loss
FROM(
SELECT *,
CASE WHEN RESULT=1 OR tickets=1 THEN SUM(winamount) END AS win,
CASE WHEN RESULT=0 OR tickets=0 THEN SUM(Risk) END AS loss
FROM(
SELECT CONVERT(VARCHAR(8), H.[SettledDate] , 108) AS 'Time', CONVERT(VARCHAR(MAX),G.[VisitorNumber]) + '/' + CONVERT(VARCHAR(MAX),G.[HomeNumber]) AS 'NumGame',
G.[VisitorTeam] + '@'+ G.[HomeTeam] AS 'Teams', CONVERT(VARCHAR(MAX),G.[VisitorScore]) + ' - ' + CONVERT(VARCHAR(MAX),G.[HomeScore]) AS 'Score', COUNT(*) AS 'Tickets',
SUM(H.[RiskAmount]) AS 'Risk',d.RESULT,h.winamount
FROM GRADEDGAME G, GRADEDWAGERHEADER H, GRADEDWAGERDETAIL D, AGENT A, WAGERTYPE W
WHERE D.[IdGame] = G.[IdGame] AND
D.[IdWager] = H.[IdWager] AND
H.[IdAgent] = A.[IdAgent] AND
H.[IdWagerType] = W.[IdWagerType] AND
G.[GradedDate] >= CONVERT(datetime,('2014-01-12 00:00:00')) AND
G.[GradedDate] <= CONVERT(datetime,('2014-01-12 23:59:59')) AND
H.[IdAgent] = 5585
GROUP BY H.[SettledDate], G.[VisitorNumber], G.[HomeNumber], G.[VisitorTeam], G.[HomeTeam], G.[VisitorScore], G.[HomeScore], d.RESULT,h.winamount
) AS t1
GROUP BY TIME, NumGame, Teams, Score, Tickets, Risk, RESULT, winamount
) AS t2
GROUP BY TIME, NumGame, Teams, Score, Tickets, Risk, winamount, win,loss