• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 368
  • Last Modified:

Help with query against AdventureWorks database - Products ordered

Need a hand with this query:

Return the First Name, Last Name, Product Name, and Sale Price for all products ordered in the month of November 2001.

Thanks!
0
John500
Asked:
John500
  • 6
1 Solution
 
Kevin CrossChief Technology OfficerCommented:
John500,

Post what you have so far on this and the exact difficulty you are having in producing the query. Hopefully through the other questions you have learned a bit about joins and filtering data that this should become easier as you go along.

Kevin
0
 
John500Author Commented:
Terrific, will do!
0
 
John500Author Commented:
As I remember now, the thing that was making this question so difficult was finding a field called FirstName and LastName related to customers.  Any input here?

Thanks
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
John500Author Commented:
Never mind on that last one.  The data dictionary was the ticket as mentioned before in the other questions
0
 
John500Author Commented:
For whatever it's worth, I would think the recommended price would be the selling price minus the discounted price.  I say that because there are only a few options here:

SalesOrderDetail Table
-------------------------------
UnitPrice                    |   money  |  Not null    |     Selling price of a single product.
UnitPriceDiscount      |   money  |  Not null    |     Discount amount.

Product Table
-------------------------------
StandardCost            |  money   |   Not null   |     Standard cost of the product.
ListPrice                    |  money   |   Not null   |     Selling price.

Is there any other way to get a derived or recommended selling price other than the discount?

 

 
0
 
John500Author Commented:
Ended up going with this:

SELECT FirstName, LastName, PP.Name AS 'Product Name', UnitPrice AS 'Sales Price', OrderDate
FROM Sales.Store AS S
JOIN Sales.SalesOrderHeader AS SO ON S.CustomerID = SO.CustomerID
JOIN Sales.SalesOrderDetail AS SD ON SD.SalesOrderID = SO.SalesOrderID
JOIN Production.Product AS PP ON PP.ProductID = SD.ProductID
JOIN Person.Contact AS PC ON PC.ContactID = S.CustomerID
WHERE OrderDate > '2001-10-31' AND OrderDate < '2001-12-01'
0
 
John500Author Commented:
Query is accurate
0

Featured Post

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now