We help IT Professionals succeed at work.
Troubleshooting Question

Complex Sql to retrieve latest pricing information

23 Views
Last Modified: 2020-11-19

I have two tables tblProducts (ProductID, ProductName) and tblProductPricing (ProductID,ValidDate, PriceUSD,PriceInd). For each product in tblProducts there may exist 0 to many entries in tblProductPricing Where PriceInd=1 indicates Purchase Cost and PriceInd=2 indicates Sales Prices.

I am trying to find a single SQL to retrieve all products including their respective LATEST cost and sales price (if available). The latest or Valid price is defined by Max(ValidDate) <= Today for each ProductID and PriceInd respectively. Cost and sales price will most likely have different dates.

Thank you so much for your help. Best regards Michael

Comment
Watch Question

HainKurtSr. System Analyst
CERTIFIED EXPERT
can you attach a sample db
with 2 table and some sample data
and show what you need, a screen shot may work or excel, or another table in the db
CERTIFIED EXPERT
Most Valuable Expert 2015
Distinguished Expert 2018
Use the method you have:

Update multiple columns from a different table selecting only the latest record

It could be:

Select
    *,
    (Select Top 1 T.Price From tblProductPricing As T 
    Where T.ProductId = tblProducts.Id And PriceInd = 1
    Order By T.Date Desc) As Purchase,
    (Select Top 1 T.Price From tblProductPricing As T 
    Where T.ProductId = tblProducts.Id And PriceInd = 2
    Order By T.Date Desc) As Sale
From 
    tblProducts

Open in new window

HainKurtSr. System Analyst
CERTIFIED EXPERT
is this what you want


have a look at this
29200941.accdb
Thank you so much to both of you. Gustav - what happens if the pricing table has two columns which need to be returned (PriceUSD, PriceLoc)? The reason is that for each entry I will always have the price is USD and the price in local currency. However, if I change it to

Select
    *,
    (Select Top 1 T.PriceUsd, T.PriceLoc From tblProductPricing As T
    Where T.ProductId = tblProducts.ProductId And PriceInd = 1
    Order By T.ValidDate Desc) As Purchase,
    (Select Top 1 T.PriceUSD, T.PriceLoc From tblProductPricing As T
    Where T.ProductId = tblProducts.ProductId And PriceInd = 2
    Order By T.ValidDate Desc) As Sale
From
    tblProducts

then i get an error msg that more than one field is returned? Any help would be fantastic.... Cheers Michael 
CERTIFIED EXPERT
Most Valuable Expert 2015
Distinguished Expert 2018
You could "double" it, though it may too slow, a test will show:

Select
    *,
    (Select Top 1 T.PriceUSD From tblProductPricing As T 
    Where T.ProductId = tblProducts.Id And PriceInd = 1
    Order By T.Date Desc) As PurchaseUSD,
    (Select Top 1 T.PriceUSD From tblProductPricing As T 
    Where T.ProductId = tblProducts.Id And PriceInd = 2
    Order By T.Date Desc) As SaleUSD,
    (Select Top 1 T.PriceLocal From tblProductPricing As T 
    Where T.ProductId = tblProducts.Id And PriceInd = 1
    Order By T.Date Desc) As PurchaseLocal,
    (Select Top 1 T.PriceLocal From tblProductPricing As T 
    Where T.ProductId = tblProducts.Id And PriceInd = 2
    Order By T.Date Desc) As SaleLocal
From 
    tblProducts

Open in new window

ste5anSenior Developer
CERTIFIED EXPERT
Purchase costs and sales price are different attributes of different processes:

  • In an OLTP model (normal operations), they should belong to different tables, thus different entities.
  • In an OLAP model (reporting stuff), they can be part of a single table, the same dimension, but then you need two columns per row and not two rows.
Hi Ste5an, they are two columns (PriceUsd, PriceLoc) per Row. Cheers michael
ste5anSenior Developer
CERTIFIED EXPERT
Nope, You need the columns PurchasePrice and SalesPrice per row. This would be the dimensional approach:

tblProductPricing (ProductID, ValidDate, PurchasePrice, SalesPrice)

