unir dos consultas sql server para ejecutar en php

[madison_sg]

Hola a todos,

Tengo que unir dos consultas anidadas para crear una tabla en php, la cosa es que cuando le hago el left join me sale error de multi part idetifier could not be bound, alguien me puede ayudar a unirlas? ambas consultas tienen en comun un campo denominado [nivel]
Consulta 1:

Código:

DECLARE @No_Periods FLOAT
	SET @No_Periods= (select max([mes]) from [ty_actuals_m4])
DECLARE @No_Period FLOAT
	SET @No_Period=12

	SELECT tbl_Act_Join.ID_nivel_budget,tbl_Act_Join.nivel,tbl_Act_Join.HC_Actuals,tbl_HC_B.Nivel_B, tbl_HC_B.HC_B
	,ISNULL(Tbl_Amoun_Join.Amount_Actuals,0) AS Amount_Actuals
	,(ISNULL(Tbl_Amoun_Join.Amount_Actuals,0)/IIF(tbl_Act_Join.HC_Actuals=0,1,tbl_Act_Join.HC_Actuals))/@No_Periods AS Average_Actuals
	FROM 
		(SELECT niv.[ID_nivel_budget],Niv.[nivel]/*,Actuals_Final.NIvel*/,ISNULL(Actuals_Final.HC_Actuals,0) AS HC_Actuals,ISNULL(Actuals_Final.Amount_Actuals,0) AS Amount_Actuals,ISNULL(Actuals_Final.Average_Actuals,0) AS Average_Actuals
		FROM [ty_nivel_budget] Niv
		LEFT JOIN 
			(SELECT Tbl_Actuales.[Level]
				,t3.[nivel] AS NIvel
				,COALESCE(COUNT(Tbl_Actuales.[Employee_Number])/@No_Periods,0) AS HC_Actuals
				,0 /*SUM(Tbl_Actuales.[Amount])*/ As Amount_Actuals
				,0 /*COALESCE((SUM(Tbl_Actuales.[Amount])/COALESCE(COUNT(Tbl_Actuales.[Employee_Number])/@No_Periods,0))/@No_Periods,0)*/ AS Average_Actuals
			FROM (SELECT 'Actuals' AS 'Version'
						,am4.[anio] AS 'Year'
						,am4.[mes] As Mes
						,am4.[ID_GL_Account_cat] AS ID_GL
						,emp.[ID_Cost_center] AS CC --Get from Employee_Master
						,am4.[numero_empleado] AS Employee_Number
						,am4.[nombre_empleado] AS Employee_Name
						,emp.[ID_nivel_budget_kla] AS 'Level' --Get from Employee_Master
						,'NA' AS ID_Position
						,'NA' AS Position
						,CASE 
							WHEN am4.[ID_grouper_total] = 1 OR am4.[ID_GL_Account_cat]=4 THEN am4.[importe]
						ELSE am4.[importe]*-1
						END Amount

						,gl.[ID_Subgroup]
	  
						,am4.[ID_Company_code]
						,am4.[period_registered]
						,am4.[year_] AS 'Year_'
					FROM [ty_actuals_m4] am4
					LEFT JOIN ty_employee_master emp on am4.[numero_empleado] = emp.[numero_empleado] AND am4.mes = emp.[periodo] AND am4.[anio] = emp.[year_]
					LEFT JOIN [ty_gl_account] gl on am4.[ID_GL_Account_cat] = gl.[ID_GL_Account_cat]
					WHERE am4.[ID_GL_Account_cat] IN (1,4) AND gl.[ID_Subgroup]=20		  
					) Tbl_Actuales
				LEFT JOIN [ty_nivel_budget] t3 ON Tbl_Actuales.[Level]=t3.[ID_nivel_budget]
				GROUP BY Tbl_Actuales.[Level],t3.[nivel]
				) Actuals_Final ON Niv.ID_nivel_budget=Actuals_Final.[Level]) tbl_Act_Join
	LEFT JOIN (SELECT Tbl_Actuales.[Level]
			,t3.[nivel] AS NIvel
			,SUM(Tbl_Actuales.[Amount]) As Amount_Actuals
		FROM (SELECT 'Actuals' AS 'Version'
					,am4.[anio] AS 'Year'
					,am4.[mes] As Mes
					,am4.[ID_GL_Account_cat] AS ID_GL
					,emp.[ID_Cost_center] AS CC --Get from Employee_Master
					,am4.[numero_empleado] AS Employee_Number
					,am4.[nombre_empleado] AS Employee_Name
					,emp.[ID_nivel_budget_kla] AS 'Level' --Get from Employee_Master
					,'NA' AS ID_Position
					,'NA' AS Position
					,CASE 
						WHEN am4.[ID_grouper_total] = 1 OR am4.[ID_GL_Account_cat]=4 THEN am4.[importe]
					ELSE am4.[importe]*-1
					END Amount

					,gl.[ID_Subgroup]
	  
					,am4.[ID_Company_code]
					,am4.[period_registered]
					,am4.[year_] AS 'Year_'
				FROM [ty_actuals_m4] am4
				LEFT JOIN ty_employee_master emp on am4.[numero_empleado] = emp.[numero_empleado] AND am4.mes = emp.[periodo] AND am4.[anio] = emp.[year_]
				LEFT JOIN [ty_gl_account] gl on am4.[ID_GL_Account_cat] = gl.[ID_GL_Account_cat]
				WHERE  gl.[ID_Subgroup]=20) Tbl_Actuales
			LEFT JOIN [ty_nivel_budget] t3 ON Tbl_Actuales.[Level]=t3.[ID_nivel_budget]
			GROUP BY Tbl_Actuales.[Level],t3.[nivel]) Tbl_Amoun_Join ON tbl_Act_Join.ID_nivel_budget=Tbl_Amoun_Join.[Level]
	ORDER BY tbl_Act_Join.[ID_nivel_budget]

Consulta 2:

Código:

SELECT tbl_Niv.[nivel],isnull(tbl_Budget.HC_B,0) AS HC_B,isnull(tbl_Budget.Amount_B,0) AS Amount_B,isnull(tbl_Budget.Average_B,0) AS Average_B
	FROM [ty_nivel_budget] tbl_Niv
	LEFT JOIN (SELECT t1.[ID_nivel_budget]
					,t3.[nivel] AS NIvel
					,COALESCE(COUNT(t1.[numero_empleado])/@No_Period,0) AS HC_B
					,SUM(t1.[cantidad]) As Amount_B
					,COALESCE((SUM(t1.[cantidad])/COALESCE(COUNT(t1.[numero_empleado])/@No_Period,0))/@No_Period,0) AS Average_B
				FROM [ty_budget] t1
				LEFT JOIN [ty_Settings] t2 ON t1.[ID_Concat_GL]=t2.[ID_Concat_Fk]
				LEFT JOIN [ty_nivel_budget] t3 ON t1.[ID_nivel_budget]=t3.[ID_nivel_budget]
				WHERE LOWER(t2.[Budget_Concept]) IN ('salario base','salary expatriados')
				GROUP BY t1.[ID_nivel_budget],t3.[nivel]
				) tbl_Budget ON tbl_Niv.ID_nivel_budget=tbl_Budget.ID_nivel_budget
	ORDER BY tbl_Niv.ID_nivel_budget

[danilojr007]

Compañero y si haces una vista en base de datos y despues la consultas ?

[bandolera]

Yo crearía una tabla con la estructura deseada desde alguna de tus tablas de trabajo.
Y luego en los campos necesarios insertaría los valores según los requiera, a veces no puedes hacer complejas consultas.