Solved

Maximum Record Selection

Posted on 2013-06-26
8
184 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
 
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
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 

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

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

Hot fix for .Net Crystal Reports 10.2.3600.0 to fix problems with sub reports running on 64 bit operating systems ISSUE: Reports which contain subreports fail with error "Missing Parameter Value" DEPLOYMENT SERVER OS: Windows 2008 with 64 bi…
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 …
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.

743 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now