USE [PRONOSTICO]
GO
/****** Object:  StoredProcedure [dbo].[spXMLGameAnotationTeamLISTForBaseBallLA]    Script Date: 04/13/2016 19:38:12 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[spXMLGameAnotationTeamLISTForBaseBallLA] 

  @LeagueId tinyint

AS

SET NOCOUNT ON

Declare @FechaActual  smalldatetime,
        @FechaNormal  smalldatetime,
        @GameDate     smalldatetime,
        @Ano          smallint,
        @Mes          tinyint,
        @Dia          tinyint,
        @Registros    smallint


SELECT @FechaActual = getdate()

SET @Ano = DATEPART(Year, @FechaActual)
SET @Mes = DATEPART(Month, @FechaActual)
SET @Dia = DATEPART(Day, @FechaActual)

SET @FechaNormal = convert(smalldatetime,
(
cast(@Ano as varchar(4)) + '-' +
cast(@Mes as varchar(2)) + '-' + 
cast(@Dia as varchar(2))
))

SELECT @Registros = COUNT(*) FROM GameMatchupTotalPrice gtp
INNER JOIN Game g on g.[id]=gtp.gameid
where DATEPART(Year, g.GameDate)  = DATEPART(Year, @FechaActual) AND
      DATEPART(Month, g.GameDate) = DATEPART(Month, @FechaActual) AND
      DATEPART(Day, g.GameDate)   = DATEPART(Day, @FechaActual) AND
      gtp.isstarted=1 AND gtp.IsActive = 1 AND g.LeagueId = @LeagueId

IF @Registros > 0
   BEGIN
     SET @GameDate = @FechaNormal
   END
ELSE
   BEGIN
    SET @GameDate = @FechaNormal - 1
   END


IF @Registros = 0
BEGIN
SELECT
gat.TeamId,
t.[Name],
g.[Id],
g.NoOrder,
gtp.HomeAway,
p1V = MAX(CASE WHEN gat.periodandtypebet = '311' and gtp.homeaway = 0 THEN gat.anotation else 0 END),
p2V = MAX(CASE WHEN gat.periodandtypebet = '321' and gtp.homeaway = 0 THEN gat.anotation else 0 END),
p3V = MAX(CASE WHEN gat.periodandtypebet = '331' and gtp.homeaway = 0 THEN gat.anotation else 0 END),
p4V = MAX(CASE WHEN gat.periodandtypebet = '341' and gtp.homeaway = 0 THEN gat.anotation else 0 END),
p5V = MAX(CASE WHEN gat.periodandtypebet = '351' and gtp.homeaway = 0 THEN gat.anotation else 0 END),
p6V = MAX(CASE WHEN gat.periodandtypebet = '361' and gtp.homeaway = 0 THEN gat.anotation else 0 END),
p7V = MAX(CASE WHEN gat.periodandtypebet = '371' and gtp.homeaway = 0 THEN gat.anotation else 0 END),
p8V = MAX(CASE WHEN gat.periodandtypebet = '381' and gtp.homeaway = 0 THEN gat.anotation else 0 END),
p9V = MAX(CASE WHEN gat.periodandtypebet = '391' and gtp.homeaway = 0 THEN gat.anotation else 0 END),
pxV = MAX(CASE WHEN gat.periodandtypebet = '401' and gtp.homeaway = 0 THEN gat.anotation else 0 END),

h1V = MAX(CASE WHEN gat.periodandtypebet = '111' and gtp.homeaway = 0 THEN gat.anotation else 0 END),
h2V = MAX(CASE WHEN gat.periodandtypebet = '121' and gtp.homeaway = 0 THEN gat.anotation else 0 END),

t1V = MAX(CASE WHEN gat.periodandtypebet = '211' and gtp.homeaway = 0 THEN gat.anotation else 0 END),
t2V = MAX(CASE WHEN gat.periodandtypebet = '221' and gtp.homeaway = 0 THEN gat.anotation else 0 END),
t3V = MAX(CASE WHEN gat.periodandtypebet = '231' and gtp.homeaway = 0 THEN gat.anotation else 0 END),

JCV = MAX(CASE WHEN gat.periodandtypebet = '101' and gtp.homeaway = 0 THEN gat.anotation else 0 END),

PAV = MAX(CASE WHEN gat.periodandtypebet = '101' and 
                    gat.typescoreId = '14'  and
                    gtp.homeaway = 0 THEN gat.anotation else 0 END),

UAV = MAX(CASE WHEN gat.periodandtypebet = '101' and 
                    gat.typescoreId      = '15'  and
                    gtp.homeaway = 0 THEN gat.anotation else 0 END),

p1l = MAX(CASE WHEN gat.periodandtypebet = '311' and gtp.homeaway = 1 THEN gat.anotation else 0 END),
p2l = MAX(CASE WHEN gat.periodandtypebet = '321' and gtp.homeaway = 1 THEN gat.anotation else 0 END),
p3l = MAX(CASE WHEN gat.periodandtypebet = '331' and gtp.homeaway = 1 THEN gat.anotation else 0 END),
p4l = MAX(CASE WHEN gat.periodandtypebet = '341' and gtp.homeaway = 1 THEN gat.anotation else 0 END),
p5l = MAX(CASE WHEN gat.periodandtypebet = '351' and gtp.homeaway = 1 THEN gat.anotation else 0 END),
p6l = MAX(CASE WHEN gat.periodandtypebet = '361' and gtp.homeaway = 1 THEN gat.anotation else 0 END),
p7l = MAX(CASE WHEN gat.periodandtypebet = '371' and gtp.homeaway = 1 THEN gat.anotation else 0 END),
p8l = MAX(CASE WHEN gat.periodandtypebet = '381' and gtp.homeaway = 1 THEN gat.anotation else 0 END),
p9l = MAX(CASE WHEN gat.periodandtypebet = '391' and gtp.homeaway = 1 THEN gat.anotation else 0 END),
pxl = MAX(CASE WHEN gat.periodandtypebet = '401' and gtp.homeaway = 1 THEN gat.anotation else 0 END),


h1l = MAX(CASE WHEN gat.periodandtypebet = '111' and gtp.homeaway = 1 THEN gat.anotation else 0 END),
h2l = MAX(CASE WHEN gat.periodandtypebet = '121' and gtp.homeaway = 1 THEN gat.anotation else 0 END),

t1l = MAX(CASE WHEN gat.periodandtypebet = '211' and gtp.homeaway = 1 THEN gat.anotation else 0 END),
t2l = MAX(CASE WHEN gat.periodandtypebet = '221' and gtp.homeaway = 1 THEN gat.anotation else 0 END),
t3l = MAX(CASE WHEN gat.periodandtypebet = '231' and gtp.homeaway = 1 THEN gat.anotation else 0 END),

JCl = MAX(CASE WHEN gat.periodandtypebet = '101' and gtp.homeaway = 1 THEN gat.anotation else 0 END),

PAl = MAX(CASE WHEN gat.periodandtypebet = '101' and 
                      gat.typescoreId      = '14'  and
                      gtp.homeaway = 1 THEN gat.anotation else 0 END),

UAl = MAX(CASE WHEN gat.periodandtypebet = '101' and 
                    gat.typescoreId      = '15'  and
                    gtp.homeaway = 1 THEN gat.anotation else 0 END),

PAS = MAX(CASE WHEN   gat.periodandtypebet = '101' and 
                      gat.typescoreId      = '14'  and
                      gtp.homeaway = 0 THEN gat.StatusId else 0 END),

UAS = MAX(CASE WHEN   gat.periodandtypebet = '101' and 
                      gat.typescoreId      = '15'  and
                      gtp.homeaway = 0 THEN gat.StatusId else 0 END),

sp1 = MAX(CASE WHEN gat.periodandtypebet = '311' and gtp.homeaway = 0 THEN gat.StatusId  else 0 END),
sp2 = MAX(CASE WHEN gat.periodandtypebet = '321' and gtp.homeaway = 0 THEN gat.StatusId else 0 END),
sp3 = MAX(CASE WHEN gat.periodandtypebet = '331' and gtp.homeaway = 0 THEN gat.StatusId else 0 END),
sp4 = MAX(CASE WHEN gat.periodandtypebet = '341' and gtp.homeaway = 0 THEN gat.StatusId else 0 END),
sp5 = MAX(CASE WHEN gat.periodandtypebet = '351' and gtp.homeaway = 0 THEN gat.StatusId else 0 END),
sp6 = MAX(CASE WHEN gat.periodandtypebet = '361' and gtp.homeaway = 0 THEN gat.StatusId else 0 END),
sp7 = MAX(CASE WHEN gat.periodandtypebet = '371' and gtp.homeaway = 0 THEN gat.StatusId else 0 END),
sp8 = MAX(CASE WHEN gat.periodandtypebet = '381' and gtp.homeaway = 0 THEN gat.StatusId else 0 END),
sp9 = MAX(CASE WHEN gat.periodandtypebet = '391' and gtp.homeaway = 0 THEN gat.StatusId else 0 END),
spx = MAX(CASE WHEN gat.periodandtypebet = '401' and gtp.homeaway = 0 THEN gat.StatusId else 0 END),
sh1 = MAX(CASE WHEN gat.periodandtypebet = '111' and gtp.homeaway = 0 THEN gat.StatusId else 0 END),
sh2 = MAX(CASE WHEN gat.periodandtypebet = '121' and gtp.homeaway = 0 THEN gat.StatusId else 0 END),
sjc = MAX(CASE WHEN gat.periodandtypebet = '101' and gtp.homeaway = 0 THEN gat.StatusId else 0 END)

FROM Historygameanotationteam gat
INNER JOIN HistoryGameMatchupTotalPrice gtp ON gtp.GameId = gat.GameId and
                                        gtp.TeamId = gat.TeamId and 
                                        gtp.IsStarted = 1       and
                                        gtp.IsActive  = 1
INNER JOIN HistoryGame g ON g.[Id] = gtp.GameId AND g.LeagueId = @LeagueId
INNER JOIN Team        t ON t.Code = gat.TeamId
WHERE g.LeagueId = @LeagueId AND g.GameDate = @GameDate
/*WHERE g.LeagueId = @LeagueId AND gat.TeamId <= 4014 AND g.GameDate = @GameDate*/
GROUP BY
gat.teamId,
t.[Name],
g.[Id],
g.NoOrder,
gtp.HomeAway
ORDER BY g.NoOrder, g.[Id], gtp.HomeAway 
END
ELSE
BEGIN
SELECT 
gat.TeamId,
t.[Name],
g.[Id],
g.NoOrder,
gtp.HomeAway,

