How do I create this? - 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)

How do I create this?

Author

Topic

Kapital123
Starting Member

31 Posts

Posted - 2012-12-11 : 05:44:53

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

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2012-12-11 : 09:07:56

[code]
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[/code]

Kapital123
Starting Member

31 Posts

Posted - 2012-12-11 : 16:23:44

Thankyou Sir I will try this.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources