As for the multiplying, you can use a CASE statement to handle that:
CASE Unit WHEN 0 THEN Price * Mass ELSE Price * Qty END
SELECT arInvoicedetail.Code, arInvoicedetail.Description, arInvoicedetail.Product, arInvoicedetail.Unit, arInvoicedetail.Qty, arInvoicedetail.Mass, arInvoicedetail.Tare, arInvoicedetail.Price, arInvoicedetail.Reason
FROM arInvoicedetail INNER JOIN
arInvoiceheader ON arInvoicedetail.InvoiceNumber = arInvoiceheader.InvoiceNumber
WHERE (arInvoiceheader.Datum >= CONVERT(DATETIME, '2013-02-20 00:00:29.000', 102)) AND (arInvoiceheader.Datum <= CONVERT(DATETIME, '2013-02-25 00:00:00', 102))
SELECT
arInvoicedetail.Code,
arInvoicedetail.Description,
arInvoicedetail.Product,
arInvoicedetail.Unit,
arInvoicedetail.Qty,
arInvoicedetail.Mass,
arInvoicedetail.Tare,
arInvoicedetail.Price,
arInvoicedetail.Reason,
arDebtors.Area AS [Client Area], arDebtors.DGroup AS [Client Group],
case
arInvoicedetail.Unit when 0 then arInvoicedetail.Price * arInvoicedetail.Mass
else
arInvoicedetail.Qty * arInvoicedetail.Price end as Value
FROM
arInvoicedetail INNER JOIN
arInvoiceheader ON arInvoicedetail.InvoiceNumber = arInvoiceheader.InvoiceNumber INNER JOIN
arDebtors ON arInvoiceheader.DebtorAcc = arDebtors.Number
WHERE
(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')
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
not sure yet about the "average" price , but maybe you get enough input from this suggestion to finish that part...
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
) value
FROM
arInvoicedetail INNER JOIN
arInvoiceheader ON arInvoicedetail.InvoiceNumber = arInvoiceheader.InvoiceNumber INNER JOIN
arDebtors ON arInvoiceheader.DebtorAcc = arDebtors.Number
WHERE
(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
) value
FROM
arInvoicedetail INNER JOIN
arInvoiceheader ON arInvoicedetail.InvoiceNumber = arInvoiceheader.InvoiceNumber INNER JOIN
arDebtors ON arInvoiceheader.DebtorAcc = arDebtors.Number
WHERE
(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
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_price
FROM
arInvoicedetail INNER JOIN
arInvoiceheader ON arInvoicedetail.InvoiceNumber = arInvoiceheader.InvoiceNumber INNER JOIN
arDebtors ON arInvoiceheader.DebtorAcc = arDebtors.Number
WHERE
(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
If you are experiencing a similar issue, please ask a related question
Join the community of 500,000 technology professionals and ask your questions.
Connect with top rated Experts
17 Experts available now in Live!