Price effectivity query - 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 2000 Forums

Transact-SQL (2000)

Price effectivity query

Author

Topic

jonasalbert20
Constraint Violating Yak Guru

300 Posts

Posted - 2007-05-30 : 20:49:17

I have this data on transaction..



select trans.*
from   (
	select 1 as stkid, cast('1/1/2007' as datetime) as transdate, 1 as qty union all
	select 1 as stkid, cast('1/2/2007' as datetime) as transdate, 6 as qty union all
	select 1 as stkid, cast('1/3/2007' as datetime) as transdate, 4 as qty union all
	select 1 as stkid, cast('1/7/2007' as datetime) as transdate, 2 as qty union all
	select 1 as stkid, cast('1/10/2007' as datetime) as transdate, 1 as qty
	) as trans

and this on pricing history...



select pricinghistory.*
from   (
	select 1 as stkid, 1.2 as sPrice, cast('12/1/2006' as datetime) as effectivity union all
	select 1 as stkid, 1.85 as sPrice, cast('1/3/2007' as datetime) as effectivity union all
	select 1 as stkid, 3 as sPrice, cast('1/5/2007' as datetime) as effectivity 
	) as pricinghistory

The result may look like this...



select result.*
from   (
	select 1 as stkid, cast('1/1/2007' as datetime) as transdate, 1 as qty, 1.2 as sPrice union all
	select 1 as stkid, cast('1/2/2007' as datetime) as transdate, 6 as qty, 1.2 as sPrice union all
	select 1 as stkid, cast('1/3/2007' as datetime) as transdate, 4 as qty, 1.85 as sPrice union all
	select 1 as stkid, cast('1/7/2007' as datetime) as transdate, 2 as qty,  3 as sPrice union all
	select 1 as stkid, cast('1/10/2007' as datetime) as transdate, 1 as qty, 3 as sPrice 
	) as result

What i want is to get the effectivity price of the transaction base on its transaction date.

Any help will be greatly appriciated.

Want Philippines to become 1st World COuntry? Go for World War 3...

Koji Matsumura
Posting Yak Master

141 Posts

Posted - 2007-05-30 : 21:40:57

DECLARE @Trans TABLE (stkid int, transdate datetime, qty int)

INSERT INTO @Trans
SELECT 1 as stkid, cast('1/1/2007' as datetime) as transdate, 1 as qty union all
SELECT 1 as stkid, cast('1/2/2007' as datetime) as transdate, 6 as qty union all
SELECT 1 as stkid, cast('1/3/2007' as datetime) as transdate, 4 as qty union all
SELECT 1 as stkid, cast('1/7/2007' as datetime) as transdate, 2 as qty union all
SELECT 1 as stkid, cast('1/10/2007' as datetime) as transdate, 1 as qty

DECLARE @pricinghistory TABLE (stkid int, sPrice numeric(5,2), effectivity datetime)

INSERT INTO @pricinghistory
SELECT 1 as stkid, 1.2 as sPrice, cast('12/1/2006' as datetime) as effectivity union all
SELECT 1 as stkid, 1.85 as sPrice, cast('1/3/2007' as datetime) as effectivity union all
SELECT 1 as stkid, 3 as sPrice, cast('1/5/2007' as datetime) as effectivity

SELECT A.stkid, A.transdate, A.qty
,sPrice =
(
SELECT sPrice Z
FROM @pricinghistory Z
WHERE Z.stkid = A.stkid AND Z.effectivity =
(
SELECT MAX(Y.effectivity)
FROM @pricinghistory Y
WHERE Y.stkid = A.stkid AND Y.effectivity <= A.transdate
)
)
FROM @trans A

K. Matsumura

jonasalbert20
Constraint Violating Yak Guru

300 Posts

Posted - 2007-05-30 : 21:48:34

tnx...

Can this be done without using a subquery? For sure, this will be very slow if i want to validate a 1,000+ items on 20,000+ transactions.

Want Philippines to become 1st World COuntry? Go for World War 3...

Koji Matsumura
Posting Yak Master

141 Posts

Posted - 2007-05-30 : 22:32:07

Not sure if this is faster.

SELECT A.stkid, A.transdate, A.qty, C.sPrice
FROM @trans A
INNER JOIN
(
SELECT A.stkid, A.transdate, effectivity = MAX(B.effectivity)
FROM @trans A
INNER JOIN @pricinghistory B ON B.stkid = A.stkid AND B.effectivity <= A.transdate
GROUP BY A.stkid, A.transdate
) B ON B.stkid = A.stkid AND B.transdate = A.transdate
INNER JOIN @pricinghistory C ON C.stkid = A.stkid AND C.effectivity = B.effectivity

K. Matsumura

PeterNeo
Constraint Violating Yak Guru

357 Posts

Posted - 2007-05-31 : 00:45:16

Hi,
check the Execution timings for each qry and choose the one which is faster.

SELECT T.stkid, T.transdate, T.qty, PH.sPrice
FROM @trans T
INNER JOIN @pricinghistory PH ON T.stkid = PH.stkid
WHERE PH.effectivity = ( SELECT MAX(effectivity) FROM @pricinghistory WHERE stkid = T.stkid AND effectivity <= T.transdate )

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources