Calculate Return Using Quirky Update

Question

Post reply

Calculate Return Using Quirky Update

vijay.s

SSCrazy

Points: 2161
More actions
June 14, 2010 at 6:27 am

#222656

Dear All,
I got a task to calculate blanded benchmark value, Based on return calculated for client.
I have calcumated return and store the same into "Ret" column, the same is done by me already.
Now i want to calculate benchmark value based on those return.
I have to benchmark value in my table for those where is flow is "Y".
Now i Want to Calculate benchmark return based on below mentioned formula.
Portfolio = [(Portfolio for the previous flow (Where IsFlow is "Y")) * (Return)]
If any flow comes between the month then till flow date benchmark
is calculated whit portfolio value present for 1st for every month.
If flow is not present between one or more then one month then
portfolio for whole month should be calculated using portfolio present for
1st date of month to till end of month
Sample script is attached
I already calculated benchmark value using Quirky update but i will take take exact previous value insted of 1st or flow date.
--===== Declare the working variables
DECLARE @ClientCode INT,
@cumReturn NUMERIC(18,9) ,
@ast_cls_cd varchar(25),
@TplId int,
@blnd_bnmk_dt smalldatetime
SET @ClientCode=0
SET @cumReturn=0
SET @TplId = 0
--===== Calculating Comuting multiplication
UPDATE blnd_bnmk
SET @cumReturn = blnd_bnmk_val = CASE
WHEN clnt_id = @ClientCode
AND ast_cls_cd = @ast_cls_cd
AND tpl_id = @TplId
AND blnd_bnmk_val IS NULL
THEN @cumReturn * Ret
WHEN clnt_id <> @ClientCode
OR ast_cls_cd <> @ast_cls_cd
OR tpl_id <> @TplId
OR blnd_bnmk_val IS NOT NULL
THEN blnd_bnmk_val
END,
@ClientCode = clnt_id,
@ast_cls_cd = ast_cls_cd,
@TplId = tpl_id
FROM blnd_bnmk WITH(TABLOCKX)
OPTION (MAXDOP 1)
let me know if question is not clear...
hoping for kind reply..

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

vijay.s SSCrazy Points: 2161 More actions · Answer 1

Another and Small Set of Sample script is mention below

-- DROP TABLE #blnd_bnmk

CREATE TABLE #blnd_bnmk

(

clnt_id int,

ast_cls_cd varchar(25),

[Date] DATETIME,

[IndexValue] NUMERIC(18,2),

[Return] NUMERIC(18,4),

Portfolio NUMERIC(18,4),

IsFlow CHAR(1)

)

CREATE CLUSTERED INDEX IX_#blnd_bnmk ON #blnd_bnmk (clnt_id, ast_cls_cd, Date)

-- delete from #blnd_bnmk

--- Portfolio = blnd_bnmk_val

INSERT INTO #blnd_bnmk (clnt_id, ast_cls_cd, IsFlow,[Date],IndexValue,[Return],Portfolio)

SELECT 1, 'EQ', 'N', '30-Apr-2010',4000,0.5,50

UNION

SELECT 1, 'EQ', 'N', '1-May-2010',4050,1.0125,NULL

UNION

SELECT 1, 'EQ', 'N', '2-May-2010',5000,1.23456790123457,NULL

UNION

SELECT 1, 'EQ', 'N', '3-May-2010',5500,1.35802469135802,NULL

UNION

SELECT 1, 'EQ', 'N', '4-May-2010',4000,0.987654320987654,NULL

UNION

SELECT 1, 'EQ', 'N', '5-May-2010',4500,1.11111111111111,NULL

UNION

SELECT 1, 'EQ', 'Y', '6-May-2010',8000,1.97530864197531,NULL

UNION

SELECT 1, 'EQ', 'N', '7-May-2010',7000,0.875,NULL

UNION

SELECT 1, 'EQ', 'N', '8-May-2010',5000,0.625,NULL

UNION

SELECT 1, 'EQ', 'N', '9-May-2010',4000,0.5,NULL

UNION

SELECT 1, 'EQ', 'N', '10-May-2010',4000,0.5,NULL

UNION

SELECT 1, 'EQ', 'Y', '11-May-2010',4050,0.50625,NULL

UNION

SELECT 1, 'EQ', 'N', '12-May-2010',5000,1.23456790123457,NULL

UNION

SELECT 1, 'EQ', 'N', '13-May-2010',5500,1.35802469135802,NULL

UNION

SELECT 1, 'EQ', 'N', '14-May-2010',4000,0.987654320987654,NULL

SELECT * FROM #blnd_bnmk

--===== Declare the working variables

DECLARE @ClientCode INT,

@ast_cls_cd varchar(25),

@cumReturn NUMERIC(18,4)

SET @ClientCode=0

SET @cumReturn=0

--===== Calculating Comuting multiplication

UPDATE #blnd_bnmk

SET @cumReturn = Portfolio = CASE

WHEN clnt_id = @ClientCode

AND ast_cls_cd = @ast_cls_cd

AND Portfolio IS NULL

THEN @cumReturn * [Return]

WHEN clnt_id <> @ClientCode

OR ast_cls_cd <> @ast_cls_cd

OR Portfolio IS NOT NULL

THEN Portfolio

END,

@ClientCode = clnt_id,

@ast_cls_cd = ast_cls_cd

FROM #blnd_bnmk WITH(TABLOCKX)

OPTION (MAXDOP 1)

Below is the expected output for problem, It may be help you to understand the problem.

--------------------------------------------------------

IsFlow DateIndex Value Return Portfolio 1

--------------------------------------------------------

N 30-Apr-20104000 0.5 50

N 1-May-20104050 1.012550.625

N 2-May-20105000 1.23456790162.5

N 3-May-20105500 1.35802469168.75

N 4-May-20104000 0.98765432150

