June 18, 2015 at 5:32 am
Hi everyone,
Can someone help me solve this:
I have 2 tables that look something like this:
Table Price:
id - int
price - varchar
validFrom - datetime
validTo - datetime
itemId - int
....
Table sales:
id - int
price - varchar
date - datetime
itemId - int
........
Tables are much larger but I only gave portions of them. So what I need to do is:
update price in Table sales, from table price, but I have 2 conditions:
1. date in sales has to be between validFrom and ValidTo
2. ItemId in sales has to be = to itemId in table Price
I have done this:
UPDATE Sales
SET Sales.price = Price.price
FROM Sales
INNER JOIN price
ON ( Sales.itemId = Price.itemId)
WHERE Sales.date between Price.validFrom and price.ValidTo);
And it is not working. Any suggestions?
Best regards,
Voya
June 18, 2015 at 5:41 am
you said its not working but you didn't explain whats not working. there is also no sample data from which we can understand the issue. One issue which may raise is that if there is a more than one price for a single item. but it is just a guess
please share some sample data and your desired output to help us understand the actual issue.
June 18, 2015 at 5:48 am
vceklic (6/18/2015)
Hi everyone,Can someone help me solve this:
I have 2 tables that look something like this:
Table Price:
id - int
price - varchar
validFrom - datetime
validTo - datetime
itemId - int
....
Table sales:
id - int
price - varchar
date - datetime
itemId - int
........
Tables are much larger but I only gave portions of them. So what I need to do is:
update price in Table sales, from table price, but I have 2 conditions:
1. date in sales has to be between validFrom and ValidTo
2. ItemId in sales has to be = to itemId in table Price
I have done this:
UPDATE Sales
SET Sales.price = Price.price
FROM Sales
INNER JOIN price
ON ( Sales.itemId = Price.itemId)
WHERE Sales.date between Price.validFrom and price.ValidTo);
And it is not working. Any suggestions?
Best regards,
Voya
Always check an UPDATE with the equivalent SELECT. What does this return?
SELECT s.*, p.*
FROM Sales s
INNER JOIN price p
ON s.itemId = p.itemId
AND s.[date] BETWEEN p.validFrom AND p.ValidTo;
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
June 18, 2015 at 7:06 am
I'm guessing the problem is with the BETWEEN on your datetime field in the WHERE clause. There are a number of issues with using BETWEEN with datetime data. You should use a half-open range for dates (includes the start date, but excludes the end date, or, less frequently, the reverse). This may mean adjusting your ValidTo dates or performing calculations on the current ValidTo date (typically adding one day).
AND Sales.[date] >= Price.validFrom
AND Sales.[date] < p.ValidTo;
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
June 18, 2015 at 7:20 am
Given the nature of what your tables look like I would drop the column from sales entirely. You know what the price was in the sales table from the price table.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 18, 2015 at 7:24 am
ChrisM@Work (6/18/2015)
vceklic (6/18/2015)
Hi everyone,Can someone help me solve this:
I have 2 tables that look something like this:
Table Price:
id - int
price - varchar
validFrom - datetime
validTo - datetime
itemId - int
....
Table sales:
id - int
price - varchar
date - datetime
itemId - int
........
Tables are much larger but I only gave portions of them. So what I need to do is:
update price in Table sales, from table price, but I have 2 conditions:
1. date in sales has to be between validFrom and ValidTo
2. ItemId in sales has to be = to itemId in table Price
I have done this:
UPDATE Sales
SET Sales.price = Price.price
FROM Sales
INNER JOIN price
ON ( Sales.itemId = Price.itemId)
WHERE Sales.date between Price.validFrom and price.ValidTo);
And it is not working. Any suggestions?
Best regards,
Voya
Always check an UPDATE with the equivalent SELECT. What does this return?
SELECT s.*, p.*FROM Sales s
INNER JOIN price p
ON s.itemId = p.itemId
AND s.[date] BETWEEN p.validFrom AND p.ValidTo;
I agree. Run the Select statement and keep adjusting your Where clause until you get the rows from Sales that need updating, with the Price.price you need.
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
June 18, 2015 at 3:42 pm
UPDATE Sales
SET Sales.price = P.price
FROM Sales
CROSS APPLY (
SELECT TOP (1) price
FROM Price
WHERE Sales.itemId = Price.itemId AND
Sales.date BETWEEN Price.validFrom and Price.ValidTo
ORDER BY Price.validFrom DESC
) AS P
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply