CODEWITHSUNDEEP
sqlsql-serveradventureworks
Big_D
Big_D

Reputation: 15

AdventureWorks - Selling Price Problem - Queries Microsoft SQL Server

Just looking for someone who has downloaded AdventureWorks data and done queries with them.

I was looking for someone to explain the difference between list price and unit price.

I filtered to productid 749 and 83% of the time it is being sold to the customer with listprice = unitprice.

I did some digging to see if there were any discounts etc. with the below query which did not come up with an answer. Is there something I am missing?

select *
from sales.specialoffer
where SpecialOfferID = 1;

select SOH.customerID, 

    SOH.orderdate, 
    pp.listprice, 
    sod.unitprice, 
    sod.ProductID,  
    sod.SpecialOfferID, 
    SOD.UnitPriceDiscount, 
    sr.SalesReasonID,sr.name, 
    sr.ReasonType 
from sales.SalesOrderHeader SOH
inner join sales.SalesOrderDetail SOD
    on soh.SalesOrderID = sod.SalesOrderID
inner join production.Product PP
    on SOD.ProductID= PP.ProductID
left join sales.SalesOrderHeaderSalesReason SOHSR
    on soh.SalesOrderID = sohsr.SalesOrderID
left join sales.SalesReason SR
    on  SOHSR.SalesReasonID = SR.SalesReasonID
where standardcost >0
and PP.listprice != sod.unitprice
and pp.productid = 749
    ;

Upvotes: 0

Views: 855

Answers (2)

Ben
Ben

Reputation: 81

the answer is that all orders without salesperson got the latest price - with salesperson ID the price is half

Upvotes: 0

SMor
SMor

Reputation: 2862

This is really an accounting question. List price, without any further attributes, is generally the "current" price as of now. This value will typically change over time. When you sell (or buy) something, you capture the price of each item sold (as well as other information) with the details of each sale - which is the price you find in the SOD table. Why? For very important accounting reasons.

So no - you aren't missing anything. BTW - did you notice a table called ProductListPriceHistory? So again - the difference you see is a current (or "now") fact versus an historical fact.

Lastly, don't expect a sample database to be completely consistent with respect to all the information it contains. The sample database was built to demonstrate various features of sql server and to serve as a learning platform. FWIW this database is quite dated. MS has developed WorldWideImporters as a replacement.

Upvotes: 0

Related Questions