N 5-May-20104500 1.11111111156.25

Y 6-May-20108000 1.975308642100

N 7-May-20107000 0.87587.5

N 8-May-20105000 0.62562.5

N 9-May-20104000 0.5 50

N 10-May-20104000 0.5 50

Y 11-May-20104050 0.5062550.625

N 12-May-20105000 1.23456790162.5

N 13-May-20105500 1.35802469168.75

N 14-May-20104000 0.98765432150

vijay.s SSCrazy Points: 2161 More actions · Answer 2

Only problem in this update is, It take just previous date portfolio portfolio and current date return to retrieve current date portfolio.

I just need to update current date portfolio using 1st day of month portfolio multiply by current day of return.

Kindly help me to solve this query....

vijay.s SSCrazy Points: 2161 More actions · Answer 3

Is there any additional information required for this query as provide actual data (attached) and sample data???

Any ideas or suggestions will be highly appreciated!

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 4

I just saw this, Vijay. It's almost 4 in the morning and I have to be at work in a couple of hours. I can't get to this for another 18 or 19 hours so, hopefully, someone will beat me to it.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

vijay.s SSCrazy Points: 2161 More actions · Answer 5

I used below mentioned approch to solve this problem... Is this approch is right.

Output and solution attached for my both (Same Data And Actual Data) problem.

-- DROP TABLE #blnd_bnmk

CREATE TABLE #blnd_bnmk

(

clnt_id int,

ast_cls_cd varchar(25),

[Date] DATETIME,

[IndexValue] NUMERIC(18,2),

[Return] NUMERIC(18,4),

Portfolio NUMERIC(18,4),

IsFlow CHAR(1)

)

CREATE CLUSTERED INDEX IX_#blnd_bnmk ON #blnd_bnmk (clnt_id, ast_cls_cd, Date)

-- delete from #blnd_bnmk

--- Portfolio = blnd_bnmk_val

INSERT INTO #blnd_bnmk (clnt_id, ast_cls_cd, IsFlow,[Date],IndexValue,[Return],Portfolio)

SELECT 1, 'EQ', 'Y', '30-Apr-2010',4000,0.5,50

UNION

SELECT 1, 'EQ', 'N', '1-May-2010',4050,1.0125,NULL

UNION

SELECT 1, 'EQ', 'N', '2-May-2010',5000,1.23456790123457,NULL

UNION

SELECT 1, 'EQ', 'N', '3-May-2010',5500,1.35802469135802,NULL

UNION

SELECT 1, 'EQ', 'N', '4-May-2010',4000,0.987654320987654,NULL

UNION

SELECT 1, 'EQ', 'N', '5-May-2010',4500,1.11111111111111,NULL

UNION

SELECT 1, 'EQ', 'Y', '6-May-2010',8000,1.97530864197531,NULL

UNION

SELECT 1, 'EQ', 'N', '7-May-2010',7000,0.875,NULL

UNION

SELECT 1, 'EQ', 'N', '8-May-2010',5000,0.625,NULL

UNION

SELECT 1, 'EQ', 'N', '9-May-2010',4000,0.5,NULL

UNION

SELECT 1, 'EQ', 'N', '10-May-2010',4000,0.5,NULL

UNION

SELECT 1, 'EQ', 'Y', '11-May-2010',4050,0.50625,NULL

UNION

SELECT 1, 'EQ', 'N', '12-May-2010',5000,1.23456790123457,NULL

UNION

SELECT 1, 'EQ', 'N', '13-May-2010',5500,1.35802469135802,NULL

UNION

SELECT 1, 'EQ', 'N', '14-May-2010',4000,0.987654320987654,NULL

SELECT * FROM #blnd_bnmk

WHERE IsFlow = 'Y' OR DAY(Date)=1

--===== Declare the working variables

DECLARE @ClientCode INT,

@cumReturn NUMERIC(18,4) ,

@ast_cls_cd varchar(25)

--===== Calculating Comuting multiplication

UPDATE #blnd_bnmk

SET @cumReturn = Portfolio = CASE

WHEN clnt_id = @ClientCode

AND ast_cls_cd = @ast_cls_cd

AND Portfolio IS NULL

THEN @cumReturn * [Return]

WHEN clnt_id <> @ClientCode

OR ast_cls_cd <> @ast_cls_cd

OR Portfolio IS NOT NULL

THEN Portfolio

END,

@ClientCode = clnt_id,

@ast_cls_cd = ast_cls_cd

FROM #blnd_bnmk WITH(TABLOCKX)

WHERE IsFlow = 'Y' OR DAY(Date)= 1

OPTION (MAXDOP 1)

--===== Declare some necessary working variables for update temp table

DECLARE @PrevCLN INT,

@PrevAST VARCHAR(25),

@PrevVal NUMERIC(18,4)

--===== "Quirky" Update for update the null value with previous amount value in temp table

UPDATE #blnd_bnmk

SET @PrevVal = Portfolio = CASE

WHEN clnt_id = @PrevCLN

AND ast_cls_cd = @PrevAST

AND Portfolio IS NULL

THEN @PrevVal

WHEN clnt_id <> @PrevCLN

OR ast_cls_cd <> @PrevAST

OR Portfolio IS NOT NULL

THEN Portfolio

END,

@PrevCLN = clnt_id,

@PrevAST = ast_cls_cd

FROM #blnd_bnmk WITH(TABLOCKX)

OPTION (MAXDOP 1)

SELECT * FROM

(

select *, [Return] * Portfolio Value

from #blnd_bnmk

WHERE IsFlow = 'N' AND DAY(Date) <> 1

UNION

select *, Portfolio

from #blnd_bnmk

WHERE IsFlow = 'Y' OR DAY(Date) = 1

) a

ORDER by DATE

--======== Test On Actual Data

SELECT * FROM blnd_bnmk

WHERE ( IsFlow = 'Y' OR DAY(blnd_bnmk_dt)= 1 )

--===== Declare the working variables

DECLARE @clnt_id INT,

@tpl_id INT,

@ast_cls_cd VARCHAR(25),

@cumReturn NUMERIC(18,4)

--===== Calculating Comuting multiplication

UPDATE blnd_bnmk

SET @cumReturn = blnd_bnmk_val = CASE

WHEN clnt_id = @clnt_id

AND tpl_id = @tpl_id

AND ast_cls_cd = @ast_cls_cd

AND blnd_bnmk_val IS NULL

THEN @cumReturn * [Ret]

WHEN clnt_id <> @clnt_id

OR tpl_id = @tpl_id

OR ast_cls_cd <> @ast_cls_cd

OR blnd_bnmk_val IS NOT NULL

THEN blnd_bnmk_val

END,

@clnt_id = clnt_id,

@tpl_id = tpl_id,

@ast_cls_cd = ast_cls_cd

FROM blnd_bnmk WITH(TABLOCKX)

WHERE (IsFlow = 'Y' OR DAY(blnd_bnmk_dt)= 1)

OPTION (MAXDOP 1)

--===== Declare some necessary working variables for update temp table

DECLARE @PrevCLN INT,

@PrevAST VARCHAR(25),

@tpl_id INT,

@PrevVal NUMERIC(18,4)

--===== "Quirky" Update for update the null value with previous blnd_bnmk_val value in temp table

UPDATE blnd_bnmk

SET @PrevVal = blnd_bnmk_val = CASE

WHEN clnt_id = @PrevCLN

AND tpl_id = @tpl_id

AND ast_cls_cd = @PrevAST

AND blnd_bnmk_val IS NULL

THEN @PrevVal

WHEN clnt_id <> @PrevCLN

OR tpl_id = @tpl_id

OR ast_cls_cd <> @PrevAST

OR blnd_bnmk_val IS NOT NULL

THEN blnd_bnmk_val

END,

@PrevCLN = clnt_id,

@tpl_id = tpl_id,

@PrevAST = ast_cls_cd

FROM blnd_bnmk WITH(TABLOCKX)

OPTION (MAXDOP 1)

SELECT Ret, blnd_bnmk_val, bnmk_val, blnd_bnmk_dt, tpl_id, ast_cls_cd, std_per, bnmk_cd, clnt_id, wef_dt, bnmk_rt, no_flow_bnmk_rt,

IsFlow

FROM

(

select *, [Ret] * blnd_bnmk_val bnmk_val

from blnd_bnmk

WHERE IsFlow = 'N' AND DAY(blnd_bnmk_dt) <> 1

UNION

select *, blnd_bnmk_val

from blnd_bnmk

WHERE IsFlow = 'Y' OR DAY(blnd_bnmk_dt) = 1

) blnd

ORDER by clnt_id, tpl_id, ast_cls_cd, blnd_bnmk_dt

vijay.s SSCrazy Points: 2161 More actions · Answer 6

I thought I am right, above mention is a feasible Solution.

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 7

Sorry... I've been sick and haven't touched a computer in almost 2 days.

Lemme ask... does your latest couple of posts mean that you're all set or do you still need some help?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

vijay.s SSCrazy Points: 2161 More actions · Answer 8

Thankx Jeff Sir, For your replay,

how r u and how is your health???

Actually i got the solution of this problem but stuck again with this problem as a change has been come. Due to that changes my whole logic is change.

Now I am trying to achieve my result which this changes...

I am given a sample script and suggest solution for you.

create table #blnd_bnmk

(

blnd_bnmk_dt datetime,

tpl_id numeric(18,0),

ast_cls_cd varchar(25),

clnt_id numeric(18,0),

ret numeric(18,9),

blnd_bnmk_valnumeric(18,9),

IsFlowCHAR(1) default('N')

)

INSERT INTO #blnd_bnmk (clnt_id, tpl_id, ast_cls_cd, blnd_bnmk_dt, ret, blnd_bnmk_val, IsFlow)

SELECT '242','82','Debt','Sep 30 2009 12:00AM','1.000000000','99.00','Y' UNION ALL

SELECT '242','82','Debt','Oct 1 2009 12:00AM','0.999980566',NULL,'N' UNION ALL

SELECT '242','82','Debt','Oct 2 2009 12:00AM','1.000148996',NULL,'N' UNION ALL

SELECT '242','82','Debt','Oct 3 2009 12:00AM','1.000304469',NULL,'N' UNION ALL

SELECT '242','82','Debt','Oct 4 2009 12:00AM','1.000459943',NULL,'N' UNION ALL

SELECT '242','82','Debt','Oct 5 2009 12:00AM','1.000764412',NULL,'N' UNION ALL

SELECT '242','82','Debt','Oct 6 2009 12:00AM','1.000550636',NULL,'N' UNION ALL

SELECT '242','82','Debt','Oct 7 2009 12:00AM','1.000181386',NULL,'N' UNION ALL

SELECT '242','82','Debt','Oct 8 2009 12:00AM','0.999779746',NULL,'N' UNION ALL

SELECT '242','82','Debt','Oct 9 2009 12:00AM','0.999138417',NULL,'N' UNION ALL

SELECT '242','82','Debt','Oct 10 2009 12:00AM','0.999293891',NULL,'N' UNION ALL

SELECT '242','82','Debt','Oct 11 2009 12:00AM','0.999449364',NULL,'N' UNION ALL

SELECT '242','82','Debt','Oct 12 2009 12:00AM','0.998406395',NULL,'N' UNION ALL

SELECT '242','82','Debt','Oct 13 2009 12:00AM','0.998568347',NULL,'N' UNION ALL

SELECT '242','82','Debt','Oct 14 2009 12:00AM','0.998050101',NULL,'N' UNION ALL

SELECT '242','82','Debt','Oct 15 2009 12:00AM','0.997110781',NULL,'N' UNION ALL

SELECT '242','82','Debt','Oct 16 2009 12:00AM','0.996767444',NULL,'N' UNION ALL

SELECT '242','82','Debt','Oct 17 2009 12:00AM','0.996916439',NULL,'N' UNION ALL

SELECT '242','82','Debt','Oct 18 2009 12:00AM','0.997065435',NULL,'N' UNION ALL

SELECT '242','82','Debt','Oct 19 2009 12:00AM','0.997214431',NULL,'N' UNION ALL

SELECT '242','82','Debt','Oct 20 2009 12:00AM','0.999404018',NULL,'N' UNION ALL

SELECT '242','82','Debt','Oct 21 2009 12:00AM','1.000589504',NULL,'N' UNION ALL

SELECT '242','82','Debt','Oct 22 2009 12:00AM','1.000680197',NULL,'N' UNION ALL

SELECT '242','82','Debt','Oct 23 2009 12:00AM','1.000829193',NULL,'N' UNION ALL

SELECT '242','82','Debt','Oct 24 2009 12:00AM','1.000971710',NULL,'N' UNION ALL

SELECT '242','82','Debt','Oct 25 2009 12:00AM','1.001114228',NULL,'N' UNION ALL

SELECT '242','82','Debt','Oct 26 2009 12:00AM','1.001224355',NULL,'N' UNION ALL

SELECT '242','82','Debt','Oct 27 2009 12:00AM','1.002131285',NULL,'N' UNION ALL

SELECT '242','82','Debt','Oct 28 2009 12:00AM','1.001820337',NULL,'N' UNION ALL

SELECT '242','82','Debt','Oct 29 2009 12:00AM','1.002921609',NULL,'N' UNION ALL

SELECT '242','82','Debt','Oct 30 2009 12:00AM','1.002902175',NULL,'N' UNION ALL

SELECT '242','82','Debt','Oct 31 2009 12:00AM','1.002986390',NULL,'N' UNION ALL

SELECT '242','82','Debt','Nov 1 2009 12:00AM','1.000142093',NULL,'N' UNION ALL

SELECT '242','82','Debt','Nov 2 2009 12:00AM','1.000148531',NULL,'N' UNION ALL

SELECT '242','82','Debt','Nov 3 2009 12:00AM','1.000820149',NULL,'N' UNION ALL

SELECT '242','82','Debt','Nov 4 2009 12:00AM','1.001782370',NULL,'N' UNION ALL

SELECT '242','82','Debt','Nov 5 2009 12:00AM','1.002628350',NULL,'N' UNION ALL

SELECT '242','82','Debt','Nov 6 2009 12:00AM','1.002957701',NULL,'N' UNION ALL

SELECT '242','82','Debt','Nov 7 2009 12:00AM','1.003093316',NULL,'N' UNION ALL

SELECT '242','82','Debt','Nov 8 2009 12:00AM','1.003228931',NULL,'N' UNION ALL

SELECT '242','82','Debt','Nov 9 2009 12:00AM','1.004953180',NULL,'N' UNION ALL

SELECT '242','82','Debt','Nov 10 2009 12:00AM','1.005134001',NULL,'N' UNION ALL

SELECT '242','82','Debt','Nov 11 2009 12:00AM','1.005515015',NULL,'N' UNION ALL

SELECT '242','82','Debt','Nov 12 2009 12:00AM','1.005159832',NULL,'N' UNION ALL

SELECT '242','82','Debt','Nov 13 2009 12:00AM','1.005127543',NULL,'N' UNION ALL

SELECT '242','82','Debt','Nov 14 2009 12:00AM','1.005127543',NULL,'N' UNION ALL

SELECT '242','82','Debt','Nov 15 2009 12:00AM','1.005127543',NULL,'N' UNION ALL

SELECT '242','82','Debt','Nov 16 2009 12:00AM','1.006231837',NULL,'N' UNION ALL

SELECT '242','82','Debt','Nov 17 2009 12:00AM','1.007516952',NULL,'N' UNION ALL

SELECT '242','82','Debt','Nov 18 2009 12:00AM','1.008414595',NULL,'N' UNION ALL

SELECT '242','82','Debt','Nov 19 2009 12:00AM','1.009331611',NULL,'N' UNION ALL

SELECT '242','82','Debt','Nov 20 2009 12:00AM','1.010351954',NULL,'N' UNION ALL

SELECT '242','82','Debt','Nov 21 2009 12:00AM','1.010487569',NULL,'N' UNION ALL

SELECT '242','82','Debt','Nov 22 2009 12:00AM','1.010616726',NULL,'N' UNION ALL

SELECT '242','82','Debt','Nov 23 2009 12:00AM','1.010565063',NULL,'N' UNION ALL

SELECT '242','82','Debt','Nov 24 2009 12:00AM','1.011017113',NULL,'N' UNION ALL

SELECT '242','82','Debt','Nov 25 2009 12:00AM','1.012257023',NULL,'Y' UNION ALL

SELECT '242','82','Debt','Nov 26 2009 12:00AM','1.001007987',NULL,'N' UNION ALL

SELECT '242','82','Debt','Nov 27 2009 12:00AM','1.000925052',NULL,'N' UNION ALL

SELECT '242','82','Debt','Nov 28 2009 12:00AM','1.000925052',NULL,'N' UNION ALL

SELECT '242','82','Debt','Nov 29 2009 12:00AM','1.000925052',NULL,'N' UNION ALL

SELECT '242','82','Debt','Nov 30 2009 12:00AM','1.001199377',NULL,'N' UNION ALL

SELECT '242','82','Debt','Dec 1 2009 12:00AM','0.997750675',NULL,'N' UNION ALL

SELECT '242','82','Debt','Dec 2 2009 12:00AM','0.999323043',NULL,'N' UNION ALL

SELECT '242','82','Debt','Dec 3 2009 12:00AM','0.999367747',NULL,'N' UNION ALL

SELECT '242','82','Debt','Dec 4 2009 12:00AM','0.999048428',NULL,'N' UNION ALL

SELECT '242','82','Debt','Dec 5 2009 12:00AM','0.999182542',NULL,'N' UNION ALL

SELECT '242','82','Debt','Dec 6 2009 12:00AM','0.999316656',NULL,'N' UNION ALL

SELECT '242','82','Debt','Dec 7 2009 12:00AM','0.997898878',NULL,'N' UNION ALL

SELECT '242','82','Debt','Dec 8 2009 12:00AM','0.999112292',NULL,'N' UNION ALL

SELECT '242','82','Debt','Dec 9 2009 12:00AM','0.999137837',NULL,'N' UNION ALL

SELECT '242','82','Debt','Dec 10 2009 12:00AM','0.998946246',NULL,'N' UNION ALL

SELECT '242','82','Debt','Dec 11 2009 12:00AM','0.999489089',NULL,'N' UNION ALL

SELECT '242','82','Debt','Dec 12 2009 12:00AM','0.999489089',NULL,'N' UNION ALL

SELECT '242','82','Debt','Dec 13 2009 12:00AM','0.999489089',NULL,'N' UNION ALL

SELECT '242','82','Debt','Dec 14 2009 12:00AM','0.999329429',NULL,'N' UNION ALL

SELECT '242','82','Debt','Dec 15 2009 12:00AM','0.999284724',NULL,'N' UNION ALL

SELECT '242','82','Debt','Dec 16 2009 12:00AM','0.998543903',NULL,'N' UNION ALL

SELECT '242','82','Debt','Dec 17 2009 12:00AM','0.998939859',NULL,'N' UNION ALL

SELECT '242','82','Debt','Dec 18 2009 12:00AM','0.998907927',NULL,'N' UNION ALL

SELECT '242','82','Debt','Dec 19 2009 12:00AM','0.998978178',NULL,'N' UNION ALL

SELECT '242','82','Debt','Dec 20 2009 12:00AM','0.999054814',NULL,'N' UNION ALL

SELECT '242','82','Debt','Dec 21 2009 12:00AM','0.998582222',NULL,'N' UNION ALL

SELECT '242','82','Debt','Dec 22 2009 12:00AM','0.999131451',NULL,'N' UNION ALL

SELECT '242','82','Debt','Dec 23 2009 12:00AM','0.999131451',NULL,'N' UNION ALL

SELECT '242','82','Debt','Dec 24 2009 12:00AM','0.999131451',NULL,'N' UNION ALL

SELECT '242','82','Debt','Dec 25 2009 12:00AM','0.999131451',NULL,'N' UNION ALL

SELECT '242','82','Debt','Dec 26 2009 12:00AM','0.999131451',NULL,'N' UNION ALL

SELECT '242','82','Debt','Dec 27 2009 12:00AM','0.999131451',NULL,'N' UNION ALL

SELECT '242','82','Debt','Dec 28 2009 12:00AM','0.999131451',NULL,'N' UNION ALL

SELECT '242','82','Debt','Dec 29 2009 12:00AM','0.999910591',NULL,'N' UNION ALL

SELECT '242','82','Debt','Dec 30 2009 12:00AM','1.000204364',NULL,'N' UNION ALL

SELECT '242','82','Debt','Dec 31 2009 12:00AM','1.000447047',NULL,'N' UNION ALL

SELECT '242','82','Debt','Jan 1 2010 12:00AM','1.000727723',NULL,'N' UNION ALL

SELECT '242','82','Debt','Jan 2 2010 12:00AM','1.000140336',NULL,'N' UNION ALL

SELECT '242','82','Debt','Jan 3 2010 12:00AM','1.000287050',NULL,'N' UNION ALL

SELECT '242','82','Debt','Jan 4 2010 12:00AM','1.000095683',NULL,'N' UNION ALL

SELECT '242','82','Debt','Jan 5 2010 12:00AM','1.000835635',NULL,'Y' UNION ALL

SELECT '242','82','Debt','Jan 6 2010 12:00AM','0.999987253',NULL,'N' UNION ALL

SELECT '242','82','Debt','Jan 7 2010 12:00AM','0.999987253',NULL,'N' UNION ALL

SELECT '242','82','Debt','Jan 8 2010 12:00AM','0.999987253',NULL,'N' UNION ALL

SELECT '242','82','Debt','Jan 9 2010 12:00AM','1.000401535',NULL,'N' UNION ALL

SELECT '242','82','Debt','Jan 10 2010 12:00AM','1.000548127',NULL,'N' UNION ALL

SELECT '242','82','Debt','Jan 11 2010 12:00AM','1.000223075',NULL,'N' UNION ALL

SELECT '242','82','Debt','Jan 12 2010 12:00AM','1.000254943',NULL,'N' UNION ALL

SELECT '242','82','Debt','Jan 13 2010 12:00AM','1.000210328',NULL,'N' UNION ALL

SELECT '242','82','Debt','Jan 14 2010 12:00AM','1.000975156',NULL,'N' UNION ALL

SELECT '242','82','Debt','Jan 15 2010 12:00AM','1.001402185',NULL,'N' UNION ALL

SELECT '242','82','Debt','Jan 16 2010 12:00AM','1.001567898',NULL,'N' UNION ALL

SELECT '242','82','Debt','Jan 17 2010 12:00AM','1.001733610',NULL,'N' UNION ALL

SELECT '242','82','Debt','Jan 18 2010 12:00AM','1.001733610',NULL,'N' UNION ALL

SELECT '242','82','Debt','Jan 19 2010 12:00AM','1.001714490',NULL,'N' UNION ALL

