DECLARE @return_value table(BonusSalary int,testid int) insert @return_value SELECT ( SUM(case when BonusSalary.Type=1 and BonusSalary.Active=1 then BonusSalary.BonusSalary else 0 end)- SUM(case when BonusSalary.Type=0 and BonusSalary.Active=1 then BonusSalary.BonusSalary else 0 end)) AS Expr1, EmpID FROM BonusSalary GROUP BY EmpID SELECT Emp.ID, Emp.EmpName, Emp.NID, EmpEmail.Email, EmpAddress.Address, EmpPhone.Phone, EmpPhone.PhoneType, Salary.Salary, Salary.SalaryType, Positions.PositionName, MainCompany.Name, WorkTimes.StartTime, WorkTimes.EndTime, Contracts.Contract, Contracts.Type, Emp_Contracts.ContractStartDate,@return_value.BonusSalary --, ContractStartEnd.Length, ContractStartEnd.Enddate, ContractStartEnd.Startdate FROM Emp left outer join EmpPhone on Emp.ID=EmpPhone.EmpID left outer join EmpEmail on Emp.ID=EmpEmail.EmpID left outer join EmpAddress on Emp.ID=EmpAddress.EmpID left outer join Emp_Salary ON Emp.ID = Emp_Salary.EmpID left outer join Salary ON Emp_Salary.SalaryID = Salary.ID left outer join Emp_Positions_Company ON Emp.ID = Emp_Positions_Company.EMPID left outer join MainCompany ON MainCompany.ID = Emp_Positions_Company.CompanyID left outer join Positions on Positions.ID=Emp_Positions_Company.PositionID left outer join WorkTimes on Emp.ID=WorkTimes.EmpID left outer join Emp_Contracts on Emp.ID=Emp_Contracts.EmpID left outer join Contracts on Contracts.ID=Emp_Contracts.ContractID LEFT OUTER JOIN @return_value on Emp.ID = @return_value.testid --WHERE (Salary.Active = 1) AND (EmpAddress.Active = 1) AND (EmpEmail.Active = 1) AND (Contracts.Active = 1) AND (Emp_Contracts.Active = 1) AND (EmpPhone.Active = 1) AND (WorkTimes.Active = 1) AND -- (Emp_Positions_Company.Active = 1) AND (MainCompany.Active = 1) AND (Positions.Active = 1) AND (Emp.Active = 1) AND (ContractStartEnd.Active = 1) AND (Emp_Salary.Active = 1) AND -- (BonusSalary.Active = 1) OR -- (Salary.Active IS NULL) AND (EmpAddress.Active IS NULL) AND (EmpEmail.Active IS NULL) AND (Contracts.Active IS NULL) AND (Emp_Contracts.Active IS NULL) AND (EmpPhone.Active IS NULL) AND -- (WorkTimes.Active IS NULL) AND (Emp_Positions_Company.Active IS NULL) AND (MainCompany.Active IS NULL) AND (Positions.Active IS NULL) AND (Emp.Active IS NULL) AND (ContractStartEnd.Active IS NULL) -- AND (Emp_Salary.Active IS NULL) AND (BonusSalary.Active IS NULL)
CREATE PROCEDURE
SELECT ... ,rv.BonusSalary,... .... LEFT OUTER JOIN @return_value as rv on Emp.ID = rv.testid
var
This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)