The forum will experience an outage sometime between February 10 at 7:00pm EST and February 12 at 11:59 EST for installation of security updates. The actual time and duration of the outage are unknown but attempts will be made to minimize the downtime. We apologize for any inconvenience.

# Calculate amount on every row in a orderlogg

 I have a trigger that logs the customer order changes in a table. I shall, for each row in the log table to figure out how much the value has changed. I have three columns to be used, quantity, price and discount. In the table, the three columns like this: old_price, new_price, old_qty, new_qty, old_discount, new_discount. Everything works except that I do not get into how I can calculate the amount of change per row. Each entry in the table is logged in the field operation if there is a change, removal, or new row. 1 = New row, 2 = Change 3 = Removal. I do like this below now, it works if you just make one change. If you make two changes or more on the same row, for example, the number and price, it does not work. Even if there are no changes at all on these three fields it dont work. What I have done is wrong, so the question is how I will do it right? `````` (new_qty - old_qty) as cc_qty, (new_price - old_price) as cc_price, (new_discount - old_discount) as cc_discount, (case when cc_qty = 0 Then old_qty else cc_qty end * case when cc_price = 0 Then old_price else cc_price end * ucase when cc_discount = 0 Then (1-old_discount/100) else ( case when ol_operation = 1 then (1-cc_discount/100) else (- cc_discount /100) end ) end ) as cc_amount,`````` asked 31 May '15, 05:14 Rolle 345●18●23●39 accept rate: 0% Here are sample data. Column cc_amount is the value I want to calculate. ``````ordernr----old_price----new_price----old_qty----new_qty----old_discount----new_discount----operation----cc_amount -1025---------0------------1,3----------0----------30------------0---------------0---------------1-------_39_----- new row -1025---------1,3----------1,3---------30----------45------------0---------------1,5-------------2-------_18,62--- update row -1025---------1,3----------1,1---------45----------60------------1,5--------------1--------------2-------_7,72_--- update row -1025---------1,1----------1,1---------60----------40------------1----------------0--------------2------_-21,34_-- update row -1025---------1,1----------0-----------40----------0- -----------0----------------0--------------3------_-44_----- delete row`````` (31 May '15, 15:42) Rolle

 I may not have understood your question fully, but would it not be simply like that (possibly rounded as required)? ``````cc_amount = isnull(new_price * new_qty * (1 - new_discount / 100.0), 0) - isnull(old_price * old_qty * (1 - old_discount / 100.0), 0);`````` answered 01 Jun '15, 04:31 Volker Barth 29.3k●287●438●644 accept rate: 32% You are absolutely right. It was I who complicate it. Thank you for your help. (03 Jun '15, 03:42) Rolle
 toggle preview community wiki:

By Email:

Markdown Basics

• *italic* or _italic_
• **bold** or __bold__
• image?![alt text](/path/img.jpg "title")
• numbered list: 1. Foo 2. Bar
• to add a line break simply add two spaces to where you would like the new line to be.
• basic HTML tags are also supported

Question tags:

×11

question asked: 31 May '15, 05:14

question was seen: 401 times

last updated: 03 Jun '15, 03:42