SELECT '242','82','Debt','Jan 20 2010 12:00AM','1.001912070',NULL,'Y' UNION ALL

SELECT '242','82','Debt','Jan 21 2010 12:00AM','1.000642502',NULL,'N' UNION ALL

SELECT '242','82','Debt','Jan 22 2010 12:00AM','1.001310449',NULL,'N' UNION ALL

SELECT '242','82','Debt','Jan 23 2010 12:00AM','1.001475846',NULL,'N' UNION ALL

SELECT '242','82','Debt','Jan 24 2010 12:00AM','1.001647604',NULL,'N' UNION ALL

SELECT '242','82','Debt','Jan 25 2010 12:00AM','1.001482207',NULL,'N' UNION ALL

SELECT '242','82','Debt','Jan 26 2010 12:00AM','1.001653965',NULL,'N' UNION ALL

SELECT '242','82','Debt','Jan 27 2010 12:00AM','1.002379165',NULL,'N' UNION ALL

SELECT '242','82','Debt','Jan 28 2010 12:00AM','1.002379165',NULL,'N' UNION ALL

SELECT '242','82','Debt','Jan 29 2010 12:00AM','1.002379165',NULL,'N' UNION ALL

SELECT '242','82','Debt','Jan 30 2010 12:00AM','1.002379165',NULL,'N' UNION ALL

SELECT '242','82','Debt','Jan 31 2010 12:00AM','1.002379165',NULL,'N' UNION ALL

SELECT '242','82','Debt','Feb 1 2010 12:00AM','0.999968268',NULL,'N' UNION ALL

SELECT '242','82','Debt','Feb 2 2010 12:00AM','0.998794164',NULL,'N' UNION ALL

SELECT '242','82','Debt','Feb 3 2010 12:00AM','0.998794164',NULL,'N' UNION ALL

SELECT '242','82','Debt','Feb 4 2010 12:00AM','0.998629155',NULL,'N' UNION ALL

SELECT '242','82','Debt','Feb 5 2010 12:00AM','0.998883015',NULL,'N' UNION ALL

SELECT '242','82','Debt','Feb 6 2010 12:00AM','0.999060717',NULL,'N' UNION ALL

SELECT '242','82','Debt','Feb 7 2010 12:00AM','0.999244766',NULL,'N' UNION ALL

SELECT '242','82','Debt','Feb 8 2010 12:00AM','0.999359003',NULL,'N' UNION ALL

SELECT '242','82','Debt','Feb 20 2010 12:00AM','0.996020740',NULL,'N' UNION ALL

SELECT '242','82','Debt','Feb 21 2010 12:00AM','0.996211136',NULL,'N' UNION ALL

SELECT '242','82','Debt','Feb 27 2010 12:00AM','0.999251112',NULL,'N' UNION ALL

SELECT '242','82','Debt','Feb 28 2010 12:00AM','0.999441507',NULL,'N' UNION ALL

SELECT '242','82','Debt','Mar 1 2010 12:00AM','1.000336553',NULL,'N' UNION ALL

SELECT '242','82','Debt','Mar 23 2010 12:00AM','1.004356136',NULL,'N' UNION ALL

SELECT '242','82','Debt','Mar 24 2010 12:00AM','1.005168943',NULL,'N' UNION ALL

SELECT '242','82','Debt','Mar 25 2010 12:00AM','1.005346745',NULL,'N' UNION ALL

SELECT '242','82','Debt','Mar 26 2010 12:00AM','1.005600747',NULL,'N' UNION ALL

SELECT '242','82','Debt','Mar 27 2010 12:00AM','1.005778548',NULL,'N' UNION ALL

SELECT '242','82','Debt','Mar 28 2010 12:00AM','1.005937300',NULL,'N' UNION ALL

SELECT '242','82','Debt','Mar 29 2010 12:00AM','1.006762806',NULL,'N' UNION ALL

SELECT '242','82','Debt','Mar 30 2010 12:00AM','1.007347011',NULL,'N' UNION ALL

SELECT '242','82','Debt','Mar 31 2010 12:00AM','1.007347011',NULL,'N' UNION ALL

SELECT '242','82','Debt','Apr 1 2010 12:00AM','1.000460173',NULL,'N' UNION ALL

SELECT '242','82','Debt','Apr 2 2010 12:00AM','1.000176424',NULL,'N' UNION ALL

SELECT '242','82','Debt','Apr 3 2010 12:00AM','1.000359148',NULL,'N' UNION ALL

SELECT '242','82','Debt','Apr 4 2010 12:00AM','1.000541872',NULL,'N' UNION ALL

SELECT '242','82','Debt','Apr 5 2010 12:00AM','1.000535571',NULL,'N' UNION ALL

SELECT '242','82','Debt','Apr 6 2010 12:00AM','1.000919923',NULL,'N' UNION ALL

SELECT '242','82','Debt','Apr 7 2010 12:00AM','1.001770536',NULL,'N' UNION ALL

SELECT '242','82','Debt','Apr 8 2010 12:00AM','1.001833544',NULL,'N' UNION ALL

SELECT '242','82','Debt','Apr 9 2010 12:00AM','1.001266469',NULL,'N' UNION ALL

SELECT '242','82','Debt','Apr 10 2010 12:00AM','1.001430291',NULL,'N' UNION ALL

SELECT '242','82','Debt','Apr 11 2010 12:00AM','1.001587812',NULL,'N' UNION ALL

SELECT '242','82','Debt','Apr 12 2010 12:00AM','1.001171956',NULL,'N' UNION ALL

SELECT '242','82','Debt','Apr 13 2010 12:00AM','1.000957728',NULL,'N' UNION ALL

SELECT '242','82','Debt','Apr 14 2010 12:00AM','1.001127850',NULL,'N' UNION ALL

SELECT '242','82','Debt','Apr 15 2010 12:00AM','1.000365449',NULL,'N' UNION ALL