When you need the price to be fixed for different currencies then you need additional 2 columns per currency. While this may look redundant, it is normal in a dimension table in a dimensional data model.
Why would I do this? The Purchase price might get updated at very different intervals from the Sales price... The PriceUSD and PriceLocal is just a special case for Chile. Cheers Michael 
ste5anSenior Developer
CERTIFIED EXPERT
There are two models of interest:

1) The relational model for online transaction processing (OLTP). Using entity relationship modelling (ERM) or object role modelling (ORM) give you the base to derive your schema and then you apply broadly known rules of normalization, the normal forms (NF).

The Purchase price might get updated at very different intervals from the Sales price.
In an OLTP model your requirement will always lead to at least two tables. One storing the the purchase prices, one the sales prices.

"Why?" you may ask.
Cause there are different processes behind those values. Look at the ledgers. Those numbers are not found in the same account.

The only reason why you can store it in one table is the Excel approach: the columns have the same data type. But a relational database is not Excel. So this kind of data model is in most cases incorrect.

The hint, that it is at least suboptimal is the fact, that a query, which should be simple is complex instead.

2) The dimensional model for online analytic processing (OLAP). Here are different models possible, imho the most common is dimensional modelling according to Inmon and Kimball. In a dimensional model, you can have one row per product storing purchase prices and sales prices in the same row with their valid date. There rows are calculated from the above mentioned correct model for faster accessing the correct data for reporting and analysis purpose.
HainKurtSr. System Analyst
CERTIFIED EXPERT
did you check the db I posted
can you add records and check the result...
any comment?
Dear Kurt thank you so much for your help and it is exactly the way I have currently solved it. However, I was trying to get rid of the two queries and just use a single select statement as it will not be used too often and to keep two complex queries seems a bit heavy... Again, thank you so much for your help.. Rg Michael 
HainKurtSr. System Analyst
CERTIFIED EXPERT
then you can just find the code for q2
and embed into q1

so end result will be like this

SELECT tblProducts.ProductID,
       tblProducts.ProductName,
       qryMaxDates.MaxOfValidDate,
       qryMaxDates.PriceInd,
       tblProductPricing.PriceUSD
  FROM (tblProducts INNER JOIN 
       (SELECT tblProductPricing.ProductID, 
               Max(tblProductPricing.ValidDate) AS  MaxOfValidDate,
               tblProductPricing.PriceInd
          FROM tblProductPricing
         GROUP BY tblProductPricing.ProductID, tblProductPricing.PriceInd
      ) as qryMaxDates
    ON tblProducts.ProductID = qryMaxDates.ProductID)
 INNER JOIN tblProductPricing
    ON (qryMaxDates.MaxOfValidDate = tblProductPricing.ValidDate)
   AND (tblProducts.ProductID = tblProductPricing.ProductID);
Hi Kurt this looks very interesting! However, at present it gives me the wrong dates. For example for productid=1 i have the following data set:


tblProductPricing

ID
bCompanyIDProviderIdProductIdValidDatePriceUSDPriceLocPriceInd
28101101-Feb-20$0,00$0,001
83101101-Feb-20$150,00$95.000,002
111101101-Mar-20$0,00$15.000,001
The query should now give me the March 1, 2020 rate for Index 1, and the Feb 1 price for Index 2. However, the query gives me the following result:

Query2
ProductIDProductENMaxOfValidDatePriceIndPriceUSD
1Hot Stone Massage - 90 minutes01-Feb-202$0,00
1Hot Stone Massage - 90 minutes01-Feb-202$150,00
1Hot Stone Massage - 90 minutes01-Mar-201$0,00
Any thoughts on what might be wrong on the query? Also what happens if we need to consider ProviderID as each product may have zero to many providers? Thank you so much for any help... Cheers michael 

HainKurtSr. System Analyst
CERTIFIED EXPERT
here the db
open it, populate it with enough data to cover all scenarios

and show what should be the result
29200941.accdb
Hi Kurt, the problem only occurs if there are two different prices on the same date for the same product and priceind. I tried to use Top 1 but it did not work properly. Otherwise your SQL works marvelous. Thank you so much... Cheers Michael 
Sr. System Analyst
CERTIFIED EXPERT
This problem has been solved!
(Unlock this solution with a 7-day Free Trial)
UNLOCK SOLUTION
You are absoluetly right. Thanks so much for your help. Best regards Michael 

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions