Solved

Maximum Record Selection

Posted on 2013-06-26
8
185 Views
Last Modified: 2013-06-28
I have a table:

Product

This table is linked to another table:

Product Price

This table contains:
Price Date
Price

I need to be able to select the latest price for a product.
0
Comment
Question by:whittinga2
  • 3
  • 3
  • 2
8 Comments
 
LVL 100

Expert Comment

by:mlmcc
ID: 39277961
You are going to have to use a view/stored procedure in the database or a Crystal command

mlmcc
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 39278011
The query will be something like

SELECT Product.ID, Product.Description, ...
(SELECT Price.Price  FROM Price WHERE Price.ProductID = Product.ID AND Price.PriceDate = Maximum(Price.PriceDate)
FROM Product

mlmcc
0
 
LVL 100

Assisted Solution

by:mlmcc
mlmcc earned 250 total points
ID: 39278547
Error in the SQL

SELECT Product.ID, Product.Description, ...
(SELECT Price.Price  FROM Price WHERE Price.ProductID = Product.ID AND Price.PriceDate = (SELECT Maximum(Price.PriceDate) FROM Price WHERE Price.ProductID = Product.ID ))
FROM Product

mlmcc
0
Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

 
LVL 34

Expert Comment

by:James0628
ID: 39280779
FWIW, you _could_ find the most recent price in a report, but it would probably be much more efficient to do it in the db query, as mlmcc suggested.  It would depend a lot on your data and the report, and what you wanted to do with that price (just display it, or use it in calculations).

 James
0
 

Author Comment

by:whittinga2
ID: 39283648
Cheers fellas. I will give it a go and get back to you.

Sorry for the delay.
0
 

Author Comment

by:whittinga2
ID: 39283730
Any idea why I've been given a syntax error?

SELECT *
(SELECT * FROM pricehistory WHERE pricehistory.productid = productdetails.productid AND pricehistory.priceeffectivefromdate =
(SELECT Maximum(pricehistory.priceeffectivefromdate) FROM pricehistory WHERE pricehistory.productid = productdetails.productid))
FROM productdetails
0
 
LVL 34

Accepted Solution

by:
James0628 earned 250 total points
ID: 39283816
The subquery was meant to pull in the latest price (_only_ the price), and that would become another column in the main query.  You have "(SELECT * FROM pricehistory ...", so you're pulling in all of the columns from pricehistory.

 So, that should be

(SELECT your_price_column FROM pricehistory WHERE <etc.>


 If you could have more than one entry in pricehistory with the same date, then you should use something like TOP 1, to make sure that the subquery only returns one value:

(SELECT TOP 1 your_price_column FROM pricehistory WHERE <etc.>

 OTOH, if priceeffectivefromdate is a datetime, then that probably won't happen.  But I wanted to mention it anyway, just in case.


 You probably also want to set a name for that subquery column.

 And you need a "," at the end of the first line, to separate the other columns (*) and the subquery column.

 So, putting all of that together, maybe something like this:

SELECT *,
(SELECT your_price_column FROM pricehistory WHERE pricehistory.productid = productdetails.productid AND pricehistory.priceeffectivefromdate =
(SELECT Maximum(pricehistory.priceeffectivefromdate) FROM pricehistory WHERE pricehistory.productid = productdetails.productid)) AS latest_price
FROM productdetails


 Obviously, replace your_price_column with the actual column name.  And you can add the TOP 1 if you need it.

 James
0
 

Author Closing Comment

by:whittinga2
ID: 39283859
Thanks to both of you. Points allocated.
0

Featured Post

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Crystal Reports: 5 Tests for Top Performance It is complete, your masterpiece report.  Not only does it meet your customer’s expectations, it blows them out the water, all they want is beautifully summarised and displayed in a myriad of ways. …
There have always been a lot of questions related to when Crystal Reports evaluates report components (such as formulas, summaries, cross-tabs, charts, to name a few examples). Crystal Reports uses a two-pass reporting process to provide greater …
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

831 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question