SELECT '242','82','Debt','Apr 16 2010 12:00AM','1.000976630',NULL,'N' UNION ALL

SELECT '242','82','Debt','Apr 17 2010 12:00AM','1.001115249',NULL,'N' UNION ALL

SELECT '242','82','Debt','Apr 18 2010 12:00AM','1.001253867',NULL,'N' UNION ALL

SELECT '242','82','Debt','Apr 19 2010 12:00AM','1.001310575',NULL,'N' UNION ALL

SELECT '242','82','Debt','Apr 20 2010 12:00AM','1.002608548',NULL,'N' UNION ALL

SELECT '242','82','Debt','Apr 21 2010 12:00AM','1.003604080',NULL,'N' UNION ALL

SELECT '242','82','Debt','Apr 22 2010 12:00AM','1.005141485',NULL,'N' UNION ALL

SELECT '242','82','Debt','Apr 23 2010 12:00AM','1.005979497',NULL,'N' UNION ALL

SELECT '242','82','Debt','Apr 24 2010 12:00AM','1.006105514',NULL,'N' UNION ALL

SELECT '242','82','Debt','Apr 25 2010 12:00AM','1.006231531',NULL,'N' UNION ALL

SELECT '242','82','Debt','Apr 26 2010 12:00AM','1.006313442',NULL,'N' UNION ALL

SELECT '242','82','Debt','Apr 27 2010 12:00AM','1.006376450',NULL,'N' UNION ALL

SELECT '242','82','Debt','Apr 28 2010 12:00AM','1.008121783',NULL,'N' UNION ALL

SELECT '242','82','Debt','Apr 29 2010 12:00AM','1.008317109',NULL,'N' UNION ALL

SELECT '242','82','Debt','Apr 30 2010 12:00AM','1.008468329',NULL,'N' UNION ALL

SELECT '242','82','Debt','May 1 2010 12:00AM','1.000131207',NULL,'N' UNION ALL

SELECT '242','82','Debt','May 2 2010 12:00AM','1.000131189',NULL,'N' UNION ALL

SELECT '242','82','Debt','May 3 2010 12:00AM','0.999981259',NULL,'N' UNION ALL

SELECT '242','82','Debt','May 4 2010 12:00AM','1.001005785',NULL,'N' UNION ALL

SELECT '242','82','Debt','May 5 2010 12:00AM','1.002030311',NULL,'N' UNION ALL

SELECT '242','82','Debt','May 6 2010 12:00AM','1.001486812',NULL,'N' UNION ALL

SELECT '242','82','Debt','May 7 2010 12:00AM','1.002242713',NULL,'N' UNION ALL

SELECT '242','82','Debt','May 8 2010 12:00AM','1.002367655',NULL,'N' UNION ALL

SELECT '242','82','Debt','May 9 2010 12:00AM','1.002498844',NULL,'N' UNION ALL

SELECT '242','82','Debt','May 10 2010 12:00AM','1.001274411',NULL,'N' UNION ALL

SELECT '242','82','Debt','May 11 2010 12:00AM','1.001574272',NULL,'N' UNION ALL

SELECT '242','82','Debt','May 12 2010 12:00AM','1.001980334',NULL,'N' UNION ALL

SELECT '242','82','Debt','May 13 2010 12:00AM','1.002442620',NULL,'N' UNION ALL

SELECT '242','82','Debt','May 14 2010 12:00AM','1.003179779',NULL,'N' UNION ALL

SELECT '242','82','Debt','May 15 2010 12:00AM','1.003323463',NULL,'N' UNION ALL

SELECT '242','82','Debt','May 16 2010 12:00AM','1.003460899',NULL,'N' UNION ALL

SELECT '242','82','Debt','May 17 2010 12:00AM','1.003435911',NULL,'N' UNION ALL

SELECT '242','82','Debt','May 18 2010 12:00AM','1.004291765',NULL,'N' UNION ALL

SELECT '242','82','Debt','May 19 2010 12:00AM','1.005003936',NULL,'N' UNION ALL

SELECT '242','82','Debt','May 20 2010 12:00AM','1.004280708',NULL,'N' UNION ALL

SELECT '242','82','Debt','May 21 2010 12:00AM','1.004602996',NULL,'N' UNION ALL

SELECT '242','82','Debt','May 22 2010 12:00AM','1.004748991',NULL,'N' UNION ALL

SELECT '242','82','Debt','May 23 2010 12:00AM','1.004895298',NULL,'N' UNION ALL

SELECT '242','82','Debt','May 24 2010 12:00AM','1.004756862',NULL,'N' UNION ALL

SELECT '242','82','Debt','May 25 2010 12:00AM','1.004987693',NULL,'N' UNION ALL

SELECT '242','82','Debt','May 26 2010 12:00AM','1.004193685',NULL,'N' UNION ALL

SELECT '242','82','Debt','May 27 2010 12:00AM','1.004344303',NULL,'N' UNION ALL

SELECT '242','82','Debt','May 28 2010 12:00AM','1.003557480',NULL,'N' UNION ALL

SELECT '242','82','Debt','May 29 2010 12:00AM','1.003715844',NULL,'N' UNION ALL

SELECT '242','82','Debt','May 30 2010 12:00AM','1.003874271',NULL,'N' UNION ALL

SELECT '242','82','Debt','May 31 2010 12:00AM','1.004217674',NULL,'N' UNION ALL

SELECT '242','82','Debt','Jun 1 2010 12:00AM','0.999868491',NULL,'N' UNION ALL

SELECT '242','82','Debt','Jun 2 2010 12:00AM','1.000441740',NULL,'N' UNION ALL

SELECT '242','82','Debt','Jun 3 2010 12:00AM','1.000230203',NULL,'N' UNION ALL

SELECT '242','82','Debt','Jun 4 2010 12:00AM','1.000771490',NULL,'N' UNION ALL

