Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 242
  • Last Modified:

Data Issue with Aggregate in Sql Update

Hi ,

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:

 BEGIN TRANSACTION
  UPDATE T1
  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

Thank you,
Nigluc
0
Lucia
Asked:
Lucia
  • 2
1 Solution
 
Louis01Commented:
Try this?
UPDATE T1 
  SET T1.SPCL_EARNS = (SELECT 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' 
			   AND TMP.EMPLID = T1.EMPLID
			  GROUP BY EARNS.PAGE_NUM,TMP.EMPLID)
  FROM PS_PY_CAAT_CALC T1

Open in new window

0
 
Louis01Commented:
Thanks. By the way, I think it was the DISTINCT in your sub-select that caused the problems...
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now