[SOLVED] AdventureWorks - Selling Price Problem

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
    ;

Solution

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.