match a sale date to a price date.

[JITA]

Hi, I am trying to figure out how to match a sale date to a price date.
I have three tables:
tblProd
ID
Desc

tblPrices
PriceDate
Price
ProdID

tblSales
SaleDate
SaleQ
ProdID

Prices do not change frequently, so the tblPrice is not updated regularly.
What I would like to achieve is to find a match between a sale at a certain date and the valid price at the time of the sale. Currently I am working with only one product, but even then I am not able to find the solution.
This is the query I have so far

SELECT
tblSale.SaleQ,
tblPrices.Price,
Max(tblPrices.PriceDate),
tblSales.SaleDate
FROM
tblSales
LEFT JOIN tblPrices
ON tblSales.ProdID = tblPrices.ProdID
WHERE (((tblSales.Date)>=[tblPrices].[Date]))
GROUP BY
tblSales.SaleQ,
tblPrices.Price,
tblSales.Date
ORDER BY
tblSales.Date;

Running the query results in multiple rows with different prices (different staring dates) for a sale at a certain date. I understand it has to do with my statement tblSales.Date)>=[tblPrices].[Date], but I have no clue on how to select just one price.

Example results:
PriceDate Price ProdID
02-01-2015 2.30 1
02-03-2015 2.70 1

shows
SaleQ Price PriceDate SalesDate
5 2.30 02-01-2015 02-01-2015
2 2.30 02-01-2015 02-02-2015
7 2.70 02-03-2015 02-03-2015
7 2.30 02-01-2015 02-03-2015

Thanks for your support

 

[SkipVought]

Hi,

So a ProdID has a price on a price date and now you want to associate a sale of a ProdID with sale date.

I'd think that You'ld have an inventory quantity of the ProdID on hand. Depending on your inventory turns, your price date may be years, month or days away from your sales date. I don't understand this criteria????

Also you have not supplies any source data for the tables in order to test the SQL!

 

[JITA]

SkipVought I am afraid I don't understand your comment. At the moment I have no interest in inventory, just combine sales with prices. And my example does give data.
Please explain what you mean.

 

[SkipVought]

You may have purchased a product on 20014/01/01 quantity 5 at $1.00 and 20015/01/01 quantity 1 at $1.50. Your current inventory may be 3, implying that you still have 2 at $1.00 and 1 at $1.50 when your sale occurs.

So how is a date of 2015 and a price of $1.50 relevant to a current sale?

The answer you're looking for is to sub query the price for the MAX(purchase date) and use the max purchase date for each PurcID in you >= Where clause.

 

[JITA]

@SkipVought
I have tried your suggestion, but yet succeeded.
SELECT tblSales.SaleQ, tblPrices.Price, tblPrices.PriceDate, tblSales.SaleDate
FROM tblSales LEFT JOIN tblPrices ON tblSales.ProdID = tblPrices.ProdID
WHERE (
SELECT MAX(tblPrices.PriceDate)
FROM tblPrices
) <= tblSales.SaleDate
GROUP BY tblSales.SaleQ, tblPrices.Price, tblSales.SaleDate, tblPrices.PriceDate
ORDER BY tblSales.SaleDate;

This results in an outcome that shows only part of the sales, but the sales showed are combined with multiple prices. I tried also the WHERE clause SELECT MAX(tblSales.SaleDate)
FROM tblSales) >= tblPrices.PriceDate, but then each sales is combined with every price in the price table. Changing >= to <= results in no record at all.

Obviously I don't understand at the moment what to do.

 

[SkipVought]

In your sub query, you need a Where the PurchID equals the PurchID in the main query.
As I stated "use the max purchase date for each PurcID".

 

[JITA]

Whow that was a quick response

Unfortunately this suggestion does not change the outcome. The result of my query is that I get all records from tblPrices and all records from tblSales. I still have no clue

 

[SkipVought]

Please post your SQL.

 

[JITA]

SELECT tblSales.SaleQ, tblPrices.Price, tblPrices.PriceDate, tblSales.SaleDate
FROM tblSales LEFT JOIN tblPrices ON tblSales.ProdID = tblPrices.ProdID
WHERE (
SELECT
MAX(tblPrices.PriceDate)
FROM tblPrices
WHERE
tblOpgewekteEnergie.Prod = tblEnergiePrijzen.Prod
) <= tblSales.SaleDate
GROUP BY tblSales.SaleQ, tblPrices.Price, tblSales.SaleDate, tblPrices.PriceDate
ORDER BY tblSales.SaleDate;

 

[SkipVought]

SELECT
  tblSales.SaleQ
, tblPrices.Price
, tblPrices.PriceDate
, tblSales.SaleDate

FROM tblSales LEFT JOIN tblPrices
  ON tblSales.ProdID = tblPrices.ProdID

WHERE (
SELECT 
  MAX(a.PriceDate) 
FROM tblPrices a
WHERE a.ProdID = tblPrices.ProdID
) <= tblSales.SaleDate

ORDER BY tblSales.SaleDate;

 

[JITA]

Again fast response.
I Appreciate your help, but this time I still don't get the expected result.
The query shows all sales after the last date the price has changed, but these sales are compared with all price records I have.
I have a price table with 3 prices
02-01-2015 25
02-10-2015 23
02-15-2015 28

