Ver Mensaje Individual
  #2 (permalink)  
Antiguo 22/01/2014, 09:55
Avatar de Libras
Libras
Colaborador
 
Fecha de Ingreso: agosto-2006
Ubicación: En la hermosa perla de occidente
Mensajes: 7.412
Antigüedad: 17 años, 8 meses
Puntos: 774
Respuesta: Como ordenar por fecha la combinacion de estos 2 select?

Código SQL:
Ver original
  1. SELECT * FROM(
  2. SELECT 0 AS 'm', G.[GradedDate] AS 'LastDate',
  3.        CONVERT(VARCHAR(8), G.[GradedDate] , 108) AS 'Time',
  4.        CONVERT(VARCHAR(MAX),G.[VisitorNumber]) + '/' + CONVERT(VARCHAR(MAX),G.[HomeNumber]) AS 'NumGame', G.[VisitorTeam] + ' @ ' +G.[HomeTeam] AS 'Teams', P.[PeriodDescription],
  5.        CONVERT(VARCHAR(MAX),G.[VisitorScore]) + ' - ' + CONVERT(VARCHAR(MAX),G.[HomeScore]) AS 'Score', COUNT(*) AS 'Tickets',
  6.        SUM(H.[RiskAmount]) AS 'Risk', A.[Agent], D.[RESULT], SUM(H.[WinAmount]) AS 'Win', D.[IdGame], 0 AS 'IdWager'
  7. FROM GRADEDWAGERHEADER H, GRADEDWAGERDETAIL D, GRADEDGAME G, PERIOD P, AGENT A, WAGERTYPE W
  8. WHERE H.[IdWager] = D.[IdWager] AND
  9.       D.[IdGame]  = G.[IdGame]  AND
  10.       H.[IdAgent] = A.[IdAgent] AND
  11.       H.[IdWagerType] = W.[IdWagerType] AND
  12.       H.[SettledDate] >= CONVERT(datetime,('2014-01-12 00:00:01')) AND
  13.       H.[SettledDate] <= CONVERT(datetime,('2014-01-12 23:59:59')) AND
  14.       D.[IdSport] = P.[IdSport] AND
  15.       D.[Period]  = P.[NumberOfPeriod] AND
  16.       W.[Description] NOT LIKE '%PARLAY%' AND
  17.       H.[IdAgent] = 5585
  18. GROUP BY H.[SettledDate], G.[GradedDate], G.[VisitorNumber], G.[HomeNumber], G.[VisitorTeam], G.[HomeNumber], G.[HomeTeam],
  19.          P.[PeriodDescription], G.[VisitorScore], G.[HomeScore], A.[Agent],D.[RESULT], D.[IdGame]
  20. UNION
  21. SELECT * FROM(
  22. SELECT ROW_NUMBER()OVER (partition BY lastdate ORDER BY LastDate) AS rn, * FROM
  23. (
  24. SELECT MAX(H.[SettledDate]) AS 'LastDate', CONVERT(VARCHAR(8), G.[GradedDate] , 108) AS 'Time',
  25.        CONVERT(VARCHAR(MAX),G.[VisitorNumber]) + '/' + CONVERT(VARCHAR(MAX),G.[HomeNumber]) AS 'NumGame', G.[VisitorTeam] + ' @ ' +G.[HomeTeam] AS 'Teams', P.[PeriodDescription],
  26.        CONVERT(VARCHAR(MAX),G.[VisitorScore]) + ' - ' + CONVERT(VARCHAR(MAX),G.[HomeScore]) AS 'Score', 1 AS 'Tickets',
  27.        H.[RiskAmount] AS 'Risk', A.[Agent], D.[RESULT], H.[WinAmount] AS 'Win', D.[IdGame], D.[IdWager]  
  28. FROM GRADEDWAGERHEADER H, GRADEDWAGERDETAIL D, GRADEDGAME G, PERIOD P, AGENT A, WAGERTYPE W, PLAYER J
  29. WHERE H.[IdWager]  = D.[IdWager] AND
  30.       D.[IdGame]   = G.[IdGame]  AND
  31.       H.[IdAgent]  = A.[IdAgent] AND
  32.       H.[IdPlayer] = J.[IdPlayer] AND
  33.       H.[IdWagerType] = W.[IdWagerType] AND
  34.       D.[IdSport] = P.[IdSport] AND
  35.       D.[Period]  = P.[NumberOfPeriod] AND
  36.       H.[IdAgent] = 5585 AND
  37.       D.[IdWager] IN(12683473,12690356,12689748,12703286) AND
  38.       D.[RESULT]  = 1
  39. GROUP BY G.[GradedDate], G.[VisitorNumber], G.[HomeNumber], G.[VisitorTeam], G.[HomeNumber], G.[HomeTeam],
  40.          P.[PeriodDescription], G.[VisitorScore], G.[HomeScore], H.[RiskAmount], A.[Agent],D.[RESULT], H.[WinAmount], D.[IdGame], D.[IdWager]
  41. ) AS t1
  42. ) t2 WHERE rn=1
  43. ) AS completa ORDER BY TIME ASC
__________________
What does an execution plan say to t-sql query? Go f**k yourself, if you are not happy with me