5 Replies - 1042 Views - Last Post: 16 July 2011 - 12:29 PM

#1 Public Designs   User is offline

  • D.I.C Head

Reputation: -3
  • View blog
  • Posts: 145
  • Joined: 08-November 08

adventure works calculate cost sql

Posted 15 July 2011 - 08:03 PM

I am playing around with sql server and the adventureworks database

I am trying to
Select the OrderQty, SalesOrderID, UnitPrice, ProductID, SalesOrderDetailID, and calculate cost of every line item

I have this so far. When it says line item does it mean to select the LineTotal or do some calculation?



SELECT        SalesOrderID, SalesOrderDetailID, ProductID, OrderQty, UnitPrice
FROM            Sales.SalesOrderDetail


Is This A Good Question/Topic? 0
  • +

Replies To: adventure works calculate cost sql

#2 macosxnerd101   User is online

  • Games, Graphs, and Auctions
  • member icon




Reputation: 12316
  • View blog
  • Posts: 45,416
  • Joined: 27-December 08

Re: adventure works calculate cost sql

Posted 15 July 2011 - 09:58 PM

What does the products table look like? Without knowing exactly what information it contains, I'm not sure how to best advise you.
Was This Post Helpful? 0
  • +
  • -

#3 Public Designs   User is offline

  • D.I.C Head

Reputation: -3
  • View blog
  • Posts: 145
  • Joined: 08-November 08

Re: adventure works calculate cost sql

Posted 15 July 2011 - 10:41 PM

Table Columns

SalesOrderID
SalesOrderDetailID
CarrierTrackingNumber
OrderQty
ProductID
UnitePrice
UnitePriceDiscount
LineTotal
rowguid
ModifiedDate


This is the table structure
Was This Post Helpful? 0
  • +
  • -

#4 Ionut   User is offline

  • D.I.C Lover
  • member icon

Reputation: 386
  • View blog
  • Posts: 1,057
  • Joined: 17-July 10

Re: adventure works calculate cost sql

Posted 16 July 2011 - 04:18 AM

you have to use aggregate functions(sum in this case).
Your select only brings the data. Let's think the problem logically:
1. you get the data you want to manipulate
2. you want to calculate the cost of every line from an order <=> OrderQty * UnitPrice
3. an invoice/order can contain 1 or more lines with one product, so if you want to calculate the cost of all products from an invoice you have to sum them up and group them by ProductID and SalesOrderID
select <some_columns>, Sum(OrderQty * UnitPrice)
From <table>
Group By ProductId, SalesOrderID, <the_remaining_columns>


4. further calculation will start from the above queries, having in mind aggregate function
Was This Post Helpful? 2
  • +
  • -

#5 Public Designs   User is offline

  • D.I.C Head

Reputation: -3
  • View blog
  • Posts: 145
  • Joined: 08-November 08

Re: adventure works calculate cost sql

Posted 16 July 2011 - 10:25 AM

See that is what I was thinking it was but it caused me confusion because the column LineTotal already has that calculation in it which is one the default columns in adventureworks
Was This Post Helpful? 0
  • +
  • -

#6 Ionut   User is offline

  • D.I.C Lover
  • member icon

Reputation: 386
  • View blog
  • Posts: 1,057
  • Joined: 17-July 10

Re: adventure works calculate cost sql

Posted 16 July 2011 - 12:29 PM

As I see from the table schema, it has one more column called UnitePriceDiscount that should be added to your calculation
Select 
    case UnitPriceDiscount 
         when 0 then UnitPrice*OrderQty
         else UnitPrice*OrderQty*UnitPriceDiscount/100 --depends on how the values are stored in the column 
from <table>



Anyway, the field TotalLine is probably added just for help(a method of verification). I personally don't like using computed fields.
Was This Post Helpful? 1
  • +
  • -

Page 1 of 1