• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 157
  • Last Modified:

SQL scripting help

I am an expert in a few things but SQL scripting is not one of them.  That being said, here is what I need help with.  There are three fields in a table that I need to manipulate.  These fields are EMP_ID, EARN_ID, and Amount.  There are multiple records in the table for each EMP_ID with different EARN_ID and Amount.  There is one record for each EMP_ID that has an EARN_ID equal to 'W2MEDREV'.  Each EMP_ID record will also have and EARN_ID record for their status (i.e. single, married, etc.).  I need to produce a script that will set the Amount field in the record for each EMP_ID that contains the 'W2MEDREV' field to the negative or inverse of the Amount in the single, married, etc. for the corresponding EMP_ID.

EMP_ID        EARN_ID             Amount
00001           W2MEDREV        100.00
00001           MARRIED           -100.00
00002           W2MEDREV         200.00
00002           SINGLE               -200.00

This is a sample of what I need to end up with.  Can someone help me?  

2 Solutions
Guy Hengel [angelIII / a3]Billing EngineerCommented:
this should do it:
  set Amount = - ( select sum(i.Amount)
               from yourtable i
             where i.emp_id = t.emp_id
                  and i.earn_id <> 'W2MEDREV'
from yourtable t
where t.earn_id = 'W2MEDREV' 

Open in new window

I would suggest a small change in line 5 above
 and i.earn_id in ('MARRIED','SINGLE')

Open in new window

as we don't know what other values may be stored in the field earn_id
rlewiscAuthor Commented:
I utilized both comments.  It worked perfectly.  Thanks for the help.....
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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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