I need to subtract all items with a code 3 from code 1 where the product field is the same. Then I have to multiply price with mass where the unit is 0 and multiply qty with price where the unit is 1.

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

We'll need more information to understand what you mean by "subtract all items with a code 3 from code 1". Assuming these are the values of your code field, what needs subtracting, the qtyfield?

As for the multiplying, you can use a CASE statement to handle that:
CASE Unit WHEN 0 THEN Price * Mass ELSE Price * Qty END

Please find a better explanation attached. Query.xlsx

0

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

I am not sure how to tell the query to combine the lines where the product codes are the same value, and subtract the values from the lines with code id 3 from the ones with code id 1

Guy Hengel [angelIII / a3]Billing EngineerCommented:

it will be like this:

select Description, product , sum( case when code = 3 then -1 else 1 end * price * case when unit = 1 then qty else 0 end ) qty , sum( case when code = 3 then -1 else 1 end * price * case when unit = 0 then mass else 0 end ) mass , sum( case when code = 3 then -1 else 1 end * price * case when unit = 1 then qty when unit = 0 then mass end ) value from ... where ...group by Description, product

Msg 8120, Level 16, State 1, Line 1
Column 'arInvoicedetail.Product' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

This is what the query currently looks like.

select arInvoicedetail.Description, Product , sum( case when code = 3 then -1 else 1 end * arInvoicedetail.Price * case when arInvoicedetail.unit = 1 then arInvoicedetail.qty else 0 end ) qty , sum( case when code = 3 then -1 else 1 end * arInvoicedetail.price * case when unit = 0 then arInvoicedetail.mass else 0 end ) qty , sum( case when arInvoicedetail.code = 3 then -1 else 1 end * arInvoicedetail.price * case when arInvoicedetail.unit = 1 then arInvoicedetail.qty when arInvoicedetail.unit = 0 then arInvoicedetail.mass end ) valueFROM arInvoicedetail INNER JOIN arInvoiceheader ON arInvoicedetail.InvoiceNumber = arInvoiceheader.InvoiceNumber INNER JOIN arDebtors ON arInvoiceheader.DebtorAcc = arDebtors.NumberWHERE (arInvoiceheader.Datum >= CONVERT(DATETIME, '2013-02-20 00:00:29.000', 102)) AND (arInvoiceheader.Datum <= CONVERT(DATETIME, '2013-02-25 00:00:00', 102)) AND (arInvoicedetail.Product >= 'SA0') AND (arInvoicedetail.Product <= 'SC6R')GROUP BY arInvoicedetail.Description

select arInvoicedetail.Description, Product , sum( case when code = 3 then -1 else 1 end * Price * case when arInvoicedetail.unit = 1 then arInvoicedetail.qty else 0 end ) qty , sum( case when code = 3 then -1 else 1 end * price * case when unit = 0 then arInvoicedetail.mass else 0 end ) mass , sum( case when arInvoicedetail.code = 3 then -1 else 1 end * arInvoicedetail.price * case when arInvoicedetail.unit = 1 then arInvoicedetail.qty when arInvoicedetail.unit = 0 then arInvoicedetail.mass end ) valueFROM arInvoicedetail INNER JOIN arInvoiceheader ON arInvoicedetail.InvoiceNumber = arInvoiceheader.InvoiceNumber INNER JOIN arDebtors ON arInvoiceheader.DebtorAcc = arDebtors.NumberWHERE (arInvoiceheader.Datum >= CONVERT(DATETIME, '2013-02-20 00:00:29.000', 102)) AND (arInvoiceheader.Datum <= CONVERT(DATETIME, '2013-02-25 00:00:00', 102)) AND (arInvoicedetail.Product >= 'SA0') AND (arInvoicedetail.Product <= 'SC6R')GROUP BY arInvoicedetail.Description, product

Guy Hengel [angelIII / a3]Billing EngineerCommented:

>The Qty, Mass and value doesn't calculate correctly.
without seeing the original data compared to what it returns currently and compared to what is should return it will be difficult to suggest.
can you please take 1 product/description to show the actual data, simplified as much as possible to show the issue?

Attached in this file is a sample of the first 1000 lines for the 2 tables.

This is what needs to happen.

The query must multiply the Qty with the price if the unit = 1, when the unit = 0 it should multiply the mass with the price to get the value.
Lines with the same Description must be grouped into one line, code is a debit and code 3 is a credit, so when the id is 3 the value must be deducted from the line with code 1.
Then I want to divide the mass if code 1 into the value and qty into the value if code 3 to get a average price.

Guy Hengel [angelIII / a3]Billing EngineerCommented:

difficult if one doesn't get the "simple" data listing with a couple of lines one, containing :
* this is the input I have
* this is the corresponding output I want ...

anyhow, let me try this again:

select arInvoicedetail.Description, Product , sum( case when code = 3 then -1 else 1 end * case when arInvoicedetail.unit = 1 then arInvoicedetail.qty else 0 end ) sum_qty , sum( case when code = 3 then -1 else 1 end * case when arInvoicedetail.unit = 0 then arInvoicedetail.mass else 0 end ) sum_mass , sum( case when arInvoicedetail.code = 3 then -1 else 1 end * arInvoicedetail.price * case when arInvoicedetail.unit = 1 then arInvoicedetail.qty when arInvoicedetail.unit = 0 then arInvoicedetail.mass end ) sum_priceFROM arInvoicedetail INNER JOIN arInvoiceheader ON arInvoicedetail.InvoiceNumber = arInvoiceheader.InvoiceNumber INNER JOIN arDebtors ON arInvoiceheader.DebtorAcc = arDebtors.NumberWHERE (arInvoiceheader.Datum >= CONVERT(DATETIME, '2013-02-20 00:00:29.000', 102)) AND (arInvoiceheader.Datum <= CONVERT(DATETIME, '2013-02-25 00:00:00', 102)) AND (arInvoicedetail.Product >= 'SA0') AND (arInvoicedetail.Product <= 'SC6R')GROUP BY arInvoicedetail.Description, product

I am still confused about this line: Then I want to divide the mass if code 1 into the value and qty into the value if code 3 to get a average price.
can you, please, put together only a couple of lines, showing EXACTLY what this should become, input vs output ?

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for freeEdge Out The Competitionfor your dream job with proven skills and certifications.Get started todayStand Outas the employee with proven skills.Start learning today for freeMove Your Career Forwardwith certification training in the latest technologies.Start your trial today

As for the multiplying, you can use a CASE statement to handle that:

CASE Unit WHEN 0 THEN Price * Mass ELSE Price * Qty END