help with SELECT - 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

General SQL Server Forums

New to SQL Server Programming

help with SELECT

Author

Topic

elic05
Yak Posting Veteran

62 Posts

Posted - 2012-06-14 : 10:56:27

in one table i have records that holds update of a price for a product
productID,newPrice,updateDate

in another table i have records of each product sold
productID,soldDate

I want to know the price of a product at a specific date
(the product price has to be taken from the first table based on the product id and the date)

thanks

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2012-06-14 : 11:10:53

What have you tried so far?

Jim

Everyday I learn something that somebody else already knew

elic05
Yak Posting Veteran

62 Posts

Posted - 2012-06-14 : 11:17:39

I think I have to sort the first table by its date column
Desc

and to return only one result top(1)
where date of the sale is after the update date

I an not sure its the correct way
if it is
please help me write it

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-06-14 : 12:05:49

Just join the two tables on the date from the sold table and max date less than that from the price table

from sold s
join price p
on p.productid = s.productid
and p.date = (select max(p2.date) from price p2 where s.productid = p2.productid and p2.date <= s.date)

Another way might be to create a derived pricing table with all days from the price start to the last sold date and just join to that - you could also maintain that daily as a permanent table.
Might be easier and more efficient to use.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-06-14 : 12:20:43

[code]
SELECT s.productID,s.soldDate,p.newPrice
FROM sold s
CROSS APPLY (SELECT TOP 1 newPrice
FROM price
WHERE productID = s.productID
AND updateDate < = soldDate
ORDER BY updateDate DESC)p
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

elic05
Yak Posting Veteran

62 Posts

Posted - 2012-06-14 : 12:22:40

GREAT!!! nigelrivett - thanks a lot
I checked your first solution and its working fine

visakh16, I will check your, too
i am sure its working
although i have no idea whats apply cross
is there any option in this forum to mark reply as an answer?

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-06-14 : 12:27:27

Nope - all posts shold be worth reading.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-06-14 : 12:32:05

quote:
Originally posted by elic05

GREAT!!! nigelrivett - thanks a lot
I checked your first solution and its working fine

visakh16, I will check your, too
i am sure its working
although i have no idea whats apply cross
is there any option in this forum to mark reply as an answer?

see uses of it

http://visakhm.blogspot.com/2010/01/multipurpose-apply-operator.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

ElenaSTL1
Starting Member

9 Posts

Posted - 2012-06-14 : 14:57:00

To Visakh16:
If you think that Scenario 2 from examples of your link works for this problem – yes, it works, but why should we confuse people with new syntax if simple LEFT JOIN statement works fine? ( example of nigelrivett)
In scenario 1 you show running total for Price which doesn’t have any sense. Running total should be for amount and amount = price*quantity.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-06-14 : 15:30:59

quote:
Originally posted by ElenaSTL1

To Visakh16:
If you think that Scenario 2 from examples of your link works for this problem – yes, it works, but why should we confuse people with new syntax if simple LEFT JOIN statement works fine? ( example of nigelrivett)
In scenario 1 you show running total for Price which doesn’t have any sense. Running total should be for amount and amount = price*quantity.

Its not about confusing people but its about using APPLY operator which is new feature available from 2005 onwards. You're free to use whatever suggestion you're comfortable with but its always good to know different ways of dealing with same problem

That was just an example. you can apply same logic to apply it for amount if you want

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources