CREATE PROCEDURE dbo.SPTableroControl
@Dia1 money,
@Dia2 money,
@Dia3 money,
@Dia4 money,
@Dia5 money,
@Dia6 money,
@Dia7 money,
@Dia8 money,
@Dia9 money,
@Dia10 money,
@Dia11 money,
@Dia12 money,
@Dia13 money,
@Dia14 money,
@Dia15 money,
@Dia16 money,
@Dia17 money,
@Dia18 money,
@Dia19 money,
@Dia20 money,
@Dia21 money,
@Dia22 money,
@Dia23 money,
@Dia24 money,
@Dia25 money,
@Dia26 money,
@Dia27 money,
@Dia28 money,
@Dia29 money,
@Dia30 money,
@Dia31 money,
@DesdeFecha char(10),
@HastaFecha char(10),
@chk11 int,
@chk12 int,
@chk21 int,
@chk22 int,
@chk31 int,
@chk32 int,
@chk41 int,
@chk42 int,
@chk51 int,
@chk52 int,
@chk61 int,
@chk62 int,
@chk71 int,
@chk72 int,
@chk81 int,
@chk82 int,
@chk91 int,
@chk92 int,
@chk101 int,
@chk102 int
AS
declare @BME money
select @BME = 0
select @BME = @Dia1 + @Dia2 + @Dia3 + @Dia4 + @Dia5 + @Dia6 + @Dia7 + @Dia8 + @Dia9 + @Dia10 + @Dia11 + @Dia12 + @Dia13
select @BME = @BME + @Dia14 + @Dia15 + @Dia16 + @Dia17 + @Dia18 + @Dia19 + @Dia20 + @Dia21 + @Dia22 + @Dia23 + @Dia24
select @BME = @BME + @Dia25 + @Dia26 + @Dia27 + @Dia28 + @Dia29 + @Dia30 + @Dia31
--select @DesdeFecha = (select convert(char(4),year(@DesdeFecha)) + '/' + replicate('0', 2-len(rtrim(convert(char(2),month(@DesdeFecha))))) + rtrim(convert(char(2),month(@DesdeFecha))) + '/' + replicate('0', 2-len(rtrim(convert(char(2),day(@DesdeFecha))))) + rtrim(convert(char(2),day(@DesdeFecha))))
--select @HastaFecha = (select convert(char(4),year(@HastaFecha)) + '/' + replicate('0', 2-len(rtrim(convert(char(2),month(@HastaFecha))))) + rtrim(convert(char(2),month(@HastaFecha))) + '/' + replicate('0', 2-len(rtrim(convert(char(2),day(@HastaFecha))))) + rtrim(convert(char(2),day(@HastaFecha))))
SELECT round(convert(money, (sum(tsb) - sum(cossbeb))/sum(cub)),2) as 'Beneficio Marginal Sin Bebida',
round(convert(money, (sum(tcb) - sum(costot))/sum(cub)),2) as 'Beneficio Marginal Con Bebida',
case sum(cub) when 0 then 0 else convert(numeric, ((sum(tsb) - sum(cosSbeb))/(sum(cub))/(AVg(bme))*100)) end as 'Beneficio Marginal Sin Bebida %',
case sum(cub) when 0 then 0 else convert(numeric, ((sum(tcb) - sum(costot))/(sum(cub))/(AVg(bme))*100)) end as 'Beneficio Marginal Con Bebida %',
case sum(cub) when 0 then 0 else round(convert(money, (sum(tsb) / sum(cub))),2) end as 'Precio del Menu Sin Bebida',
case sum(cub) when 0 then 0 else round(convert(money, (sum(tcb) / sum(cub))),2) end as 'Precio del Menu Con Bebida',
case sum(cub) when 0 then 0 else round(convert(money, (sum(cosSbeb) / sum(cub))),2) end as 'Costo Cubierto Sin Bebida',
case sum(cub) when 0 then 0 else round(convert(money, (sum(costot) / sum(cub))),2) end as 'Costo Cubierto Con Bebida',
case sum(cub) when 0 then 0 else round(convert(money, ((sum(tsb) - sum(cossbeb)) / sum(cub))),2) end as 'Contribución Marginal Por Cubierto Sin Bebida',
case sum(cub) when 0 then 0 else round(convert(money, ((sum(tcb) - sum(costot)) / sum(cub))),2) end as 'Contribución Marginal Por Cubierto Con Bebida',
case sum(cub) when 0 then 0 else round(convert(money, (sum(valdecob) / sum(cub))),2) end as 'Decomiso Balanza Por Cubiertos',
case sum(cub) when 0 then 0 else round(convert(money, (sum(valdecop) / sum(cub))),2) end as 'Decomiso Producción',
case sum(cub) when 0 then 0 else round(convert(money, (sum(valsob) / sum(cub))),2) end as 'Sobras Por Cubiertos',
round(convert(money, sum(cub)),2) as 'Cubiertos'
FROM costos_maxirest, bme
Where (datediff(day, @DesdeFecha, Fecha) >= 0) and (datediff(day, @HastaFecha, Fecha) <= 0) and (
((Local = 1 and Turno = @chk11) or (Local = 1 and Turno = @chk12)) or
((Local = 2 and Turno = @chk21) or (Local = 2 and Turno = @chk22)) or
((Local = 3 and Turno = @chk31) or (Local = 3 and Turno = @chk32)) or
((Local = 4 and Turno = @chk41) or (Local = 4 and Turno = @chk42)) or
((Local = 5 and Turno = @chk51) or (Local = 5 and Turno = @chk52)) or
((Local = 6 and Turno = @chk61) or (Local = 6 and Turno = @chk62)) or
((Local = 7 and Turno = @chk71) or (Local = 7 and Turno = @chk72)) or
((Local = 8 and Turno = @chk81) or (Local = 8 and Turno = @chk82)) or
((Local = 9 and Turno = @chk91) or (Local = 9 and Turno = @chk92)) or
((Local = 10 and Turno = @chk101) or (Local = 10 and Turno = @chk102)))
GO
