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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 How do I create this?
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Kapital123
Starting Member

31 Posts

Posted - 12/11/2012 :  05:44:53  Show Profile  Reply with Quote
Hello,

I am not sure what the most efficient way to do this is, hence why I have decided to turn to the forum for ideas and advice. Here is Sample Table 1 (4 Columns: Date, Fund, Security and Actual_Weight):

Date Fund Security Actual_Weight
1/01/2012 FundA Cash 0.2
1/01/2012 FundA MBS 0.8
1/01/2012 FundB Cash 0.3
1/01/2012 FundB MBS 0.7
2/01/2012 FundA Cash 0.1
2/01/2012 FundA MBS 0.9
2/01/2012 FundB Cash 0.2
2/01/2012 FundB MBS 0.8
3/01/2012 FundA Cash 0.7
3/01/2012 FundA MBS 0.3
3/01/2012 FundB Cash 0.5
3/01/2012 FundB MBS 0.5


Here is Sample Table 2 (4 Columns: Date, Fund, Security and Target_Weight)

Date Fund Security Target_Weight
1/01/2012 FundA Cash 0.5
1/01/2012 FundA MBS 0.5
1/01/2012 FundB Cash 0.7
1/01/2012 FundB MBS 0.3
3/01/2012 FundA Cash 0.6
3/01/2012 FundA MBS 0.4
3/01/2012 FundB Cash 0.1
3/01/2012 FundB MBS 0.9

Now what I would like to generate through a SELECT statement is this Table (5 Columns) from Table 1 and Table 2 above:

Date Fund Security Actual_Weight Target_Weight
1/01/2012 FundA Cash 0.2 0.5
1/01/2012 FundA MBS 0.8 0.5
1/01/2012 FundB Cash 0.3 0.7
1/01/2012 FundB MBS 0.7 0.3
2/01/2012 FundA Cash 0.1 0.5
2/01/2012 FundA MBS 0.9 0.5
2/01/2012 FundB Cash 0.2 0.7
2/01/2012 FundB MBS 0.8 0.3
3/01/2012 FundA Cash 0.7 0.6
3/01/2012 FundA MBS 0.3 0.4
3/01/2012 FundB Cash 0.5 0.1
3/01/2012 FundB MBS 0.5 0.9

You could do this via simple LEFT JOIN I believe, however there is a little twist with the Date field. You see, in Table 2 I am missing the 02/01/2012, however I would like the Target_Weights on 01/01/2012 to fill that void up until the Date where new target weights have been established.

Originally I thought this approach would work:

Select *,
(Select Top 1 a.Target_Weight
from Table2 a
where a.fund=b.fund
and a.security=b.security
and a.date <= b.date
order a.date asc) as TARGET
from Table1 b

Any Ideas? Bear in mind, I am trying to make the code as efficient as possible

Edited by - Kapital123 on 12/11/2012 05:47:00

sodeep
Flowing Fount of Yak Knowledge

USA
7174 Posts

Posted - 12/11/2012 :  09:07:56  Show Profile  Reply with Quote

Declare @Table1 table
(Date Datetime,Fund Varchar(10),Security Varchar(10),Actual_Weight float)

Insert into @Table1

Select '1/01/2012','FundA' ,'Cash',0.2 union all
Select '1/01/2012' ,'FundA' ,'MBS' ,0.8  union all
Select '1/01/2012' ,'FundB' ,'Cash' ,0.3 union all
Select '1/01/2012' ,'FundB' ,'MBS' ,0.7 union all
Select '2/01/2012' ,'FundA' ,'Cash' ,0.1 union all
Select '2/01/2012' ,'FundA' ,'MBS' ,0.9 union all
Select '2/01/2012' ,'FundB' ,'Cash' ,0.2 union all
Select '2/01/2012' ,'FundB' ,'MBS' ,0.8 union all
Select '3/01/2012' ,'FundA' ,'Cash' ,0.7 union all
Select '3/01/2012' ,'FundA' ,'MBS' ,0.3 union all
Select '3/01/2012' ,'FundB' ,'Cash' ,0.5 union all
Select '3/01/2012' ,'FundB' ,'MBS' ,0.5

Declare @Table2 table
(Date Datetime,Fund Varchar(10),Security Varchar(10),Target_Weight float)

Insert into @Table2

Select '1/01/2012','FundA' ,'Cash',0.5 union all
Select '1/01/2012' ,'FundA' ,'MBS' ,0.5 union all
Select '1/01/2012' ,'FundB' ,'Cash' ,0.7 union all
Select '1/01/2012' ,'FundB' ,'MBS' ,0.3 union all
Select '3/01/2012' ,'FundA' ,'Cash' ,0.6 union all
Select '3/01/2012' ,'FundA' ,'MBS' ,0.4 union all
Select '3/01/2012' ,'FundB' ,'Cash' ,0.1 union all
Select '3/01/2012' ,'FundB' ,'MBS' ,0.9


Select T1.Date,T1.Fund,T1.Security,T1.Actual_Weight,T2.Target_Weight
from @Table1 T1
OUTER APPLY
( 
Select Top 1 a.Target_Weight
from @Table2 a
where a.fund = T1.fund 
and a.security = T1.security
and a.date  <= T1.date
order by a.date desc 
)T2

Date	Fund	Security	Actual_Weight	Target_Weight
2012-01-01 00:00:00.000	FundA	Cash	0.2	0.5
2012-01-01 00:00:00.000	FundA	MBS	0.8	0.5
2012-01-01 00:00:00.000	FundB	Cash	0.3	0.7
2012-01-01 00:00:00.000	FundB	MBS	0.7	0.3
2012-02-01 00:00:00.000	FundA	Cash	0.1	0.5
2012-02-01 00:00:00.000	FundA	MBS	0.9	0.5
2012-02-01 00:00:00.000	FundB	Cash	0.2	0.7
2012-02-01 00:00:00.000	FundB	MBS	0.8	0.3
2012-03-01 00:00:00.000	FundA	Cash	0.7	0.6
2012-03-01 00:00:00.000	FundA	MBS	0.3	0.4
2012-03-01 00:00:00.000	FundB	Cash	0.5	0.1
2012-03-01 00:00:00.000	FundB	MBS	0.5	0.9
Go to Top of Page

Kapital123
Starting Member

31 Posts

Posted - 12/11/2012 :  16:23:44  Show Profile  Reply with Quote
Thankyou Sir I will try this.
Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.02 seconds. Powered By: Snitz Forums 2000