Combine Data from 2 tables. - 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

SQL Server Development (2000)

Combine Data from 2 tables.

Author

Topic

cvipin
Yak Posting Veteran

51 Posts

Posted - 2008-07-01 : 10:50:02

I have following data. I want to get the output I mentioned below without using subquery in the select clause.
ex. I don't want like this
Select AcctgDate, Currhold#,(Select Top 1 Cost From @OrigCost) etc etc

How can I do that?

Declare @OrigCost Table (currhold# int, TradeDate datetime, Cost decimal(15,2))

INSERT INTO @OrigCost VALUES (3271, '3/29/2007', 10000)
INSERT INTO @OrigCost VALUES (3271, '4/3/2007', 9000)
INSERT INTO @OrigCost VALUES (3271, '4/5/2007', 8000)
INSERT INTO @OrigCost VALUES (3271, '6/25/2007', 7000)
INSERT INTO @OrigCost VALUES (3271, '7/25/2007', 6000)
INSERT INTO @OrigCost VALUES (3271, '8/27/2007', 5000)
INSERT INTO @OrigCost VALUES (3271, '9/25/2007', 4000)
INSERT INTO @OrigCost VALUES (3272, '4/1/2007', 9500)
INSERT INTO @OrigCost VALUES (3272, '4/4/2007', 9200)
INSERT INTO @OrigCost VALUES (3272, '4/6/2007', 8900)
INSERT INTO @OrigCost VALUES (3272, '6/30/2007', 8750)
INSERT INTO @OrigCost VALUES (3272, '7/3/2007', 8700)

Declare @History Table (currhold# int, AcctgDate datetime)

INSERT INTO @History VALUES (3271, '3/29/2007')
INSERT INTO @History VALUES (3271, '3/30/2007')
INSERT INTO @History VALUES (3271, '3/31/2007')
INSERT INTO @History VALUES (3271, '4/1/2007')
INSERT INTO @History VALUES (3271, '4/2/2007')
INSERT INTO @History VALUES (3271, '4/3/2007')
INSERT INTO @History VALUES (3271, '4/4/2007')
INSERT INTO @History VALUES (3271, '4/5/2007')
INSERT INTO @History VALUES (3271, '4/6/2007')
INSERT INTO @History VALUES (3272, '4/1/2007')
INSERT INTO @History VALUES (3272, '4/2/2007')
INSERT INTO @History VALUES (3272, '4/3/2007')
INSERT INTO @History VALUES (3272, '4/4/2007')
INSERT INTO @History VALUES (3272, '4/5/2007')
INSERT INTO @History VALUES (3272, '4/6/2007')
INSERT INTO @History VALUES (3272, '4/7/2007')

select * from @OrigCost
select * from @History

---- Output Expected ----

currhold# AcctgDate Cost
3271 3/29/2007 10000
3271 3/30/2007 10000
3271 3/31/2007 10000
3271 4/1/2007 10000
3271 4/2/2007 10000
3271 4/3/2007 9000
3271 4/4/2007 9000
3271 4/5/2007 8000
3271 4/6/2007 8000
3272 4/1/2007 9500
3272 4/2/2007 9500
3272 4/3/2007 9500
3272 4/4/2007 9200
3272 4/5/2007 9200
3272 4/6/2007 8900
3272 4/7/2007 8900

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-07-01 : 11:42:16

[code]
SELECT a.currhold#, a.AcctgDate, c.Cost
FROM (
SELECT h.currhold#, h.AcctgDate, TradeDate = MAX(c.TradeDate)
FROM @History h
INNER JOIN @OrigCost c ON h.currhold# = c.currhold#
AND h.AcctgDate >= c.TradeDate
GROUP BY h.currhold#, h.AcctgDate
) a
INNER JOIN @OrigCost c ON a.currhold# = c.currhold#
AND a.TradeDate = c.TradeDate[/code]

KH
[spoiler]Time is always against us[/spoiler]

cvipin
Yak Posting Veteran

51 Posts

Posted - 2008-07-01 : 14:31:45

Works perfect..
Thanks KH

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources