Hi All,
i am getting one problem. Actually when i run locally i am getting proper data from database. if i put the same code in Server i am not getting proper SKILL Data . please help me. i am writing below code. only for skill section remaining all are getting proper.
CREATE TEMPORARY TABLE IF NOT EXISTS Results1
SELECT DISTINCT e.EmployeeCode,
CONCAT(e.FirstName,' ',e.LastName)AS EmployeeName,
d.Designation,
e.ExperienceatJoining,
(SELECT CONCAT(ee.FirstName,' ', ee.LastName) FROM employee ee WHERE ee.EmployeeId = e.ProjectManagerId) AS ProjectManager,
(SELECT p.StartDate FROM projectemployee p WHERE p.StartDate > CURDATE() AND p.EmployeeId = e.EmployeeId ) AS NextallocateFrom ,
(SELECT MAX(ee.EmpEndDate) FROM employee ee WHERE ee.EmployeeId = e.EmployeeId GROUP BY ee.EmployeeId HAVING MAX(ee.EmpEndDate) < CURDATE() ) AS BenchFrom ,
e.DateofJoin,
l.Location,
pp.ProjectName AS NextallocateProject,
(SELECT CONCAT(ee.FirstName,' ', ee.LastName) FROM employee ee WHERE ee.EmployeeId = pp.ProjectManagerId) AS NextProjectManager
FROM employee e
LEFT OUTER JOIN designationlookup d ON e.Designation=d.DesignationId
LEFT OUTER JOIN locationlookup l ON e.Location =l.LocationId
LEFT OUTER JOIN projectemployee p ON e.EmployeeId =p.EmployeeId
LEFT OUTER JOIN project pp ON p.ProjectId =pp.ProjectId
WHERE e.EmployeeId NOT IN
(
SELECT p.EmployeeId
FROM projectemployee p
)
GROUP BY e.EmployeeId HAVING e.EmployeeId > 1 ORDER BY e.EmployeeId ASC;
SELECT DISTINCT R.EmployeeCode, R.*,
(
SELECT GROUP_CONCAT(s.Skill,'(',es.Experience,' Months',')' SEPARATOR '; ') AS testSkill
FROM employeeskills es, skilllookup s
WHERE es.Skill = s.SkillId AND R.EmployeeCode = es.EmployeeId
ORDER BY es.Experience
) Skills
FROM Results1 R;