p1V = MAX(CASE WHEN gat.periodandtypebet = '311' and gtp.homeaway = 0 THEN gat.anotation else 0 END),
p2V = MAX(CASE WHEN gat.periodandtypebet = '321' and gtp.homeaway = 0 THEN gat.anotation else 0 END),
p3V = MAX(CASE WHEN gat.periodandtypebet = '331' and gtp.homeaway = 0 THEN gat.anotation else 0 END),
p4V = MAX(CASE WHEN gat.periodandtypebet = '341' and gtp.homeaway = 0 THEN gat.anotation else 0 END),
p5V = MAX(CASE WHEN gat.periodandtypebet = '351' and gtp.homeaway = 0 THEN gat.anotation else 0 END),
p6V = MAX(CASE WHEN gat.periodandtypebet = '361' and gtp.homeaway = 0 THEN gat.anotation else 0 END),
p7V = MAX(CASE WHEN gat.periodandtypebet = '371' and gtp.homeaway = 0 THEN gat.anotation else 0 END),
p8V = MAX(CASE WHEN gat.periodandtypebet = '381' and gtp.homeaway = 0 THEN gat.anotation else 0 END),
p9V = MAX(CASE WHEN gat.periodandtypebet = '391' and gtp.homeaway = 0 THEN gat.anotation else 0 END),
pxV = MAX(CASE WHEN gat.periodandtypebet = '401' and gtp.homeaway = 0 THEN gat.anotation else 0 END),

h1V = MAX(CASE WHEN gat.periodandtypebet = '111' and gtp.homeaway = 0 THEN gat.anotation else 0 END),
h2V = MAX(CASE WHEN gat.periodandtypebet = '121' and gtp.homeaway = 0 THEN gat.anotation else 0 END),

t1V = MAX(CASE WHEN gat.periodandtypebet = '211' and gtp.homeaway = 0 THEN gat.anotation else 0 END),
t2V = MAX(CASE WHEN gat.periodandtypebet = '221' and gtp.homeaway = 0 THEN gat.anotation else 0 END),
t3V = MAX(CASE WHEN gat.periodandtypebet = '231' and gtp.homeaway = 0 THEN gat.anotation else 0 END),

JCV = MAX(CASE WHEN gat.periodandtypebet = '101' and gtp.homeaway = 0 THEN gat.anotation else 0 END),

PAV = MAX(CASE WHEN gat.periodandtypebet = '101' and 
                    gat.typescoreId = '14'  and
                    gtp.homeaway = 0 THEN gat.anotation else 0 END),

UAV = MAX(CASE WHEN gat.periodandtypebet = '101' and 
                    gat.typescoreId      = '15'  and
                    gtp.homeaway = 0 THEN gat.anotation else 0 END),

p1l = MAX(CASE WHEN gat.periodandtypebet = '311' and gtp.homeaway = 1 THEN gat.anotation else 0 END),
p2l = MAX(CASE WHEN gat.periodandtypebet = '321' and gtp.homeaway = 1 THEN gat.anotation else 0 END),
p3l = MAX(CASE WHEN gat.periodandtypebet = '331' and gtp.homeaway = 1 THEN gat.anotation else 0 END),
p4l = MAX(CASE WHEN gat.periodandtypebet = '341' and gtp.homeaway = 1 THEN gat.anotation else 0 END),
p5l = MAX(CASE WHEN gat.periodandtypebet = '351' and gtp.homeaway = 1 THEN gat.anotation else 0 END),
p6l = MAX(CASE WHEN gat.periodandtypebet = '361' and gtp.homeaway = 1 THEN gat.anotation else 0 END),
p7l = MAX(CASE WHEN gat.periodandtypebet = '371' and gtp.homeaway = 1 THEN gat.anotation else 0 END),
p8l = MAX(CASE WHEN gat.periodandtypebet = '381' and gtp.homeaway = 1 THEN gat.anotation else 0 END),
p9l = MAX(CASE WHEN gat.periodandtypebet = '391' and gtp.homeaway = 1 THEN gat.anotation else 0 END),
pxl = MAX(CASE WHEN gat.periodandtypebet = '401' and gtp.homeaway = 1 THEN gat.anotation else 0 END),


h1l = MAX(CASE WHEN gat.periodandtypebet = '111' and gtp.homeaway = 1 THEN gat.anotation else 0 END),
h2l = MAX(CASE WHEN gat.periodandtypebet = '121' and gtp.homeaway = 1 THEN gat.anotation else 0 END),

t1l = MAX(CASE WHEN gat.periodandtypebet = '211' and gtp.homeaway = 1 THEN gat.anotation else 0 END),
t2l = MAX(CASE WHEN gat.periodandtypebet = '221' and gtp.homeaway = 1 THEN gat.anotation else 0 END),
t3l = MAX(CASE WHEN gat.periodandtypebet = '231' and gtp.homeaway = 1 THEN gat.anotation else 0 END),

JCl = MAX(CASE WHEN gat.periodandtypebet = '101' and gtp.homeaway = 1 THEN gat.anotation else 0 END),

PAl = MAX(CASE WHEN gat.periodandtypebet = '101' and 
                      gat.typescoreId      = '14'  and
                      gtp.homeaway = 1 THEN gat.anotation else 0 END),

UAl = MAX(CASE WHEN gat.periodandtypebet = '101' and 
                    gat.typescoreId      = '15'  and
                    gtp.homeaway = 1 THEN gat.anotation else 0 END),

PAS = MAX(CASE WHEN   gat.periodandtypebet = '101' and 
                      gat.typescoreId      = '14'  and
                      gtp.homeaway = 0 THEN gat.StatusId else 0 END),

UAS = MAX(CASE WHEN   gat.periodandtypebet = '101' and 
     gat.typescoreId      = '15'  and
                      gtp.homeaway = 0 THEN gat.StatusId else 0 END),

sp1 = MAX(CASE WHEN gat.periodandtypebet = '311' and gtp.homeaway = 0 THEN gat.StatusId  else 0 END),
sp2 = MAX(CASE WHEN gat.periodandtypebet = '321' and gtp.homeaway = 0 THEN gat.StatusId else 0 END),
sp3 = MAX(CASE WHEN gat.periodandtypebet = '331' and gtp.homeaway = 0 THEN gat.StatusId else 0 END),
sp4 = MAX(CASE WHEN gat.periodandtypebet = '341' and gtp.homeaway = 0 THEN gat.StatusId else 0 END),
sp5 = MAX(CASE WHEN gat.periodandtypebet = '351' and gtp.homeaway = 0 THEN gat.StatusId else 0 END),
sp6 = MAX(CASE WHEN gat.periodandtypebet = '361' and gtp.homeaway = 0 THEN gat.StatusId else 0 END),
sp7 = MAX(CASE WHEN gat.periodandtypebet = '371' and gtp.homeaway = 0 THEN gat.StatusId else 0 END),
sp8 = MAX(CASE WHEN gat.periodandtypebet = '381' and gtp.homeaway = 0 THEN gat.StatusId else 0 END),
sp9 = MAX(CASE WHEN gat.periodandtypebet = '391' and gtp.homeaway = 0 THEN gat.StatusId else 0 END),
spx = MAX(CASE WHEN gat.periodandtypebet = '401' and gtp.homeaway = 0 THEN gat.StatusId else 0 END),
sh1 = MAX(CASE WHEN gat.periodandtypebet = '111' and gtp.homeaway = 0 THEN gat.StatusId else 0 END),
sh2 = MAX(CASE WHEN gat.periodandtypebet = '121' and gtp.homeaway = 0 THEN gat.StatusId else 0 END),
sjc = MAX(CASE WHEN gat.periodandtypebet = '101' and gtp.homeaway = 0 THEN gat.StatusId else 0 END)

FROM gameanotationteam gat
INNER JOIN GameMatchupTotalPrice gtp ON gtp.GameId = gat.GameId and
                                        gtp.TeamId = gat.TeamId and 
                                        gtp.IsStarted = 1       and
                                        gtp.IsActive  = 1
INNER JOIN Game g ON g.[Id] = gtp.GameId AND g.LeagueId = @LeagueId
INNER JOIN Team        t ON t.Code = gat.TeamId
WHERE g.LeagueId = @LeagueId AND g.GameDate = @GameDate
/*WHERE g.LeagueId = @LeagueId AND gat.TeamId <= 4014 AND g.GameDate = @GameDate*/
GROUP BY
gat.teamId,
t.[Name],
g.[Id],
g.NoOrder,
gtp.HomeAway
ORDER BY g.NoOrder, g.[Id], gtp.HomeAway 
END

























