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

0

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restoreâ„¢. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

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

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes. We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that witâ€¦