I am attaching some data for example. The following is the query I am using. I don't get the right result on the update value. I have tried many different ways. Any input would be greatly appreciated:
SET T1.SPCL_EARNS = T2.SUM_QTY
FROM PS_PY_CAAT_CALC T1
INNER JOIN (
SELECT DISTINCT TMP.EMPLID,
SUM(EARNS.SPCL_EARNS) AS SUM_QTY
FROM PS_PY_CAAT_TMP TMP
INNER JOIN PS_PY_SPCL_EARNS EARNS ON TMP.PAGE_NUM = EARNS.PAGE_NUM
AND TMP.LINE_NUM = EARNS.LINE_NUM
AND TMP.OFF_CYCLE = EARNS.OFF_CYCLE
AND TMP.SEPCHK = EARNS.SEPCHK
AND TMP.EMPL_RCD = EARNS.EMPL_RCD
AND TMP.PAYGROUP = EARNS.PAYGROUP
INNER JOIN PS_PY_CAAT_CALC
AS CALC ON CALC.EMPLID = TMP.EMPLID
WHERE EARNS.PAY_END_DT > CASE WHEN CALC.NC_MAX_PENS_EFFDT >= DATEADD(yy,DATEDIFF(yy,0,'2013-03-23' ),0) THEN CALC.NC_MAX_PENS_EFFDT ELSE DATEADD(yy,DATEDIFF(yy,0,'2013-03-23' ),0) END
AND EARNS.PAY_END_DT <= '2013-03-23'
AND EARNS.ERNCD_SPCL = 'CP3'
AND EARNS.PAYGROUP = 'PTS'
GROUP BY EARNS.PAGE_NUM,TMP.EMPLID ) AS T2
ON T2.EMPLID = T1.EMPLID