GO
/****** Object:  StoredProcedure [dbo].[spXMLGameAnotationTeamLISTForBaseBallLN]    Script Date: 04/13/2016 19:38:13 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[spXMLGameAnotationTeamLISTForBaseBallLN] 

  @LeagueId tinyint

AS

SET NOCOUNT ON

Declare @FechaActual  smalldatetime,
        @FechaNormal  smalldatetime,
        @GameDate     smalldatetime,
        @Ano          smallint,
        @Mes          tinyint,
        @Dia          tinyint,
        @Registros    smallint


SELECT @FechaActual = getdate()

SET @Ano = DATEPART(Year, @FechaActual)
SET @Mes = DATEPART(Month, @FechaActual)
SET @Dia = DATEPART(Day, @FechaActual)

SET @FechaNormal = convert(smalldatetime,
(
cast(@Ano as varchar(4)) + '-' +
cast(@Mes as varchar(2)) + '-' + 
cast(@Dia as varchar(2))
))

SELECT @Registros = COUNT(*) FROM GameMatchupTotalPrice gtp
INNER JOIN Game g on g.[id]=gtp.gameid
where DATEPART(Year, g.GameDate)  = DATEPART(Year, @FechaActual) AND
      DATEPART(Month, g.GameDate) = DATEPART(Month, @FechaActual) AND
      DATEPART(Day, g.GameDate)   = DATEPART(Day, @FechaActual) AND
      gtp.isstarted=1 AND gtp.IsActive = 1 AND g.LeagueId = @LeagueId



IF @Registros > 0
   BEGIN
     SET @GameDate = @FechaNormal
   END
ELSE
   BEGIN
    SET @GameDate = @FechaNormal - 1
   END

IF @Registros = 0
BEGIN
SELECT 
gat.TeamId,
t.[Name],
g.[Id],
g.NoOrder,
gtp.HomeAway,
p1V = MAX(CASE WHEN gat.periodandtypebet = '311' and gtp.homeaway = 0 THEN gat.anotation else 0 END),
p2V = MAX(CASE WHEN gat.periodandtypebet = '321' and gtp.homeaway = 0 THEN gat.anotation else 0 END),
p3V = MAX(CASE WHEN gat.periodandtypebet = '331' and gtp.homeaway = 0 THEN gat.anotation else 0 END),
p4V = MAX(CASE WHEN gat.periodandtypebet = '341' and gtp.homeaway = 0 THEN gat.anotation else 0 END),
p5V = MAX(CASE WHEN gat.periodandtypebet = '351' and gtp.homeaway = 0 THEN gat.anotation else 0 END),
p6V = MAX(CASE WHEN gat.periodandtypebet = '361' and gtp.homeaway = 0 THEN gat.anotation else 0 END),
p7V = MAX(CASE WHEN gat.periodandtypebet = '371' and gtp.homeaway = 0 THEN gat.anotation else 0 END),
p8V = MAX(CASE WHEN gat.periodandtypebet = '381' and gtp.homeaway = 0 THEN gat.anotation else 0 END),
p9V = MAX(CASE WHEN gat.periodandtypebet = '391' and gtp.homeaway = 0 THEN gat.anotation else 0 END),
pxV = MAX(CASE WHEN gat.periodandtypebet = '401' and gtp.homeaway = 0 THEN gat.anotation else 0 END),

h1V = MAX(CASE WHEN gat.periodandtypebet = '111' and gtp.homeaway = 0 THEN gat.anotation else 0 END),
h2V = MAX(CASE WHEN gat.periodandtypebet = '121' and gtp.homeaway = 0 THEN gat.anotation else 0 END),

t1V = MAX(CASE WHEN gat.periodandtypebet = '211' and gtp.homeaway = 0 THEN gat.anotation else 0 END),
t2V = MAX(CASE WHEN gat.periodandtypebet = '221' and gtp.homeaway = 0 THEN gat.anotation else 0 END),
t3V = MAX(CASE WHEN gat.periodandtypebet = '231' and gtp.homeaway = 0 THEN gat.anotation else 0 END),

JCV = MAX(CASE WHEN gat.periodandtypebet = '101' and gtp.homeaway = 0 THEN gat.anotation else 0 END),

PAV = MAX(CASE WHEN gat.periodandtypebet = '101' and 
                    gat.typescoreId = '14'  and
                    gtp.homeaway = 0 THEN gat.anotation else 0 END),

UAV = MAX(CASE WHEN gat.periodandtypebet = '101' and 
                    gat.typescoreId      = '15'  and
                    gtp.homeaway = 0 THEN gat.anotation else 0 END),

p1l = MAX(CASE WHEN gat.periodandtypebet = '311' and gtp.homeaway = 1 THEN gat.anotation else 0 END),
p2l = MAX(CASE WHEN gat.periodandtypebet = '321' and gtp.homeaway = 1 THEN gat.anotation else 0 END),
p3l = MAX(CASE WHEN gat.periodandtypebet = '331' and gtp.homeaway = 1 THEN gat.anotation else 0 END),
p4l = MAX(CASE WHEN gat.periodandtypebet = '341' and gtp.homeaway = 1 THEN gat.anotation else 0 END),
p5l = MAX(CASE WHEN gat.periodandtypebet = '351' and gtp.homeaway = 1 THEN gat.anotation else 0 END),
p6l = MAX(CASE WHEN gat.periodandtypebet = '361' and gtp.homeaway = 1 THEN gat.anotation else 0 END),
p7l = MAX(CASE WHEN gat.periodandtypebet = '371' and gtp.homeaway = 1 THEN gat.anotation else 0 END),
p8l = MAX(CASE WHEN gat.periodandtypebet = '381' and gtp.homeaway = 1 THEN gat.anotation else 0 END),
p9l = MAX(CASE WHEN gat.periodandtypebet = '391' and gtp.homeaway = 1 THEN gat.anotation else 0 END),
pxl = MAX(CASE WHEN gat.periodandtypebet = '401' and gtp.homeaway = 1 THEN gat.anotation else 0 END),


h1l = MAX(CASE WHEN gat.periodandtypebet = '111' and gtp.homeaway = 1 THEN gat.anotation else 0 END),
h2l = MAX(CASE WHEN gat.periodandtypebet = '121' and gtp.homeaway = 1 THEN gat.anotation else 0 END),

t1l = MAX(CASE WHEN gat.periodandtypebet = '211' and gtp.homeaway = 1 THEN gat.anotation else 0 END),
t2l = MAX(CASE WHEN gat.periodandtypebet = '221' and gtp.homeaway = 1 THEN gat.anotation else 0 END),
t3l = MAX(CASE WHEN gat.periodandtypebet = '231' and gtp.homeaway = 1 THEN gat.anotation else 0 END),

JCl = MAX(CASE WHEN gat.periodandtypebet = '101' and gtp.homeaway = 1 THEN gat.anotation else 0 END),

PAl = MAX(CASE WHEN gat.periodandtypebet = '101' and 
                      gat.typescoreId      = '14'  and
                      gtp.homeaway = 1 THEN gat.anotation else 0 END),

UAl = MAX(CASE WHEN gat.periodandtypebet = '101' and 
                    gat.typescoreId      = '15'  and
                    gtp.homeaway = 1 THEN gat.anotation else 0 END),

PAS = MAX(CASE WHEN   gat.periodandtypebet = '101' and 
                      gat.typescoreId      = '14'  and
                      gtp.homeaway = 0 THEN gat.StatusId else 0 END),

UAS = MAX(CASE WHEN   gat.periodandtypebet = '101' and 
                      gat.typescoreId      = '15'  and
                      gtp.homeaway = 0 THEN gat.StatusId else 0 END),

sp1 = MAX(CASE WHEN gat.periodandtypebet = '311' and gtp.homeaway = 0 THEN gat.StatusId  else 0 END),
sp2 = MAX(CASE WHEN gat.periodandtypebet = '321' and gtp.homeaway = 0 THEN gat.StatusId else 0 END),
sp3 = MAX(CASE WHEN gat.periodandtypebet = '331' and gtp.homeaway = 0 THEN gat.StatusId else 0 END),
sp4 = MAX(CASE WHEN gat.periodandtypebet = '341' and gtp.homeaway = 0 THEN gat.StatusId else 0 END),
sp5 = MAX(CASE WHEN gat.periodandtypebet = '351' and gtp.homeaway = 0 THEN gat.StatusId else 0 END),
sp6 = MAX(CASE WHEN gat.periodandtypebet = '361' and gtp.homeaway = 0 THEN gat.StatusId else 0 END),
sp7 = MAX(CASE WHEN gat.periodandtypebet = '371' and gtp.homeaway = 0 THEN gat.StatusId else 0 END),
sp8 = MAX(CASE WHEN gat.periodandtypebet = '381' and gtp.homeaway = 0 THEN gat.StatusId else 0 END),
sp9 = MAX(CASE WHEN gat.periodandtypebet = '391' and gtp.homeaway = 0 THEN gat.StatusId else 0 END),
spx = MAX(CASE WHEN gat.periodandtypebet = '401' and gtp.homeaway = 0 THEN gat.StatusId else 0 END),
sh1 = MAX(CASE WHEN gat.periodandtypebet = '111' and gtp.homeaway = 0 THEN gat.StatusId else 0 END),
sh2 = MAX(CASE WHEN gat.periodandtypebet = '121' and gtp.homeaway = 0 THEN gat.StatusId else 0 END),
sjc = MAX(CASE WHEN gat.periodandtypebet = '101' and gtp.homeaway = 0 THEN gat.StatusId else 0 END)

FROM Historygameanotationteam gat
INNER JOIN HistoryGameMatchupTotalPrice gtp ON gtp.GameId = gat.GameId and
                                        gtp.TeamId = gat.TeamId and
                                        gtp.IsStarted = 1       and
                                        gtp.IsActive  = 1
                                       
INNER JOIN HistoryGame g ON g.[Id] = gtp.GameId AND g.LeagueId = @LeagueId
INNER JOIN Team t ON t.Code = gat.TeamId
WHERE g.LeagueId = @LeagueId AND g.GameDate = @GameDate
/*WHERE g.LeagueId = @LeagueId AND gat.TeamId > 4014 AND g.GameDate = @GameDate*/
GROUP BY
gat.teamId,
t.[Name],
g.[Id],
g.NoOrder,
gtp.HomeAway
ORDER BY g.NoOrder, g.[Id], gtp.HomeAway 
END

