I run a job at the end of the year to include a group of employee. This year they do not want a certain group of wages included in the extract.
Here is the code now:
SELECT
'01LH ' AS CompDivn,
CONVERT(CHAR(9),REPLICATE('0',5 - LEN
(CONVERT(INT,Emp.EMPLOYEE))) +
CONVERT(VARCHAR(70), Emp.EMPLOYEE*1)) AS EmployeeID,
CONVERT(CHAR(9),REPLICATE('0',9 - LEN
(CONVERT(INT,SUM (PMast.GROSS_PAY)*100)))
+ CONVERT (VARCHAR(70),(SUM(PMast.GROSS_PAY))*100))
AS GrossPay
FROM PAYMASTR AS PMast
JOIN EMPLOYEE AS Emp ON
(PMast.EMPLOYEE = Emp.EMPLOYEE)
WHERE PMast.CHECK_DATE BETWEEN
CONVERT(DATETIME,@StartDate + ' 12:00:00 AM') AND
CONVERT(DATETIME,@EndDate + ' 11:59:59 PM') AND
Emp.JOB_CODE IN ('51', '52')
GROUP BY Emp.EMPLOYEE
ORDER BY EmployeeID
Now I do not want the wages that are in a different table under a different group code to be included in the extract. I cannot get the join of another table to work right. I keep getting the error:
"Arithmetic overflow error converting numeric to data type int"
Can you help.
Thank you
Here is the code now:
SELECT
'01LH ' AS CompDivn,
CONVERT(CHAR(9),REPLICATE('0',5 - LEN
(CONVERT(INT,Emp.EMPLOYEE))) +
CONVERT(VARCHAR(70), Emp.EMPLOYEE*1)) AS EmployeeID,
CONVERT(CHAR(9),REPLICATE('0',9 - LEN
(CONVERT(INT,SUM (PMast.GROSS_PAY)*100)))
+ CONVERT (VARCHAR(70),(SUM(PMast.GROSS_PAY))*100))
AS GrossPay
FROM PAYMASTR AS PMast
JOIN EMPLOYEE AS Emp ON
(PMast.EMPLOYEE = Emp.EMPLOYEE)
WHERE PMast.CHECK_DATE BETWEEN
CONVERT(DATETIME,@StartDate + ' 12:00:00 AM') AND
CONVERT(DATETIME,@EndDate + ' 11:59:59 PM') AND
Emp.JOB_CODE IN ('51', '52')
GROUP BY Emp.EMPLOYEE
ORDER BY EmployeeID
Now I do not want the wages that are in a different table under a different group code to be included in the extract. I cannot get the join of another table to work right. I keep getting the error:
"Arithmetic overflow error converting numeric to data type int"
Can you help.
Thank you