SELECT '242','82','Debt','Jun 5 2010 12:00AM','1.000609726',NULL,'N' UNION ALL

SELECT '242','82','Debt','Jun 6 2010 12:00AM','1.000771490',NULL,'N' UNION ALL

SELECT '242','82','Debt','Jun 7 2010 12:00AM','1.001070131',NULL,'N' UNION ALL

SELECT '242','82','Debt','Jun 8 2010 12:00AM','1.001256782',NULL,'N' UNION ALL

SELECT '242','82','Debt','Jun 9 2010 12:00AM','1.001318999',NULL,'N' UNION ALL

SELECT '242','82','Debt','Jun 10 2010 12:00AM','1.000914589',NULL,'N' UNION ALL

SELECT '242','82','Debt','Jun 11 2010 12:00AM','1.000914589',NULL,'N' UNION ALL

SELECT '242','82','Debt','Jun 12 2010 12:00AM','1.000914589',NULL,'N' UNION ALL

SELECT '242','82','Debt','Jun 13 2010 12:00AM','1.000914589',NULL,'N' UNION ALL

SELECT '242','82','Debt','Jun 14 2010 12:00AM','1.000914589',NULL,'N' UNION ALL

SELECT '242','82','Debt','Jun 15 2010 12:00AM','1.000914589',NULL,'N' UNION ALL

SELECT '242','82','Debt','Jun 16 2010 12:00AM','1.000914589',NULL,'N' UNION ALL

SELECT '242','82','Debt','Jun 17 2010 12:00AM','1.000914589',NULL,'N'

Required Output is attached

Rule:

1. For Every first date benchmark value should be calculated based o below mentioned formula up to month end date.

Formula : Benchmark Value of 31st of previous month or (IsFlow = 'Y') * ret of 1st

This is only for every 1st.

after 1st, from 2nd onward portfolio value of 1st should be considered upto month end date.

I hope it will help you...

vijay.s SSCrazy Points: 2161 More actions · Answer 9

I Got the solution.....

Mentioned below

--===== Declare the working variables

DECLARE @clnt_id INT,

@tpl_id INT,

@ast_cls_cd VARCHAR(25),

@cumReturn NUMERIC(18,9)

--===== Calculate the portfolio using return multiply by previous available value for every row

-- and remember the client id, templet id and asset class for the next row

UPDATE #blnd_bnmk

SET @cumReturn = blnd_bnmk_val = CASE

WHEN clnt_id = @clnt_id

AND tpl_id = @tpl_id

AND ast_cls_cd = @ast_cls_cd

AND blnd_bnmk_val IS NULL

THEN @cumReturn * [Ret]

WHEN clnt_id <> @clnt_id

OR tpl_id <> @tpl_id

OR ast_cls_cd <> @ast_cls_cd

OR blnd_bnmk_val IS NOT NULL

THEN blnd_bnmk_val

END,

@clnt_id = clnt_id,

@tpl_id = tpl_id,

@ast_cls_cd = ast_cls_cd

FROM #blnd_bnmk WITH(TABLOCKX)

WHERE

( -- Include every month end date for which portfolio needs to calculated

(blnd_bnmk_dt = CAST(FLOOR(CAST(dateadd(ms,-3,DATEADD(mm, DATEDIFF(m,0,blnd_bnmk_dt )+1, 0)) AS FLOAT)) AS DATETIME))

OR

-- Include every month start date for which portfolio needs to created

(DAY(blnd_bnmk_dt) = 1)

OR

-- Include every flow date for which portfolio needs to created

(IsFlow = 'Y')

)

OPTION (MAXDOP 1)

--===== Re-Set the variable

SET @clnt_id = 0

SET @tpl_id = 0

SET @ast_cls_cd = NULL

SET @cumReturn = 0

--===== Update all blank portfolio with previous available portfolio so we can manually multiply the same with return

UPDATE #blnd_bnmk

SET @cumReturn = blnd_bnmk_val = CASE

WHEN clnt_id = @clnt_id

AND tpl_id = @tpl_id

AND ast_cls_cd = @ast_cls_cd

AND blnd_bnmk_val IS NULL

THEN @cumReturn

WHEN clnt_id <> @clnt_id

OR tpl_id <> @tpl_id

OR ast_cls_cd <> @ast_cls_cd

OR blnd_bnmk_val IS NOT NULL

THEN blnd_bnmk_val

END,

@clnt_id = clnt_id,

@tpl_id = tpl_id,

@ast_cls_cd = ast_cls_cd

FROM #blnd_bnmk WITH(TABLOCKX)

OPTION (MAXDOP 1)

--- And Final OutPut

SELECT blnd_bnmk_dt,tpl_id,ast_cls_cd,clnt_id,bnmk_rt,no_flow_bnmk_rt,ret,blnd_bnmk_val,IsFlow

FROM #blnd_bnmk

WHERE

(

(blnd_bnmk_dt = CAST(FLOOR(CAST(dateadd(ms,-3,DATEADD(mm, DATEDIFF(m,0,blnd_bnmk_dt )+1, 0)) AS FLOAT)) AS DATETIME))

OR

(DAY(blnd_bnmk_dt) = 1)

OR

(IsFlow = 'Y')

)

UNION

SELECT blnd_bnmk_dt,tpl_id,ast_cls_cd,clnt_id,bnmk_rt,no_flow_bnmk_rt,ret,ret * blnd_bnmk_val,IsFlow

FROM #blnd_bnmk

WHERE IsFlow = 'N'

AND day(blnd_bnmk_dt) <> 1

AND blnd_bnmk_dt <> CAST(FLOOR(CAST(dateadd(ms,-3,DATEADD(mm, DATEDIFF(m,0,blnd_bnmk_dt )+1, 0)) AS FLOAT)) AS DATETIME)

Thankx Jeff Sir