I want to join these two SQL select statements and get both results as a single result.
--1st select statement
SELECT TA.DevID, TA.Task, TA.AssignDate, TA.DevStart,TA.TaskType, TA.EstimateTime, TA.Status, TA.DevFinish, TAKPI.actAsignTime, TAKPI.actTime, TAKPI.KPI
FROM TT_TaskAssign TA
INNER JOIN HR_EmployeeMaster1 EM
on TA.DevID=EM.EmpNo
INNER JOIN dbo.TT_TaskAsignKPI TAKPI
ON TAKPI.AssignID = TA.AssignID
WHERE TA.DevID='1435'
--2nd select statement
SELECT TT_TaskAssign.DevID AS DevID ,HR_EmployeeMaster1.EmpFirstName+' '+HR_EmployeeMaster1.EmpMiddleName+' '+HR_EmployeeMaster1.EmpLastName as Developer, SUM( CASE WHEN TT_TaskAssign.Status='Done' THEN 1 ELSE 0 END ) as DoneProjects , SUM( CASE WHEN TT_TaskAssign.Status='Developing' THEN 1 ELSE 0 END ) as DevelopingPrjects, SUM( CASE WHEN TT_TaskAssign.Status='Assign' THEN 1 ELSE 0 END ) as AssignedPending, SUM(CONVERT(int, TAKPI.KPINum)) AS KPINum
FROM TT_TaskAssign inner join Project_Master on TT_TaskAssign.ProID=Project_Master.Project_Code Inner Join HR_EmployeeMaster1 on TT_TaskAssign.DevID=HR_EmployeeMaster1.EmpNo INNER JOIN dbo.TT_TaskAsignKPI TAKPI ON TAKPI.AssignID = TAKPI.AssignID
WHERE TT_TaskAssign.DevID='1435'
GROUP BY TT_TaskAssign.DevID,HR_EmployeeMaster1.EmpFirstName+' '+HR_EmployeeMaster1.EmpMiddleName+' '+HR_EmployeeMaster1.EmpLastName
ORDER BY SUM(CONVERT(int, TAKPI.KPINum)) DESC
I did it in follwing way
SELECT T1.*, T2.*
FROM
(SELECT TA.DevID, TA.Task, TA.AssignDate, TA.DevStart,TA.TaskType, TA.EstimateTime, TA.Status, TA.DevFinish, TAKPI.actAsignTime, TAKPI.actTime, TAKPI.KPI
FROM TT_TaskAssign TA
INNER JOIN HR_EmployeeMaster1 EM
on TA.DevID=EM.EmpNo
INNER JOIN dbo.TT_TaskAsignKPI TAKPI
ON TAKPI.AssignID = TA.AssignID
WHERE TA.DevID='1435') T1
INNER JOIN
(SELECT TT_TaskAssign.DevID AS DevID ,HR_EmployeeMaster1.EmpFirstName+' '+HR_EmployeeMaster1.EmpMiddleName+' '+HR_EmployeeMaster1.EmpLastName as Developer, SUM( CASE WHEN TT_TaskAssign.Status='Done' THEN 1 ELSE 0 END ) as DoneProjects , SUM( CASE WHEN TT_TaskAssign.Status='Developing' THEN 1 ELSE 0 END ) as DevelopingPrjects, SUM( CASE WHEN TT_TaskAssign.Status='Assign' THEN 1 ELSE 0 END ) as AssignedPending, SUM(CONVERT(int, TAKPI.KPINum)) AS KPINum
FROM TT_TaskAssign inner join Project_Master on TT_TaskAssign.ProID=Project_Master.Project_Code Inner Join HR_EmployeeMaster1 on TT_TaskAssign.DevID=HR_EmployeeMaster1.EmpNo INNER JOIN dbo.TT_TaskAsignKPI TAKPI ON TAKPI.AssignID = TAKPI.AssignID
WHERE TT_TaskAssign.DevID='1435'
GROUP BY TT_TaskAssign.DevID,HR_EmployeeMaster1.EmpFirstName+' '+HR_EmployeeMaster1.EmpMiddleName+' '+HR_EmployeeMaster1.EmpLastName
ORDER BY SUM(CONVERT(int, TAKPI.KPINum)) DESC) T2
ON T1.DevID=T2.DevID
Now It's giving following error
Msg 1033, Level 15, State 1, Line 17
The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is also specified.
Please somebody help me to fix this error
Aucun commentaire:
Enregistrer un commentaire