ELSE
BEGIN
SELECT 

gat.TeamId,
t.[Name],
g.[Id],
g.NoOrder,
gtp.HomeAway,
p1V = MAX(CASE WHEN gat.periodandtypebet = '311' and gtp.homeaway = 0 THEN gat.anotation else 0 END),
p2V = MAX(CASE WHEN gat.periodandtypebet = '321' and gtp.homeaway = 0 THEN gat.anotation else 0 END),
p3V = MAX(CASE WHEN gat.periodandtypebet = '331' and gtp.homeaway = 0 THEN gat.anotation else 0 END),
p4V = MAX(CASE WHEN gat.periodandtypebet = '341' and gtp.homeaway = 0 THEN gat.anotation else 0 END),
p5V = MAX(CASE WHEN gat.periodandtypebet = '351' and gtp.homeaway = 0 THEN gat.anotation else 0 END),
p6V = MAX(CASE WHEN gat.periodandtypebet = '361' and gtp.homeaway = 0 THEN gat.anotation else 0 END),
p7V = MAX(CASE WHEN gat.periodandtypebet = '371' and gtp.homeaway = 0 THEN gat.anotation else 0 END),
p8V = MAX(CASE WHEN gat.periodandtypebet = '381' and gtp.homeaway = 0 THEN gat.anotation else 0 END),
p9V = MAX(CASE WHEN gat.periodandtypebet = '391' and gtp.homeaway = 0 THEN gat.anotation else 0 END),
pxV = MAX(CASE WHEN gat.periodandtypebet = '401' and gtp.homeaway = 0 THEN gat.anotation else 0 END),

h1V = MAX(CASE WHEN gat.periodandtypebet = '111' and gtp.homeaway = 0 THEN gat.anotation else 0 END),
h2V = MAX(CASE WHEN gat.periodandtypebet = '121' and gtp.homeaway = 0 THEN gat.anotation else 0 END),

t1V = MAX(CASE WHEN gat.periodandtypebet = '211' and gtp.homeaway = 0 THEN gat.anotation else 0 END),
t2V = MAX(CASE WHEN gat.periodandtypebet = '221' and gtp.homeaway = 0 THEN gat.anotation else 0 END),
t3V = MAX(CASE WHEN gat.periodandtypebet = '231' and gtp.homeaway = 0 THEN gat.anotation else 0 END),

JCV = MAX(CASE WHEN gat.periodandtypebet = '101' and gtp.homeaway = 0 THEN gat.anotation else 0 END),

PAV = MAX(CASE WHEN gat.periodandtypebet = '101' and 
                    gat.typescoreId = '14'  and
                    gtp.homeaway = 0 THEN gat.anotation else 0 END),

UAV = MAX(CASE WHEN gat.periodandtypebet = '101' and 
                    gat.typescoreId      = '15'  and
                    gtp.homeaway = 0 THEN gat.anotation else 0 END),

p1l = MAX(CASE WHEN gat.periodandtypebet = '311' and gtp.homeaway = 1 THEN gat.anotation else 0 END),
p2l = MAX(CASE WHEN gat.periodandtypebet = '321' and gtp.homeaway = 1 THEN gat.anotation else 0 END),
p3l = MAX(CASE WHEN gat.periodandtypebet = '331' and gtp.homeaway = 1 THEN gat.anotation else 0 END),
p4l = MAX(CASE WHEN gat.periodandtypebet = '341' and gtp.homeaway = 1 THEN gat.anotation else 0 END),
p5l = MAX(CASE WHEN gat.periodandtypebet = '351' and gtp.homeaway = 1 THEN gat.anotation else 0 END),
p6l = MAX(CASE WHEN gat.periodandtypebet = '361' and gtp.homeaway = 1 THEN gat.anotation else 0 END),
p7l = MAX(CASE WHEN gat.periodandtypebet = '371' and gtp.homeaway = 1 THEN gat.anotation else 0 END),
p8l = MAX(CASE WHEN gat.periodandtypebet = '381' and gtp.homeaway = 1 THEN gat.anotation else 0 END),
p9l = MAX(CASE WHEN gat.periodandtypebet = '391' and gtp.homeaway = 1 THEN gat.anotation else 0 END),
pxl = MAX(CASE WHEN gat.periodandtypebet = '401' and gtp.homeaway = 1 THEN gat.anotation else 0 END),


h1l = MAX(CASE WHEN gat.periodandtypebet = '111' and gtp.homeaway = 1 THEN gat.anotation else 0 END),
h2l = MAX(CASE WHEN gat.periodandtypebet = '121' and gtp.homeaway = 1 THEN gat.anotation else 0 END),

t1l = MAX(CASE WHEN gat.periodandtypebet = '211' and gtp.homeaway = 1 THEN gat.anotation else 0 END),
t2l = MAX(CASE WHEN gat.periodandtypebet = '221' and gtp.homeaway = 1 THEN gat.anotation else 0 END),
t3l = MAX(CASE WHEN gat.periodandtypebet = '231' and gtp.homeaway = 1 THEN gat.anotation else 0 END),

JCl = MAX(CASE WHEN gat.periodandtypebet = '101' and gtp.homeaway = 1 THEN gat.anotation else 0 END),

PAl = MAX(CASE WHEN gat.periodandtypebet = '101' and 
                      gat.typescoreId      = '14'  and
                      gtp.homeaway = 1 THEN gat.anotation else 0 END),

UAl = MAX(CASE WHEN gat.periodandtypebet = '101' and 
                    gat.typescoreId      = '15'  and
                    gtp.homeaway = 1 THEN gat.anotation else 0 END),

PAS = MAX(CASE WHEN   gat.periodandtypebet = '101' and 
                      gat.typescoreId      = '14'  and
                      gtp.homeaway = 0 THEN gat.StatusId else 0 END),

UAS = MAX(CASE WHEN   gat.periodandtypebet = '101' and 
                      gat.typescoreId      = '15'  and
                      gtp.homeaway = 0 THEN gat.StatusId else 0 END),

sp1 = MAX(CASE WHEN gat.periodandtypebet = '311' and gtp.homeaway = 0 THEN gat.StatusId  else 0 END),
sp2 = MAX(CASE WHEN gat.periodandtypebet = '321' and gtp.homeaway = 0 THEN gat.StatusId else 0 END),
sp3 = MAX(CASE WHEN gat.periodandtypebet = '331' and gtp.homeaway = 0 THEN gat.StatusId else 0 END),
sp4 = MAX(CASE WHEN gat.periodandtypebet = '341' and gtp.homeaway = 0 THEN gat.StatusId else 0 END),
sp5 = MAX(CASE WHEN gat.periodandtypebet = '351' and gtp.homeaway = 0 THEN gat.StatusId else 0 END),
sp6 = MAX(CASE WHEN gat.periodandtypebet = '361' and gtp.homeaway = 0 THEN gat.StatusId else 0 END),
sp7 = MAX(CASE WHEN gat.periodandtypebet = '371' and gtp.homeaway = 0 THEN gat.StatusId else 0 END),
sp8 = MAX(CASE WHEN gat.periodandtypebet = '381' and gtp.homeaway = 0 THEN gat.StatusId else 0 END),
sp9 = MAX(CASE WHEN gat.periodandtypebet = '391' and gtp.homeaway = 0 THEN gat.StatusId else 0 END),
spx = MAX(CASE WHEN gat.periodandtypebet = '401' and gtp.homeaway = 0 THEN gat.StatusId else 0 END),
sh1 = MAX(CASE WHEN gat.periodandtypebet = '111' and gtp.homeaway = 0 THEN gat.StatusId else 0 END),
sh2 = MAX(CASE WHEN gat.periodandtypebet = '121' and gtp.homeaway = 0 THEN gat.StatusId else 0 END),
sjc = MAX(CASE WHEN gat.periodandtypebet = '101' and gtp.homeaway = 0 THEN gat.StatusId else 0 END)

FROM gameanotationteam gat
INNER JOIN GameMatchupTotalPrice gtp ON gtp.GameId = gat.GameId and
                                        gtp.TeamId = gat.TeamId and
                                        gtp.IsStarted = 1       and
                                        gtp.IsActive  = 1
                                       
INNER JOIN Game g ON g.[Id] = gtp.GameId AND g.LeagueId = @LeagueId
INNER JOIN Team t ON t.Code = gat.TeamId
WHERE g.LeagueId = @LeagueId AND g.GameDate = @GameDate
/*WHERE g.LeagueId = @LeagueId AND gat.TeamId > 4014 AND g.GameDate = @GameDate*/
GROUP BY
gat.teamId,
t.[Name],
g.[Id],
g.NoOrder,
gtp.HomeAway
ORDER BY g.NoOrder, g.[Id], gtp.HomeAway 
END




























