SQL Server Forums
Profile | Register | 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?		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Kapital123
Starting Member

14 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
7173 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

14 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  
 New 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.05 seconds. Powered By: Snitz Forums 2000