[SOLUCIONADO] Como ordenar por fecha la combinacion de estos 2 select?

[adryan310]

Hola amigos del foro,

Tengo estos 2 select que uno para que me retorne todo de una sola vez, la cuestion es que necesito ordenar su campo de hora o Time como se llama en realidad, necesito ordenarlo de forma ascendente osea desde la hora mas temprana a la hora mas tarde. Alguna ayuda por favor ?

Código SQL:

SELECT 0 AS 'm', G.[GradedDate] AS 'LastDate',
       CONVERT(VARCHAR(8), G.[GradedDate] , 108) AS 'Time', 
       CONVERT(VARCHAR(MAX),G.[VisitorNumber]) + '/' + CONVERT(VARCHAR(MAX),G.[HomeNumber]) AS 'NumGame', G.[VisitorTeam] + ' @ ' +G.[HomeTeam] AS 'Teams', P.[PeriodDescription],
       CONVERT(VARCHAR(MAX),G.[VisitorScore]) + ' - ' + CONVERT(VARCHAR(MAX),G.[HomeScore]) AS 'Score', COUNT(*) AS 'Tickets',
       SUM(H.[RiskAmount]) AS 'Risk', A.[Agent], D.[RESULT], SUM(H.[WinAmount]) AS 'Win', D.[IdGame], 0 AS 'IdWager'
FROM GRADEDWAGERHEADER H, GRADEDWAGERDETAIL D, GRADEDGAME G, PERIOD P, AGENT A, WAGERTYPE W
WHERE H.[IdWager] = D.[IdWager] AND 
      D.[IdGame]  = G.[IdGame]  AND 
      H.[IdAgent] = A.[IdAgent] AND
      H.[IdWagerType] = W.[IdWagerType] AND
      H.[SettledDate] >= CONVERT(datetime,('2014-01-12 00:00:01')) AND 
      H.[SettledDate] <= CONVERT(datetime,('2014-01-12 23:59:59')) AND
      D.[IdSport] = P.[IdSport] AND
      D.[Period]  = P.[NumberOfPeriod] AND 
      W.[Description] NOT LIKE '%PARLAY%' AND
      H.[IdAgent] = 5585
GROUP BY H.[SettledDate], G.[GradedDate], G.[VisitorNumber], G.[HomeNumber], G.[VisitorTeam], G.[HomeNumber], G.[HomeTeam], 
         P.[PeriodDescription], G.[VisitorScore], G.[HomeScore], A.[Agent],D.[RESULT], D.[IdGame]
UNION
SELECT * FROM(
SELECT ROW_NUMBER()OVER (partition BY lastdate ORDER BY LastDate) AS rn, * FROM
(
SELECT MAX(H.[SettledDate]) AS 'LastDate', CONVERT(VARCHAR(8), G.[GradedDate] , 108) AS 'Time', 
       CONVERT(VARCHAR(MAX),G.[VisitorNumber]) + '/' + CONVERT(VARCHAR(MAX),G.[HomeNumber]) AS 'NumGame', G.[VisitorTeam] + ' @ ' +G.[HomeTeam] AS 'Teams', P.[PeriodDescription],
       CONVERT(VARCHAR(MAX),G.[VisitorScore]) + ' - ' + CONVERT(VARCHAR(MAX),G.[HomeScore]) AS 'Score', 1 AS 'Tickets',
       H.[RiskAmount] AS 'Risk', A.[Agent], D.[RESULT], H.[WinAmount] AS 'Win', D.[IdGame], D.[IdWager]   
FROM GRADEDWAGERHEADER H, GRADEDWAGERDETAIL D, GRADEDGAME G, PERIOD P, AGENT A, WAGERTYPE W, PLAYER J
WHERE H.[IdWager]  = D.[IdWager] AND 
      D.[IdGame]   = G.[IdGame]  AND 
      H.[IdAgent]  = A.[IdAgent] AND
      H.[IdPlayer] = J.[IdPlayer] AND
      H.[IdWagerType] = W.[IdWagerType] AND
      D.[IdSport] = P.[IdSport] AND
      D.[Period]  = P.[NumberOfPeriod] AND 
      H.[IdAgent] = 5585 AND 
      D.[IdWager] IN(12683473,12690356,12689748,12703286) AND
      D.[RESULT]  = 1
GROUP BY G.[GradedDate], G.[VisitorNumber], G.[HomeNumber], G.[VisitorTeam], G.[HomeNumber], G.[HomeTeam], 
         P.[PeriodDescription], G.[VisitorScore], G.[HomeScore], H.[RiskAmount], A.[Agent],D.[RESULT], H.[WinAmount], D.[IdGame], D.[IdWager]
) AS t1
) t2 WHERE rn=1
 