GO
/****** Object:  StoredProcedure [dbo].[spXMLGameAnotationTeamLISTForBasketBall]    Script Date: 04/13/2016 19:38:13 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[spXMLGameAnotationTeamLISTForBasketBall]

 @LeagueId tinyint

AS

SET NOCOUNT ON

Declare @FechaActual  smalldatetime,
        @FechaNormal  smalldatetime,
        @GameDate     smalldatetime,
        @Ano          smallint,
        @Mes          tinyint,
        @Dia          tinyint,
        @Registros    smallint


SELECT @FechaActual = getdate()

SET @Ano = DATEPART(Year, @FechaActual)
SET @Mes = DATEPART(Month, @FechaActual)
SET @Dia = DATEPART(Day, @FechaActual)

SET @FechaNormal = convert(smalldatetime,
(
cast(@Ano as varchar(4)) + '-' +
cast(@Mes as varchar(2)) + '-' + 
cast(@Dia as varchar(2))
))

SELECT @Registros = COUNT(*) FROM GameMatchupTotalPrice gtp
INNER JOIN Game g on g.[id]=gtp.gameid
where DATEPART(Year, g.GameDate)  = DATEPART(Year, @FechaActual) AND
      DATEPART(Month, g.GameDate) = DATEPART(Month, @FechaActual) AND
      DATEPART(Day, g.GameDate)   = DATEPART(Day, @FechaActual) AND
      gtp.isstarted=1 AND gtp.IsActive = 1



IF @Registros > 0
   BEGIN
     SET @GameDate = @FechaNormal
   END
ELSE
   BEGIN
    SET @GameDate = @FechaNormal - 1
   END

IF @Registros = 0
BEGIN
SELECT 

gat.TeamId,
t.[Name],
g.[Id],
g.NoOrder,
gtp.HomeAway,
p1V = MAX(CASE WHEN gat.periodandtypebet = '212' and gtp.homeaway = 0 THEN gat.anotation else 0 END),
p2V = MAX(CASE WHEN gat.periodandtypebet = '222' and gtp.homeaway = 0 THEN gat.anotation else 0 END),
p3V = MAX(CASE WHEN gat.periodandtypebet = '232' and gtp.homeaway = 0 THEN gat.anotation else 0 END),
p4V = MAX(CASE WHEN gat.periodandtypebet = '242' and gtp.homeaway = 0 THEN gat.anotation else 0 END),
pxV = MAX(CASE WHEN gat.periodandtypebet = '402' and gtp.homeaway = 0 THEN gat.anotation else 0 END),
h1V = MAX(CASE WHEN gat.periodandtypebet = '112' and gtp.homeaway = 0 THEN gat.anotation else 0 END),
h2V = MAX(CASE WHEN gat.periodandtypebet = '122' and gtp.homeaway = 0 THEN gat.anotation else 0 END),
JCV = MAX(CASE WHEN gat.periodandtypebet = '102' and gtp.homeaway = 0 THEN gat.anotation else 0 END),

sp1 = MAX(CASE WHEN gat.periodandtypebet = '212' and gtp.homeaway = 0 THEN gat.StatusId  else 0 END),
sp2 = MAX(CASE WHEN gat.periodandtypebet = '222' and gtp.homeaway = 0 THEN gat.StatusId else 0 END),
sp3 = MAX(CASE WHEN gat.periodandtypebet = '232' and gtp.homeaway = 0 THEN gat.StatusId else 0 END),
sp4 = MAX(CASE WHEN gat.periodandtypebet = '242' and gtp.homeaway = 0 THEN gat.StatusId else 0 END),
spx = MAX(CASE WHEN gat.periodandtypebet = '402' and gtp.homeaway = 0 THEN gat.StatusId else 0 END),
sh1 = MAX(CASE WHEN gat.periodandtypebet = '112' and gtp.homeaway = 0 THEN gat.StatusId else 0 END),
sh2 = MAX(CASE WHEN gat.periodandtypebet = '122' and gtp.homeaway = 0 THEN gat.StatusId else 0 END),
sjc = MAX(CASE WHEN gat.periodandtypebet = '102' and gtp.homeaway = 0 THEN gat.StatusId else 0 END),

p1l = MAX(CASE WHEN gat.periodandtypebet = '212' and gtp.homeaway = 1 THEN gat.anotation else 0 END),
p2l = MAX(CASE WHEN gat.periodandtypebet = '222' and gtp.homeaway = 1 THEN gat.anotation else 0 END),
p3l = MAX(CASE WHEN gat.periodandtypebet = '232' and gtp.homeaway = 1 THEN gat.anotation else 0 END),
p4l = MAX(CASE WHEN gat.periodandtypebet = '242' and gtp.homeaway = 1 THEN gat.anotation else 0 END),
pxl = MAX(CASE WHEN gat.periodandtypebet = '402' and gtp.homeaway = 1 THEN gat.anotation else 0 END),

h1l = MAX(CASE WHEN gat.periodandtypebet = '112' and gtp.homeaway = 1 THEN gat.anotation else 0 END),
h2l = MAX(CASE WHEN gat.periodandtypebet = '122' and gtp.homeaway = 1 THEN gat.anotation else 0 END),
JCL = MAX(CASE WHEN gat.periodandtypebet = '102' and gtp.homeaway = 1 THEN gat.anotation else 0 END)

FROM Historygameanotationteam gat
INNER JOIN HistoryGameMatchupTotalPrice gtp ON gtp.GameId = gat.GameId and
                                        gtp.TeamId = gat.TeamId and
                                        gtp.IsStarted = 1       and 
                                        gtp.IsActive   = 1
INNER JOIN HistoryGame g ON g.[Id] = gtp.GameId AND g.LeagueId = @LeagueId
INNER JOIN Team T ON   t.Code = gat.TeamId
WHERE g.LeagueId = @LeagueId AND g.GameDate = @GameDate
GROUP BY
gat.teamId,
t.[Name],
g.[Id],
g.NoOrder,
gtp.HomeAway
ORDER BY 
g.NoOrder,
g.[Id],
gtp.HomeAway
END
ELSE
BEGIN
SELECT 

gat.TeamId,
t.[Name],
g.[Id],
g.NoOrder,
gtp.HomeAway,
p1V = MAX(CASE WHEN gat.periodandtypebet = '212' and gtp.homeaway = 0 THEN gat.anotation else 0 END),
p2V = MAX(CASE WHEN gat.periodandtypebet = '222' and gtp.homeaway = 0 THEN gat.anotation else 0 END),
p3V = MAX(CASE WHEN gat.periodandtypebet = '232' and gtp.homeaway = 0 THEN gat.anotation else 0 END),
p4V = MAX(CASE WHEN gat.periodandtypebet = '242' and gtp.homeaway = 0 THEN gat.anotation else 0 END),
pxV = MAX(CASE WHEN gat.periodandtypebet = '402' and gtp.homeaway = 0 THEN gat.anotation else 0 END),
h1V = MAX(CASE WHEN gat.periodandtypebet = '112' and gtp.homeaway = 0 THEN gat.anotation else 0 END),
h2V = MAX(CASE WHEN gat.periodandtypebet = '122' and gtp.homeaway = 0 THEN gat.anotation else 0 END),
JCV = MAX(CASE WHEN gat.periodandtypebet = '102' and gtp.homeaway = 0 THEN gat.anotation else 0 END),

sp1 = MAX(CASE WHEN gat.periodandtypebet = '212' and gtp.homeaway = 0 THEN gat.StatusId  else 0 END),
sp2 = MAX(CASE WHEN gat.periodandtypebet = '222' and gtp.homeaway = 0 THEN gat.StatusId else 0 END),
sp3 = MAX(CASE WHEN gat.periodandtypebet = '232' and gtp.homeaway = 0 THEN gat.StatusId else 0 END),
sp4 = MAX(CASE WHEN gat.periodandtypebet = '242' and gtp.homeaway = 0 THEN gat.StatusId else 0 END),
spx = MAX(CASE WHEN gat.periodandtypebet = '402' and gtp.homeaway = 0 THEN gat.StatusId else 0 END),
sh1 = MAX(CASE WHEN gat.periodandtypebet = '112' and gtp.homeaway = 0 THEN gat.StatusId else 0 END),
sh2 = MAX(CASE WHEN gat.periodandtypebet = '122' and gtp.homeaway = 0 THEN gat.StatusId else 0 END),
sjc = MAX(CASE WHEN gat.periodandtypebet = '102' and gtp.homeaway = 0 THEN gat.StatusId else 0 END),

p1l = MAX(CASE WHEN gat.periodandtypebet = '212' and gtp.homeaway = 1 THEN gat.anotation else 0 END),
p2l = MAX(CASE WHEN gat.periodandtypebet = '222' and gtp.homeaway = 1 THEN gat.anotation else 0 END),
p3l = MAX(CASE WHEN gat.periodandtypebet = '232' and gtp.homeaway = 1 THEN gat.anotation else 0 END),
p4l = MAX(CASE WHEN gat.periodandtypebet = '242' and gtp.homeaway = 1 THEN gat.anotation else 0 END),
pxl = MAX(CASE WHEN gat.periodandtypebet = '402' and gtp.homeaway = 1 THEN gat.anotation else 0 END),

h1l = MAX(CASE WHEN gat.periodandtypebet = '112' and gtp.homeaway = 1 THEN gat.anotation else 0 END),
h2l = MAX(CASE WHEN gat.periodandtypebet = '122' and gtp.homeaway = 1 THEN gat.anotation else 0 END),
JCL = MAX(CASE WHEN gat.periodandtypebet = '102' and gtp.homeaway = 1 THEN gat.anotation else 0 END)

FROM gameanotationteam gat
INNER JOIN GameMatchupTotalPrice gtp ON gtp.GameId = gat.GameId and
                                        gtp.TeamId = gat.TeamId and
                                        gtp.IsStarted = 1       and 
                                        gtp.IsActive   = 1
INNER JOIN Game g ON g.[Id] = gtp.GameId AND g.LeagueId = @LeagueId
INNER JOIN Team t ON t.Code = gat.TeamId
WHERE g.LeagueId = @LeagueId AND g.GameDate = @GameDate
GROUP BY
gat.teamId,
t.[Name],
g.[Id],
g.NoOrder,
gtp.HomeAway
ORDER BY 
g.NoOrder,
g.[Id],
gtp.HomeAway
END
























GO
/****** Object:  StoredProcedure [dbo].[spXMLGameAnotationTeamLISTForEspecial]    Script Date: 04/13/2016 19:38:13 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[spXMLGameAnotationTeamLISTForEspecial]

 @LeagueId integer,
 @TypeBetId tinyint

AS

SET NOCOUNT ON


Declare @FechaActual  smalldatetime,
        @FechaNormal  smalldatetime,
        @GameDate     smalldatetime,
        @Ano          smallint,
        @Mes          tinyint,
        @Dia          tinyint,
        @Registros    smallint


SELECT @FechaActual = getdate()

SET @Ano = DATEPART(Year, @FechaActual)
SET @Mes = DATEPART(Month, @FechaActual)
SET @Dia = DATEPART(Day, @FechaActual)

SET @FechaNormal = convert(smalldatetime,
(
cast(@Ano as varchar(4)) + '-' +
cast(@Mes as varchar(2)) + '-' + 
cast(@Dia as varchar(2))
))

SELECT @Registros = COUNT(*) FROM GameMatchupTotalPrice gtp
INNER JOIN Game g on g.[id]=gtp.gameid
where DATEPART(Year, g.GameDate)  = DATEPART(Year, @FechaActual) AND
      DATEPART(Month, g.GameDate) = DATEPART(Month, @FechaActual) AND
      DATEPART(Day, g.GameDate)   = DATEPART(Day, @FechaActual) AND
      gtp.isstarted=1 AND gtp.IsActive = 1



IF @Registros > 0
   BEGIN
     SET @GameDate = @FechaNormal
   END
ELSE
   BEGIN
    SET @GameDate = @FechaNormal - 1
   END

IF @Registros = 0
BEGIN
SELECT 
gat.TeamId,
t.[Name],
g.[Id],
g.NoOrder,
gat.PeriodId,
gtp.HomeAway,
JCV  = MAX(CASE WHEN gtp.homeaway = 0 THEN gat.anotation else 0 END),
JCL  = MAX(CASE WHEN gtp.homeaway = 1 THEN gat.anotation else 0 END),
SJCV = MAX(CASE WHEN gtp.homeaway = 0 THEN gat.StatusId  else 0 END),
SJCL = MAX(CASE WHEN gtp.homeaway = 1 THEN gat.StatusId  else 0 END)

FROM Historygameanotationteam gat
INNER JOIN HistoryGameMatchupTotalPrice gtp ON gtp.GameId = gat.GameId and
                                        gtp.TeamId = gat.TeamId and
                                        gtp.IsStarted = 1       and
                                        gtp.IsActive  = 1
INNER JOIN Team t on t.Code = gat.TeamId
INNER JOIN HistoryGame g ON g.[Id] = gtp.GameId AND g.LeagueId = @LeagueId
WHERE g.GameDate = @GameDate AND g.Leagueid = @LeagueId and
      gat.TypeBetId = @TypeBetId
GROUP BY
gat.teamId,
t.[Name],
g.[Id],
g.NoOrder,
gat.PeriodId,
gtp.HomeAway
ORDER BY
g.NoOrder,
g.[Id],
gtp.HomeAway
END
ELSE
BEGIN
SELECT 

gat.TeamId,
t.[Name],
g.[Id],
g.NoOrder,
gat.PeriodId,
gtp.HomeAway,
JCV  = MAX(CASE WHEN gtp.homeaway = 0 THEN gat.anotation else 0 END),
JCL  = MAX(CASE WHEN gtp.homeaway = 1 THEN gat.anotation else 0 END),
SJCV = MAX(CASE WHEN gtp.homeaway = 0 THEN gat.StatusId  else 0 END),
SJCL = MAX(CASE WHEN gtp.homeaway = 1 THEN gat.StatusId  else 0 END)

FROM gameanotationteam gat
INNER JOIN GameMatchupTotalPrice gtp ON gtp.GameId = gat.GameId and
                                        gtp.TeamId = gat.TeamId and
                                        gtp.IsStarted = 1       and
                                        gtp.IsActive  = 1
INNER JOIN Team t on t.Code = gat.TeamId
INNER JOIN Game g ON g.[Id] = gtp.GameId AND g.LeagueId = @LeagueId
WHERE g.GameDate = @GameDate AND g.Leagueid = @LeagueId and
      gat.TypeBetId = @TypeBetId
GROUP BY
gat.teamId,
t.[Name],
g.[Id],
g.NoOrder,
gat.PeriodId,
gtp.HomeAway
ORDER BY
g.NoOrder,
g.[Id],
gtp.HomeAway
END

















GO
/****** Object:  StoredProcedure [dbo].[spXMLGameAnotationTeamLISTForHockey]    Script Date: 04/13/2016 19:38:13 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[spXMLGameAnotationTeamLISTForHockey] 

 @LeagueId tinyint

AS

SET NOCOUNT ON


Declare @FechaActual  smalldatetime,
        @FechaNormal  smalldatetime,
        @GameDate     smalldatetime,
        @Ano          smallint,
        @Mes          tinyint,
        @Dia          tinyint,
        @Registros    smallint


SELECT @FechaActual = getdate()

SET @Ano = DATEPART(Year, @FechaActual)
SET @Mes = DATEPART(Month, @FechaActual)
SET @Dia = DATEPART(Day, @FechaActual)

SET @FechaNormal = convert(smalldatetime,
(
cast(@Ano as varchar(4)) + '-' +
cast(@Mes as varchar(2)) + '-' + 
cast(@Dia as varchar(2))
))

SELECT @Registros = COUNT(*) FROM GameMatchupTotalPrice gtp
INNER JOIN Game g on g.[id]=gtp.gameid
where DATEPART(Year, g.GameDate)  = DATEPART(Year, @FechaActual) AND
      DATEPART(Month, g.GameDate) = DATEPART(Month, @FechaActual) AND
      DATEPART(Day, g.GameDate)   = DATEPART(Day, @FechaActual) AND
      gtp.isstarted=1 AND gtp.IsActive = 1



IF @Registros > 0
   BEGIN
     SET @GameDate = @FechaNormal
   END
ELSE
   BEGIN
    SET @GameDate = @FechaNormal - 1
   END

IF @Registros = 0
BEGIN
SELECT 

gat.TeamId,
t.[Name],
g.[Id],
g.NoOrder,
gtp.HomeAway,
p1V = MAX(CASE WHEN gat.periodandtypebet = '211' and gtp.homeaway = 0 THEN gat.anotation else 0 END),
p2V = MAX(CASE WHEN gat.periodandtypebet = '221' and gtp.homeaway = 0 THEN gat.anotation else 0 END),
p3V = MAX(CASE WHEN gat.periodandtypebet = '231' and gtp.homeaway = 0 THEN gat.anotation else 0 END),

pxV = MAX(CASE WHEN gat.periodandtypebet = '401' and gtp.homeaway = 0 THEN gat.anotation else 0 END),
JCV = MAX(CASE WHEN gat.periodandtypebet = '101' and gtp.homeaway = 0 THEN gat.anotation else 0 END),

sp1 = MAX(CASE WHEN gat.periodandtypebet = '211' and gtp.homeaway = 0 THEN gat.StatusId  else 0 END),
sp2 = MAX(CASE WHEN gat.periodandtypebet = '221' and gtp.homeaway = 0 THEN gat.StatusId else 0 END),
sp3 = MAX(CASE WHEN gat.periodandtypebet = '231' and gtp.homeaway = 0 THEN gat.StatusId else 0 END),
spx = MAX(CASE WHEN gat.periodandtypebet = '401' and gtp.homeaway = 0 THEN gat.StatusId else 0 END),
sjc = MAX(CASE WHEN gat.periodandtypebet = '101' and gtp.homeaway = 0 THEN gat.StatusId else 0 END),

p1l = MAX(CASE WHEN gat.periodandtypebet = '211' and gtp.homeaway = 1 THEN gat.anotation else 0 END),
p2l = MAX(CASE WHEN gat.periodandtypebet = '221' and gtp.homeaway = 1 THEN gat.anotation else 0 END),
p3l = MAX(CASE WHEN gat.periodandtypebet = '231' and gtp.homeaway = 1 THEN gat.anotation else 0 END),

pxl = MAX(CASE WHEN gat.periodandtypebet = '401' and gtp.homeaway = 1 THEN gat.anotation else 0 END),

JCL = MAX(CASE WHEN gat.periodandtypebet = '101' and gtp.homeaway = 1 THEN gat.anotation else 0 END)

FROM Historygameanotationteam gat
INNER JOIN HistoryGameMatchupTotalPrice gtp ON gtp.GameId = gat.GameId and
                                        gtp.TeamId = gat.TeamId and
                                        gtp.IsStarted = 1       and
                                        gtp.IsActive  = 1

INNER JOIN HistoryGame g ON g.[Id] = gtp.GameId AND g.LeagueId = @LeagueId
INNER JOIN Team t on t.Code = gat.TeamId
WHERE g.LeagueId = @LeagueId AND g.GameDate = @GameDate
GROUP BY
gat.teamId,
t.[Name],
g.[Id],
g.NoOrder,
gtp.HomeAway
ORDER BY
g.NoOrder,
g.[Id],
gtp.HomeAway
END
ELSE
BEGIN
SELECT 

gat.TeamId,
t.[Name],
g.[Id],
g.NoOrder,
gtp.HomeAway,
p1V = MAX(CASE WHEN gat.periodandtypebet = '211' and gtp.homeaway = 0 THEN gat.anotation else 0 END),
p2V = MAX(CASE WHEN gat.periodandtypebet = '221' and gtp.homeaway = 0 THEN gat.anotation else 0 END),
p3V = MAX(CASE WHEN gat.periodandtypebet = '231' and gtp.homeaway = 0 THEN gat.anotation else 0 END),

pxV = MAX(CASE WHEN gat.periodandtypebet = '401' and gtp.homeaway = 0 THEN gat.anotation else 0 END),
JCV = MAX(CASE WHEN gat.periodandtypebet = '101' and gtp.homeaway = 0 THEN gat.anotation else 0 END),

sp1 = MAX(CASE WHEN gat.periodandtypebet = '211' and gtp.homeaway = 0 THEN gat.StatusId  else 0 END),
sp2 = MAX(CASE WHEN gat.periodandtypebet = '221' and gtp.homeaway = 0 THEN gat.StatusId else 0 END),
sp3 = MAX(CASE WHEN gat.periodandtypebet = '231' and gtp.homeaway = 0 THEN gat.StatusId else 0 END),
spx = MAX(CASE WHEN gat.periodandtypebet = '401' and gtp.homeaway = 0 THEN gat.StatusId else 0 END),
sjc = MAX(CASE WHEN gat.periodandtypebet = '101' and gtp.homeaway = 0 THEN gat.StatusId else 0 END),

p1l = MAX(CASE WHEN gat.periodandtypebet = '211' and gtp.homeaway = 1 THEN gat.anotation else 0 END),
p2l = MAX(CASE WHEN gat.periodandtypebet = '221' and gtp.homeaway = 1 THEN gat.anotation else 0 END),
p3l = MAX(CASE WHEN gat.periodandtypebet = '231' and gtp.homeaway = 1 THEN gat.anotation else 0 END),

pxl = MAX(CASE WHEN gat.periodandtypebet = '401' and gtp.homeaway = 1 THEN gat.anotation else 0 END),

JCL = MAX(CASE WHEN gat.periodandtypebet = '101' and gtp.homeaway = 1 THEN gat.anotation else 0 END)

FROM gameanotationteam gat
INNER JOIN GameMatchupTotalPrice gtp ON gtp.GameId = gat.GameId and
                                        gtp.TeamId = gat.TeamId and
                                        gtp.IsStarted = 1       and
                                        gtp.IsActive  = 1

INNER JOIN Game g ON g.[Id] = gtp.GameId AND g.LeagueId = @LeagueId
INNER JOIN Team t on t.Code = gat.TeamId
WHERE g.LeagueId = @LeagueId AND g.GameDate = @GameDate
GROUP BY
gat.teamId,
t.[Name],
g.[Id],
g.NoOrder,
gtp.HomeAway
ORDER BY
g.NoOrder,
g.[Id],
gtp.HomeAway
END



















GO
/****** Object:  StoredProcedure [dbo].[spXMLGameAnotationTeamLISTForSoccer]    Script Date: 04/13/2016 19:38:14 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[spXMLGameAnotationTeamLISTForSoccer]

 @LeagueId tinyint

AS

SET NOCOUNT ON

Declare @FechaActual  smalldatetime,
        @FechaNormal  smalldatetime,
        @GameDate     smalldatetime,
        @Ano          smallint,
        @Mes          tinyint,
        @Dia          tinyint,
        @Registros    smallint,
        @JugadaBase   tinyint


SELECT @FechaActual = getdate()

SET @Ano = DATEPART(Year, @FechaActual)
SET @Mes = DATEPART(Month, @FechaActual)
SET @Dia = DATEPART(Day, @FechaActual)

SET @FechaNormal = convert(smalldatetime,
(
cast(@Ano as varchar(4)) + '-' +
cast(@Mes as varchar(2)) + '-' + 
cast(@Dia as varchar(2))
))

SELECT @Registros = COUNT(*) FROM GameMatchupTotalPrice gtp
INNER JOIN Game g on g.[id]=gtp.gameid
where DATEPART(Year, g.GameDate)  = DATEPART(Year, @FechaActual) AND
      DATEPART(Month, g.GameDate) = DATEPART(Month, @FechaActual) AND
      DATEPART(Day, g.GameDate)   = DATEPART(Day, @FechaActual) AND
      gtp.isstarted=1 AND gtp.IsActive = 1



IF @Registros > 0
   BEGIN
     SET @GameDate = @FechaNormal
   END
ELSE
   BEGIN
    SET @GameDate = @FechaNormal - 1
   END

IF @Registros = 0
BEGIN
  SELECT @JugadaBase = TypeBetId
  FROM League
  WHERE [Id] = @LeagueId

  IF @JugadaBase = 1
     BEGIN
       SELECT 

         gat.TeamId,
         t.[Name],
         g.[Id],
         g.NoOrder,
         gtp.HomeAway,
         h1V = MAX(CASE WHEN gat.periodandtypebet = '111' and gtp.homeaway = 0 THEN gat.anotation else 0 END),
         h2V = MAX(CASE WHEN gat.periodandtypebet = '121' and gtp.homeaway = 0 THEN gat.anotation else 0 END),
         pxV = MAX(CASE WHEN gat.periodandtypebet = '402' and gtp.homeaway = 0 THEN gat.anotation else 0 END),
         JCV = MAX(CASE WHEN gat.periodandtypebet = '101' and gtp.homeaway = 0 THEN gat.anotation else 0 END),

         sh1 = MAX(CASE WHEN gat.periodandtypebet = '111' and gtp.homeaway = 0 THEN gat.StatusId else 0 END),
         sh2 = MAX(CASE WHEN gat.periodandtypebet = '121' and gtp.homeaway = 0 THEN gat.StatusId else 0 END),
         spx = MAX(CASE WHEN gat.periodandtypebet = '402' and gtp.homeaway = 0 THEN gat.StatusId else 0 END),
         sjc = MAX(CASE WHEN gat.periodandtypebet = '101' and gtp.homeaway = 0 THEN gat.StatusId else 0 END),

         h1l = MAX(CASE WHEN gat.periodandtypebet = '111' and gtp.homeaway = 1 THEN gat.anotation else 0 END),
         h2l = MAX(CASE WHEN gat.periodandtypebet = '121' and gtp.homeaway = 1 THEN gat.anotation else 0 END),
         pxl = MAX(CASE WHEN gat.periodandtypebet = '402' and gtp.homeaway = 1 THEN gat.anotation else 0 END),
         JCL = MAX(CASE WHEN gat.periodandtypebet = '101' and gtp.homeaway = 1 THEN gat.anotation else 0 END)

      FROM Historygameanotationteam gat
      INNER JOIN HistoryGameMatchupTotalPrice gtp ON gtp.GameId = gat.GameId and
                                              gtp.TeamId = gat.TeamId and
                                              gtp.IsStarted = 1       and
                                              gtp.isActive  = 1 
      INNER JOIN HistoryGame g ON g.[Id] = gtp.GameId AND g.LeagueId = @LeagueId
      INNER JOIN Team t on t.code = gat.TeamId
      WHERE  g.GameDate = @GameDate AND g.LeagueId = @LeagueId
      GROUP BY
      gat.teamId,
      t.[Name],
      g.[Id],
      g.NoOrder,
      gtp.HomeAway
      ORDER BY g.NoOrder, g.[Id], gtp.HomeAway
     END
  ELSE
     BEGIN
       SELECT 

         gat.TeamId,
         t.[Name],
         g.[Id],
         g.NoOrder,
         gtp.HomeAway,
         h1V = MAX(CASE WHEN gat.periodandtypebet = '112' and gtp.homeaway = 0 THEN gat.anotation else 0 END),
         h2V = MAX(CASE WHEN gat.periodandtypebet = '122' and gtp.homeaway = 0 THEN gat.anotation else 0 END),
         pxV = MAX(CASE WHEN gat.periodandtypebet = '402' and gtp.homeaway = 0 THEN gat.anotation else 0 END),
         JCV = MAX(CASE WHEN gat.periodandtypebet = '102' and gtp.homeaway = 0 THEN gat.anotation else 0 END),

         sh1 = MAX(CASE WHEN gat.periodandtypebet = '112' and gtp.homeaway = 0 THEN gat.StatusId else 0 END),
         sh2 = MAX(CASE WHEN gat.periodandtypebet = '122' and gtp.homeaway = 0 THEN gat.StatusId else 0 END),
         spx = MAX(CASE WHEN gat.periodandtypebet = '402' and gtp.homeaway = 0 THEN gat.StatusId else 0 END),
         sjc = MAX(CASE WHEN gat.periodandtypebet = '102' and gtp.homeaway = 0 THEN gat.StatusId else 0 END),

         h1l = MAX(CASE WHEN gat.periodandtypebet = '112' and gtp.homeaway = 1 THEN gat.anotation else 0 END),
         h2l = MAX(CASE WHEN gat.periodandtypebet = '122' and gtp.homeaway = 1 THEN gat.anotation else 0 END),
         pxl = MAX(CASE WHEN gat.periodandtypebet = '402' and gtp.homeaway = 1 THEN gat.anotation else 0 END),
         JCL = MAX(CASE WHEN gat.periodandtypebet = '102' and gtp.homeaway = 1 THEN gat.anotation else 0 END)

      FROM Historygameanotationteam gat
      INNER JOIN HistoryGameMatchupTotalPrice gtp ON gtp.GameId = gat.GameId and
                                              gtp.TeamId = gat.TeamId and
                                              gtp.IsStarted = 1       and
                                              gtp.isActive  = 1 
      INNER JOIN HistoryGame g ON g.[Id] = gtp.GameId AND g.LeagueId = @LeagueId
      INNER JOIN Team t on t.code = gat.TeamId
      WHERE g.GameDate = @GameDate AND g.LeagueId = @LeagueId
       GROUP BY
      gat.teamId,
      t.[Name],
      g.[Id],
      g.NoOrder,
      gtp.HomeAway
      ORDER BY g.NoOrder, g.[Id], gtp.HomeAway

     END

END
ELSE
BEGIN
  SELECT @JugadaBase = TypeBetId
  FROM League
  WHERE [Id] = @LeagueId

  IF @JugadaBase = 1
     BEGIN
       SELECT 

         gat.TeamId,
         t.[Name],
         g.[Id],
         g.NoOrder,
         gtp.HomeAway,
         h1V = MAX(CASE WHEN gat.periodandtypebet = '111' and gtp.homeaway = 0 THEN gat.anotation else 0 END),
         h2V = MAX(CASE WHEN gat.periodandtypebet = '121' and gtp.homeaway = 0 THEN gat.anotation else 0 END),
         pxV = MAX(CASE WHEN gat.periodandtypebet = '402' and gtp.homeaway = 0 THEN gat.anotation else 0 END),
         JCV = MAX(CASE WHEN gat.periodandtypebet = '101' and gtp.homeaway = 0 THEN gat.anotation else 0 END),

         sh1 = MAX(CASE WHEN gat.periodandtypebet = '111' and gtp.homeaway = 0 THEN gat.StatusId else 0 END),
         sh2 = MAX(CASE WHEN gat.periodandtypebet = '121' and gtp.homeaway = 0 THEN gat.StatusId else 0 END),
         spx = MAX(CASE WHEN gat.periodandtypebet = '402' and gtp.homeaway = 0 THEN gat.StatusId else 0 END),
         sjc = MAX(CASE WHEN gat.periodandtypebet = '101' and gtp.homeaway = 0 THEN gat.StatusId else 0 END),

         h1l = MAX(CASE WHEN gat.periodandtypebet = '111' and gtp.homeaway = 1 THEN gat.anotation else 0 END),
         h2l = MAX(CASE WHEN gat.periodandtypebet = '121' and gtp.homeaway = 1 THEN gat.anotation else 0 END),
         pxl = MAX(CASE WHEN gat.periodandtypebet = '402' and gtp.homeaway = 1 THEN gat.anotation else 0 END),
         JCL = MAX(CASE WHEN gat.periodandtypebet = '101' and gtp.homeaway = 1 THEN gat.anotation else 0 END)

      FROM gameanotationteam gat
      INNER JOIN GameMatchupTotalPrice gtp ON gtp.GameId = gat.GameId and
                                              gtp.TeamId = gat.TeamId and
                                              gtp.IsStarted = 1       and
                                              gtp.isActive  = 1 
      INNER JOIN Game g ON g.[Id] = gtp.GameId AND g.LeagueId = @LeagueId
      INNER JOIN Team t on t.code = gat.TeamId
      WHERE  g.GameDate = @GameDate AND g.LeagueId = @LeagueId
      GROUP BY
      gat.teamId,
      t.[Name],
      g.[Id],
      g.NoOrder,
      gtp.HomeAway
      ORDER BY g.NoOrder, g.[Id], gtp.HomeAway
     END
  ELSE
     BEGIN
       SELECT 

         gat.TeamId,
         t.[Name],
         g.[Id],
         g.NoOrder,
         gtp.HomeAway,
         h1V = MAX(CASE WHEN gat.periodandtypebet = '112' and gtp.homeaway = 0 THEN gat.anotation else 0 END),
         h2V = MAX(CASE WHEN gat.periodandtypebet = '122' and gtp.homeaway = 0 THEN gat.anotation else 0 END),
         pxV = MAX(CASE WHEN gat.periodandtypebet = '402' and gtp.homeaway = 0 THEN gat.anotation else 0 END),
         JCV = MAX(CASE WHEN gat.periodandtypebet = '102' and gtp.homeaway = 0 THEN gat.anotation else 0 END),

         sh1 = MAX(CASE WHEN gat.periodandtypebet = '112' and gtp.homeaway = 0 THEN gat.StatusId else 0 END),
         sh2 = MAX(CASE WHEN gat.periodandtypebet = '122' and gtp.homeaway = 0 THEN gat.StatusId else 0 END),
         spx = MAX(CASE WHEN gat.periodandtypebet = '402' and gtp.homeaway = 0 THEN gat.StatusId else 0 END),
         sjc = MAX(CASE WHEN gat.periodandtypebet = '102' and gtp.homeaway = 0 THEN gat.StatusId else 0 END),

         h1l = MAX(CASE WHEN gat.periodandtypebet = '112' and gtp.homeaway = 1 THEN gat.anotation else 0 END),
         h2l = MAX(CASE WHEN gat.periodandtypebet = '122' and gtp.homeaway = 1 THEN gat.anotation else 0 END),
         pxl = MAX(CASE WHEN gat.periodandtypebet = '402' and gtp.homeaway = 1 THEN gat.anotation else 0 END),
         JCL = MAX(CASE WHEN gat.periodandtypebet = '102' and gtp.homeaway = 1 THEN gat.anotation else 0 END)

      FROM gameanotationteam gat
      INNER JOIN GameMatchupTotalPrice gtp ON gtp.GameId = gat.GameId and
                                              gtp.TeamId = gat.TeamId and
                                              gtp.IsStarted = 1       and
                                              gtp.isActive  = 1 
      INNER JOIN Game g ON g.[Id] = gtp.GameId AND g.LeagueId = @LeagueId
      INNER JOIN Team t on t.code = gat.TeamId
      WHERE g.GameDate = @GameDate AND g.LeagueId = @LeagueId
      GROUP BY
      gat.teamId,
      t.[Name],
      g.[Id],
      g.NoOrder,
      gtp.HomeAway
      ORDER BY g.NoOrder, g.[Id], gtp.HomeAway
     END

END















GO
/****** Object:  StoredProcedure [dbo].[spXMLLISTAvailableGameAnotationTeamLeague]    Script Date: 04/13/2016 19:38:14 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[spXMLLISTAvailableGameAnotationTeamLeague]

AS

SET NOCOUNT ON

Declare @FechaActual  smalldatetime,
        @FechaNormal  smalldatetime,
        @GameDate     smalldatetime,
        @Ano          smallint,
        @Mes          tinyint,
        @Dia          tinyint,
        @Registros    smallint


SELECT @FechaActual = getdate()

SET @Ano = DATEPART(Year, @FechaActual)
SET @Mes = DATEPART(Month, @FechaActual)
SET @Dia = DATEPART(Day, @FechaActual)

SET @FechaNormal = convert(smalldatetime,
(
cast(@Ano as varchar(4)) + '-' +
cast(@Mes as varchar(2)) + '-' + 
cast(@Dia as varchar(2))
))

SELECT @Registros = COUNT(*) FROM GameMatchupTotalPrice gtp
INNER JOIN Game g on g.[id]=gtp.gameid
where DATEPART(Year, g.GameDate)  = DATEPART(Year, @FechaActual) AND
      DATEPART(Month, g.GameDate) = DATEPART(Month, @FechaActual) AND
      DATEPART(Day, g.GameDate)   = DATEPART(Day, @FechaActual) AND
      gtp.isstarted=1 AND gtp.IsActive = 1



IF @Registros > 0
   BEGIN
     SET @GameDate = @FechaNormal
   END
ELSE
   BEGIN
    SET @GameDate = @FechaNormal - 1
   END

IF @Registros = 0
BEGIN
SELECT DISTINCT
g.LeagueId,
g.GameDate,
l.[Name],
l.Acronym,
s.[Id] AS Deporte,
lx.TimeToDisplayResult,
lx.NoOrder,
l.SportId,
l.TypeBetId,
CantidadJuegos = COALESCE((SELECT count(*) FROM HistoryGame g1
                           WHERE g1.LeagueId = g.LeagueId AND g1.GameDate = @GameDate), 0)
FROM HistoryGame g
INNER JOIN League l On l.[Id] = g.LeagueId
INNER JOIN Sport  s On s.[Id] = l.SportId
INNER JOIN LeagueForXML lx ON lx.LeagueId = g.LeagueId
WHERE g.GameDate = @GameDate
GROUP BY
g.LeagueId,
g.GameDate,
l.[Name],
l.Acronym,
s.[Id],
lx.TimeToDisplayResult,
lx.NoOrder,
l.SportId,
l.TypeBetId
ORDER BY lx.NoOrder, g.LeagueId

END
ELSE
BEGIN
SELECT DISTINCT
g.LeagueId,
g.GameDate,
l.[Name],
l.Acronym,
s.[Id] AS Deporte,
lx.TimeToDisplayResult,
lx.NoOrder,
l.SportId,
l.TypeBetId,
/*CantidadJuegos = COALESCE((SELECT count(*) FROM Game g1
                           WHERE g1.LeagueId = g.LeagueId AND g1.GameDate = @GameDate AND
                                 g1.TimeGame > getdate()), 0)*/

CantidadJuegos = COALESCE((SELECT count(*) FROM Game g1
                           WHERE g1.LeagueId = g.LeagueId AND g1.GameDate = @GameDate
                                 ), 0)

FROM Game g
INNER JOIN League l On l.[Id] = g.LeagueId
INNER JOIN Sport  s On s.[Id] = l.SportId
INNER JOIN LeagueForXML lx ON lx.LeagueId = g.LeagueId
/*WHERE g.GameDate = @GameDate AND g.TimeGame > getdate()*/
WHERE g.GameDate = @GameDate

GROUP BY
g.LeagueId,
g.GameDate,
l.[Name],
l.Acronym,
s.[Id],
lx.TimeToDisplayResult,
lx.NoOrder,
l.SportId,
l.TypeBetId
ORDER BY lx.NoOrder, g.LeagueId
END