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.

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

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

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

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

>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.

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 ?

Not the solution you were looking for?

IT issues often require a personalized solution. With Ask the Experts™, submit your questions to our certified professionals and receive unlimited, customized solutions that work for you.

## Premium Content

You need an Expert Office subscription to comment.Start Free Trial