Each sale record from 02-15-2015 and later is combined with all three price records.

I have to end my session now. Thanks for your input.

 

[SkipVought]

Please post some related sample sales and price data that illustrates this problem, along with exactly what you would expect from that sample data.

 

[JITA]

I am back again
Some data
tblSales
Dat SaleQ ProdID
02-04-2015 530 1
02-05-2015 370 1
02-06-2015 950 1
02-09-2015 130 1
02-10-2015 70 1
02-11-2015 60 1
02-12-2015 90 1
02-13-2015 860 1
02-16-2015 810 1
02-17-2015 710 1
02-18-2015 1010 1
02-19-2015 1000 1
02-20-2015 80 1
02-23-2015 910 1

PriceDate ProdID Price
02-01-2015 1 25
02-10-2015 1 23
02-16-2015 1 28

Expected result
Dat ProdID SaleQ Price
04-02-2015 1 530 25
05-02-2015 1 370 25
06-02-2015 1 950 25
09-02-2015 1 130 25
10-02-2015 1 70 23
11-02-2015 1 60 23
12-02-2015 1 90 23
13-02-2015 1 860 23
16-02-2015 1 810 28
17-02-2015 1 710 28
18-02-2015 1 1010 28
19-02-2015 1 1000 28
20-02-2015 1 80 28
23-02-2015 1 910 28

 

[SkipVought]

Hey!!!

You changed the field headings!!!!

I'm not going to waste my time trying to figure out what should be changed!

Please make the examples CONSISTENT with the SQL that we have been using.

 

[Andrzejek]

Also, use Pre tag to Preserve spacing, so your example will look like this:

tblSales[pre]
Dat SaleQ ProdID
02-04-2015 530 1
02-05-2015 370 1
02-06-2015 950 1
02-09-2015 130 1
02-10-2015 70 1
02-11-2015 60 1
02-12-2015 90 1
02-13-2015 860 1
02-16-2015 810 1
02-17-2015 710 1
02-18-2015 1010 1
02-19-2015 1000 1
02-20-2015 80 1
02-23-2015 910 1
[/pre]

A lot easier to read.

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[JITA]

Sorry I did not want to confuse you. And I did not know about PRE tag either,
I will try again

tblSales

[pre]tblSales.SaleDate tblSales.ProdID tblSales.SaleQ
04-02-2015 1 530
05-02-2015 1 370
06-02-2015 1 950
09-02-2015 1 130
10-02-2015 1 70
11-02-2015 1 60
12-02-2015 1 90
13-02-2015 1 860
16-02-2015 1 810
17-02-2015 1 710
18-02-2015 1 1010
19-02-2015 1 1000
20-02-2015 1 80
23-02-2015 1 910
[/pre]

tblPrices

[pre]tblPrices.PriceDate tblPrices.ProdID tblPrices.Price
02-01-2015 1 25
02-10-2015 1 23
02-16-2015 1 28
[/pre]

Expected result
[pre]tblSales.Date tblSales.ProdID tblSales.SaleQ tblPrices.Price
02-04-2015 1 530 25
02-05-2015 1 370 25
02-06-2015 1 950 25
02-09-2015 1 130 25
02-10-2015 1 70 23
02-11-2015 1 60 23
02-12-2015 1 90 23
02-13-2015 1 860 23
02-16-2015 1 810 28
02-17-2015 1 710 28
02-18-2015 1 1010 28
02-19-2015 1 1000 28
02-20-2015 1 80 28
02-21-2015 1 910 28
[/pre]

For this example I just use one productID. I hope this is sufficient for you.

 

[JITA]

I see my dates are mixed up (european/american) I hope that is not too confusing

 

[SkipVought]

see if this might work for you

SELECT
  s.SaleDate
, s.ProdID
, s.SaleQ
, q.Price
, q.PriceDate

FROM
  [tblSales] s
, (
select
  p.ProdID
, p.Price
, p.PriceDate

From [tblPrices] p 

Where p.PriceDate = (Select Max(z.PriceDate) from [tblPrices] z where p.ProdID = z.ProdID)
) q

where s.SaleDate is not null

ORDER BY s.SaleDate;

 

[JITA]

I think you are on the right track. Only q is not defined. Running the query it ask for q.price and q.pricedate to enter a value. I suspect that q.price and q.pricedate should be p.price and p.pricedate but then I get the message "the specified field p.price could refer to more then one table in the from clause of your sql statement

 

[JITA]

Found it (thanks to

http://dba.stackexchange.com/)

This query produces the results I need

[pre]SELECT
s.Saledate,
s.SaleQ,
s.ProdID,
p.Price,
p.PriceDate
FROM
tblSales AS s
LEFT JOIN
tblPrices AS p
ON
p.ProdID = s.ProdID
AND
p.Datum <= s.Datum
WHERE NOT EXISTS
(
SELECT 1
FROM
tblPrices AS p1
WHERE
p1.ProdID = p.ProdID
AND
p1.PriceDate <= s.SaleDate
AND
p1.PriceDate > p.PriceDate
)
ORDER BY
s.Date;
[/pre]