• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 371
  • 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.

  • 6
1 Solution
Kevin CrossChief Technology OfficerCommented:

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.

John500Author Commented:
Terrific, will do!
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?

Build your data science skills into a career

Are you ready to take your data science career to the next step, or break into data science? With Springboard’s Data Science Career Track, you’ll master data science topics, have personalized career guidance, weekly calls with a data science expert, and a job guarantee.

John500Author Commented:
Never mind on that last one.  The data dictionary was the ticket as mentioned before in the other questions
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?


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'
John500Author Commented:
Query is accurate
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

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