Solved

SQL Query

Posted on 2014-02-26
11
313 Views
Last Modified: 2014-04-07
Good Day

I need some help with a sql query, I was able to create the selection query but I am not sure how to subtract and multiply in T-SQL.

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

Open in new window



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.
0
Comment
Question by:Yeaktom
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
11 Comments
 
LVL 26

Expert Comment

by:Shaun Kline
ID: 39888834
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
0
 
LVL 65

Expert Comment

by:Jim Horn
ID: 39889080
An excellent idea would be to post some mock-up data of your current situation, and what you are trying to accomplish here.
0
 
LVL 2

Author Comment

by:Yeaktom
ID: 39889467
Hi Guys

Please find a better explanation attached.
Query.xlsx
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 2

Author Comment

by:Yeaktom
ID: 39891167
I got up to here.

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')

Open in new window


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
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 39891259
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 

Open in new window

not sure yet about the "average" price , but maybe you get enough input from this suggestion to finish that part...
0
 
LVL 2

Author Comment

by:Yeaktom
ID: 39894000
I get the following error.

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

Open in new window

0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 39894117
as suggested by my code AND by the error message, you need to "group by" also the product column
GROUP BY  arInvoicedetail.Description, Product
0
 
LVL 2

Author Comment

by:Yeaktom
ID: 39894142
Hi Guy

Thanks, I missed the , Product part.

This is what the script currently look like.

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

Open in new window


This is the info it is returning. The Qty, Mass and value doesn't calculate correctly. Any suggestions?

Description      Product      qty      mass      value
LAMB A0      SA0      0      17162,7000975609      17162,7000975609
LAMB A1      SA1      0      113755,300302505      113755,300302505
LAMB A2      SA2      0      1496155,62181473      1496155,62181473
LAMB A3      SA3      0      388114,476806641      388114,476806641
LAMB A4      SA4      0      66873,4689941406      66873,4689941406
LAMB A5      SA5      0      8797,80029296875      8797,80029296875
LAMB A6      SA6      0      23565,8305969238      23565,8305969238
MUTTON AB0      SAB0      0      380,799987792969      380,799987792969
MUTTON AB1 RAM      SAB1R      0      725      725
MUTTON AB2      SAB2      0      3627,47998046875      3627,47998046875
MUTTON AB2 RAM      SAB2R      0      1853,49987792969      1853,49987792969
MUTTON AB3      SAB3      0      2987,94982910156      2987,94982910156
MUTTON AB4      SAB4      0      1057,40002441406      1057,40002441406
MUTTON AB5      SAB5      0      598,400024414063      598,400024414063
MUTTON AB6      SAB6      0      873,800048828125      873,800048828125
MUTTON AB6 RAM      SAB6R      0      700      700
MUTTON B1      SB1      0      696,799987792969      696,799987792969
MUTTON B2      SB2      0      2939,89996337891      2939,89996337891
MUTTON B3      SB3      0      987      987
MUTTON CO      SC0      0      9426,69982910156      9426,69982910156
MUTTON C0 RAM      SC0R      0      1985      1985
MUTTON C1      SC1      0      19229,8999023438      19229,8999023438
MUTTON C1 RAM      SC1R      0      8754,20043945313      8754,20043945313
MUTTON C2      SC2      0      58169,1494140625      58169,1494140625
MUTTON C2 RAM      SC2R      0      6500,95007324219      6500,95007324219
MUTTON C3      SC3      0      9600,30029296875      9600,30029296875
MUTTON C4      SC4      0      1657,60034179688      1657,60034179688
MUTTON C6      SC6      0      1576,80004882813      1576,80004882813
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 39894172
>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?
0
 
LVL 2

Author Comment

by:Yeaktom
ID: 39894232
Hi Guy

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.

I hope that explains it better.
Query-Data.xlsx
0
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 39912095
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_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

Open in new window


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

Free eBook: Backup on AWS

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

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

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.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

730 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question