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

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 ?

0

Featured Post

Everything you need to know about backup and disaster recovery with AWS, for FREE!

Why is this different from all of the other step by step guides? Â Because I make a living as a DBA and not as a writer and I lived through this experience.
Defining the name:
When I talk to people they say different names on this subject stuff lâ€¦

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includâ€¦

Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.