I am running this query:
SELECT
thirdpid
, ea.Empid
, ea.Empname
, em.RsCode
, em.CustomerName AS 'RS Name'
, SUM(CASE WHEN ea.Present=1 THEN 1 ELSE 0 END) AS 'Present days'
, SUM(CASE WHEN ea.Present=0 THEN 1 ELSE 0 END) AS 'Absent days'
, SUM(CASE WHEN ea.Reason IN ('Leave') THEN 1 ELSE 0 END) AS 'Approved Leave'
, SUM(CASE WHEN ea.Reason IN ('Leave Without Pay') THEN 1 ELSE 0 END) AS 'UnApproved Leave'
, SUM(CASE WHEN ea.Reason IN
('National Holiday','State Holiday','Weekly Holiday','Special Holiday')
THEN 1
ELSE 0
END) AS 'Official Holidays'
FROM
EmployeeAttendance_Backup ea
, Customer em
, Employee emp
WHERE
ea.markedFor >= '2012-08-26'
AND ea.markedFor <= '2012-09-25'
AND em.RSCode = ea.DistID
AND emp.EmployeeMasterID = ea.Empid
GROUP BY
ea.Empid
, ea.Empname
, ea.DistID
, em.RsCode
, em.CustomerName
, emp.FirstName
, emp.MiddleName
, emp.LastName
, ea.thirdPid
ORDER BY
em.CustomerName;
I get an error:
Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the varchar value
'EmployeeName A B' to data type int.
I've tried running the query with an explicit JOIN
such as:
SELECT
thirdpid
, ea.Empid
, ea.Empname
, em.RsCode
, em.CustomerName AS 'RS Name'
, SUM(CASE WHEN ea.Present=1 THEN 1 ELSE 0 END) AS 'Present days'
, SUM(CASE WHEN ea.Present=0 THEN 1 ELSE 0 END) AS 'Absent days'
, SUM(CASE WHEN ea.Reason IN ('Leave') THEN 1 ELSE 0 END) AS 'Approved Leave'
, SUM(CASE WHEN ea.Reason IN ('Leave Without Pay') THEN 1 ELSE 0 END) AS 'UnApproved Leave'
, SUM(CASE WHEN ea.Reason IN
('National Holiday','State Holiday','Weekly Holiday','Special Holiday')
THEN 1
ELSE 0
END) AS 'Official Holidays'
FROM
EmployeeAttendance_Backup ea
LEFT OUTER JOIN Customer em ON em.RSCode = CAST(ea.DistID AS INT)
LEFT OUTER JOIN Employee emp ON emp.EmployeeMasterID=ea.Empid
WHERE
ea.markedFor >= '2012-08-26'
AND ea.markedFor <= '2012-09-25'
AND em.RSCode = ea.DistID
AND emp.EmployeeMasterID = ea.Empid
GROUP BY
ea.Empid
, ea.Empname
, ea.DistID
, em.RsCode
, em.CustomerName
, emp.FirstName
, emp.MiddleName
, emp.LastName
, ea.thirdPid
ORDER BY
em.CustomerName;
However, I get the same error as above.
If I remove all the SUM
statements from above, I get zero records. However, if I enable even one, I get the same error.
The strange part about this error is, there is no such Employee with the name EmployeeName in any of the tables used in this query.
I am stumped as to what is happening. Can anyone shed some light?
EmployeeName A B
stored in? Why is there a comma afterea
in the second query? Are you really showing us what you're running? And as an aside, the second form (minus the syntax error) is the form you should be using. – Aaron Bertrand♦ Sep 25 '12 at 16:55EmpName A B
is stored inEmployeeAttendance_Backup
but, there is no such record. I checked with a separate query. There was a comma, because I copied the first query and forgot to correctly edit. Yes, this is the complete, unhindered query. Only EmpName is changed. – theTuxRacer Sep 25 '12 at 17:05