DECLARE @TheoreticalHours varchar(8000), @LaborTickets varchar(8000), @Join varchar(8000)

SELECT @TheoreticalHours =
	' WITH [EmpHorasTeoricas]([CodEmpleado], [Horas]) AS' +
	' (' +
	' SELECT CodEmpleado, Horas FROM OPENQUERY' +
	' (' +
		' [ORACLELINKEDSERVER]' + 
		' ,''SELECT EMPHORAS.COD CodEmpleado, SUM(EMPHORAS.HORAS) Horas' +
		' FROM ERP.EMPLEADOHORAS EMPHORAS' +
		' WHERE EMPHORAS.DIA BETWEEN TO_DATE(''''' + CONVERT(CHAR(10), @DateFrom, 126) + ''''', ''''yyyy/mm/dd'''')'
		' AND TO_DATE(''''' + CONVERT(CHAR(10), @DateTo, 126) + ''''', ''''yyyy/mm/dd'''')' +
		' GROUP BY EMPHORAS.COD''' +
	' )' +
	' )';
SELECT @LaborTickets =
	' ,[EmpHorasReportadas]([EmpId], [EmpNombre], [HorasReportadas]) AS' +
	' (' +
	' SELECT [Descargos].[EmpId]' +
	' ,[Empleados].[Nombre] [EmpNombre]' +
	' , SUM([Descargos].[HorasReportadas]) [HorasReportadas]' +
	' FROM [dbo].[Descargos]' +
	' INNER JOIN [dbo].[Empleados] ON [Descargos].[EmpId] = [Empleados].[Id]' +
	' WHERE [Descargos].[Fecha] >= CAST(''' + CONVERT(CHAR(10), @DateFrom, 112) + ''' AS DATE)' +
	' AND [Descargos].[Fecha] < DATEADD(DAY, 1, CAST(''' + CONVERT(CHAR(10), @DateTo, 112) + ''' AS DATE))' +
	' GROUP BY [Descargos].[EmpId]' +
	' )';
SELECT @Join =
	' SELECT' +
	' [EmpHorasReportadas].[EmpId]' +
	' ,[EmpHorasReportadas].[EmpNombre]' +
	' ,[EmpHorasTeoricas].[Horas] [HorasTeoricas]' +
	' ,[EmpHorasReportadas].[HorasReportadas]' +
	' FROM [EmpHorasTeoricas]' +
	' LEFT OUTER JOIN [EmpHorasReportadas] ON [EmpHorasTeoricas].[CodEmpleado] = [EmpHorasReportadas].[EmpId]' +
	' ORDER BY [EmpHorasReportadas].[EmpNombre]';
EXEC(@TheoreticalHours + @LaborTickets + @Join)