[Libras]

Código SQL:

SELECT * FROM(
SELECT 0 AS 'm', G.[GradedDate] AS 'LastDate',
       CONVERT(VARCHAR(8), G.[GradedDate] , 108) AS 'Time', 
       CONVERT(VARCHAR(MAX),G.[VisitorNumber]) + '/' + CONVERT(VARCHAR(MAX),G.[HomeNumber]) AS 'NumGame', G.[VisitorTeam] + ' @ ' +G.[HomeTeam] AS 'Teams', P.[PeriodDescription],
       CONVERT(VARCHAR(MAX),G.[VisitorScore]) + ' - ' + CONVERT(VARCHAR(MAX),G.[HomeScore]) AS 'Score', COUNT(*) AS 'Tickets',
       SUM(H.[RiskAmount]) AS 'Risk', A.[Agent], D.[RESULT], SUM(H.[WinAmount]) AS 'Win', D.[IdGame], 0 AS 'IdWager'
FROM GRADEDWAGERHEADER H, GRADEDWAGERDETAIL D, GRADEDGAME G, PERIOD P, AGENT A, WAGERTYPE W
WHERE H.[IdWager] = D.[IdWager] AND 
      D.[IdGame]  = G.[IdGame]  AND 
      H.[IdAgent] = A.[IdAgent] AND
      H.[IdWagerType] = W.[IdWagerType] AND
      H.[SettledDate] >= CONVERT(datetime,('2014-01-12 00:00:01')) AND 
      H.[SettledDate] <= CONVERT(datetime,('2014-01-12 23:59:59')) AND
      D.[IdSport] = P.[IdSport] AND
      D.[Period]  = P.[NumberOfPeriod] AND 
      W.[Description] NOT LIKE '%PARLAY%' AND
      H.[IdAgent] = 5585
GROUP BY H.[SettledDate], G.[GradedDate], G.[VisitorNumber], G.[HomeNumber], G.[VisitorTeam], G.[HomeNumber], G.[HomeTeam], 
         P.[PeriodDescription], G.[VisitorScore], G.[HomeScore], A.[Agent],D.[RESULT], D.[IdGame]
UNION
SELECT * FROM(
SELECT ROW_NUMBER()OVER (partition BY lastdate ORDER BY LastDate) AS rn, * FROM
(
SELECT MAX(H.[SettledDate]) AS 'LastDate', CONVERT(VARCHAR(8), G.[GradedDate] , 108) AS 'Time', 
       CONVERT(VARCHAR(MAX),G.[VisitorNumber]) + '/' + CONVERT(VARCHAR(MAX),G.[HomeNumber]) AS 'NumGame', G.[VisitorTeam] + ' @ ' +G.[HomeTeam] AS 'Teams', P.[PeriodDescription],
       CONVERT(VARCHAR(MAX),G.[VisitorScore]) + ' - ' + CONVERT(VARCHAR(MAX),G.[HomeScore]) AS 'Score', 1 AS 'Tickets',
       H.[RiskAmount] AS 'Risk', A.[Agent], D.[RESULT], H.[WinAmount] AS 'Win', D.[IdGame], D.[IdWager]   
FROM GRADEDWAGERHEADER H, GRADEDWAGERDETAIL D, GRADEDGAME G, PERIOD P, AGENT A, WAGERTYPE W, PLAYER J
WHERE H.[IdWager]  = D.[IdWager] AND 
      D.[IdGame]   = G.[IdGame]  AND 
      H.[IdAgent]  = A.[IdAgent] AND
      H.[IdPlayer] = J.[IdPlayer] AND
      H.[IdWagerType] = W.[IdWagerType] AND
      D.[IdSport] = P.[IdSport] AND
      D.[Period]  = P.[NumberOfPeriod] AND 
      H.[IdAgent] = 5585 AND 
      D.[IdWager] IN(12683473,12690356,12689748,12703286) AND
      D.[RESULT]  = 1
GROUP BY G.[GradedDate], G.[VisitorNumber], G.[HomeNumber], G.[VisitorTeam], G.[HomeNumber], G.[HomeTeam], 
         P.[PeriodDescription], G.[VisitorScore], G.[HomeScore], H.[RiskAmount], A.[Agent],D.[RESULT], H.[WinAmount], D.[IdGame], D.[IdWager]
) AS t1
) t2 WHERE rn=1
) AS completa ORDER BY TIME ASC
 

[adryan310]

Gracias amigo me funciono muy bien...