t+1 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 2008 Forums

Transact-SQL (2008)

t+1 query

Author

Topic

gwguy
Starting Member

2 Posts

Posted - 2012-05-03 : 09:00:10

Hey,

I am trying to grab data from two tables.

1. Grab data from first table which would look like this

Date Item Qty
01/24 Table 10
02/28 Chair 5
03/01 Table 4

2. Now I want to grab the associated price from a different table. But the catch is that I want t+1 date's price.

So in first row I would want price for 01/25 or the next business day which ever is the least. Again for 02/28 I would want the price of 5 chairs as of 02/29.

Can someone help me with this?

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-05-03 : 09:09:11

Something like this:

SELECT
	a.*,
	b.Price
FROM
	Table1 a
	OUTER APPLY
	(
		SELECT TOP 1 b.Price
		FROM Table2 b
		WHERE b.Date > a.DATE
		AND b.Item = a.Item
		ORDER BY b.DATE
	)b

gwguy
Starting Member

2 Posts

Posted - 2012-05-03 : 09:13:17

Sorry but I use Oracle and I think outer apply is not supported.

Apologies if I am in the wrong forum.

quote:

Originally posted by sunitabeck

Something like this:

SELECT
	a.*,
	b.Price
FROM
	Table1 a
	OUTER APPLY
	(
		SELECT TOP 1 b.Price
		FROM Table2 b
		WHERE b.Date > a.DATE
		AND b.Item = a.Item
		ORDER BY b.DATE
	)b

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-05-03 : 09:14:59

This forum is for Microsoft SQL Server. You would probably get faster and more accurate answers at an Oracle forum or general database forum such as dbforums.com

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources