Query help - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Query help

Author

Topic

ArieB
Starting Member

9 Posts

Posted - 2007-07-22 : 08:34:34

here's the deal:
tblPriceChange
SKU ChangeDate Price
ABC 10/10/2006 300
ABC 20/07/2007 400
BCD 10/7/2007 400
EFG 10/5/2006 400
EFG 20/5/2007 300

I need relevant prices at a stopDate say 10/12/2006
But I also need BCD (That was not created at that date)!

SKU ChangeDate Price
ABC 10/10/2006 300
BCD 10/7/2007 400
EFG 10/5/2006 400

Thanks a lot for any help...

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-22 : 08:59:27

[code]SELECT SKU, ChangeDate, Price FROM (
SELECT SKU, ChangeDate, Price, ROWNUMBER() OVER (PARTITION BY SKU ORDER BY ChangeDate DESC) AS RecID
FROM tblPriceChange WHERE ChangeDate < '20061211' -- All records less than Dec 11, 2006
) AS d WHERE RecID = 1[/code]Use the same technique learned here
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=86525

Peter Larsson
Helsingborg, Sweden

ArieB
Starting Member

9 Posts

Posted - 2007-07-22 : 09:10:39

Hey Peter again to the rescue. Thanks..

RowNumber is not recognized.
Any equivalant in SQL2000?

I inspired myself from your last response to write this:

Select Distinct SKU,(isnull((Select Max(ChangeDate) from @pricechange as pc2 where pc2.SKU=pc1.SKU and pc2.ChangeDate<=@StartDate),(Select Min(ChangeDate) from @pricechange as pc2 where pc2.SKU=pc1.SKU))) as ChangeDate,
(Select Price from @pricechange as pc3 where pc3.SKU=pc1.SKU and ChangeDate=(isnull((Select Min(ChangeDate) from @pricechange as pc2 where pc2.SKU=pc1.SKU and pc2.ChangeDate<=@StartDate),(Select Max(ChangeDate) from @pricechange as pc2 where pc2.SKU=pc1.SKU)))) as Price
from @pricechange as pc1

it works but it doesn't look good at all.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-22 : 09:43:30

Well, you have posted in a SQL Server 2005 forum.

This will work in SQL Server 2000. But beware that if there are multiple records for the last date, you will get duplicates back

SELECT		pc.SKU,
		pc.ChangeDate,
		pc.Price
FROM		dbo.tblPriceChange AS pc
INNER JOIN	(
			SELECT		SKU,
					MAX(ChangeDate) AS MaxDate
			FROM		tblPriceChange
			WHERE		ChangeDate < '20061211' -- All records less than Dec 11, 2006
			GROUP BY	SKU
		) AS x ON x.SKU = pc.SKU AND x.MaxDate = pc.ChangeDate
ORDER BY	pc.SKU

Peter Larsson
Helsingborg, Sweden

somu_p
Starting Member

13 Posts

Posted - 2007-07-23 : 03:31:16

SELECT * FROM tblPriceChange WHERE ChangeDate<='10/12/2006' OR SKU='BCD'

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources