Solved

SQL Query

Posted on 2014-02-26
11
304 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
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
 
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 142

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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
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 142

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 142

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 142

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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

758 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now