Help with a simple SQL query

Question

I've got a fairly large SPROC I'm working on. Well, large as far as I am concerned since I haven't written SQL in quite some time. Anyway, I want to make this Update statement work:

update @salesCommission
set LineAmountWithCashDiscount = (LineAmount - ((LineAmount/ (
   select SUM(LineAmount)
   FROM  @salesCommission
   WHERE InvoiceNumber='00072766')) * CashDiscountAmount))

My problem is that I need to replace the:

WHERE InvoiceNumber='00072766'

clause so that the invoice number is not hard coded. Instead it should just be the invoice number of the current row being updated.

Can anyone show me how to re-write this update query so that it selects the sum of the LineAmount for the current row being updated?

Is this SQL Server? I assume @salescommission is a table variable? — RichardTheKiwi
– RichardTheKiwi, Commented Jan 20, 2011 at 21:46

RichardTheKiwi · Accepted Answer · 2011-01-20 22:16:44Z

3

update S
SET LineAmountWithCashDiscount = S.LineAmount - (S.LineAmount / I.Total) * CashDiscountAmount
FROM @salesCommission S
inner join
(
select InvoiceNumber,
    CASE WHEN SUM(LineAmount) = 0 THEN 1 ELSE SUM(LineAmount) END as Total 
from @salesCommission
group by InvoiceNumber
) I on I.InvoiceNumber = S.InvoiceNumber

EDIT: The case to turn "total" into 1 when 0 is to get around #Div/0 errors

edited Jan 20, 2011 at 22:16

answered Jan 20, 2011 at 21:45

RichardTheKiwi

108k28 gold badges206 silver badges269 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Brent Lamborn Over a year ago

I think this is about it. The SPROC alters ok but I get a divide by zero in this part of it. I think sometimes the total can be zero.

Brent Lamborn Over a year ago

update S SET LineAmountWithCashDiscount = S.LineAmount - (S.LineAmount / I.Total) * CashDiscountAmount FROM @salesCommission S inner join ( select InvoiceNumber, CASE WHEN SUM(LineAmount) = 0 THEN 1 ELSE SUM(LineAmount) END as Total from @salesCommission group by InvoiceNumber ) I on I.InvoiceNumber = S.InvoiceNumber

Brent Lamborn Over a year ago

I modded yours cyberkiwi to the above comment and it seems to work.

RichardTheKiwi Over a year ago

@Brent - ah I see. the division by zero problem. answer edited

John K. · Accepted Answer · 2011-01-20 21:49:00Z

0

If you're doing this in a stored procedure that's being called by a trigger...

update @salesCommission 
set LineAmountWithCashDiscount = 
(LineAmount - ((LineAmount/ (select SUM(LineAmount) 
FROM  @salesCommission WHERE InvoiceNumber=(Select InvoiceNumber from Inserted)
)) * CashDiscountAmount))

answered Jan 20, 2011 at 21:49

John K.

5,4741 gold badge23 silver badges20 bronze badges

Collectives™ on Stack Overflow

Help with a simple SQL query

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related