May 6, 2010 at 12:39 pm
Hi good folks,
I'd appreciate some expert assistance on this.
I am looking to produce the third column given the first two columns in
straight SQL without using cursors. Here is a copy of the resultset done in
Excel:
ID|ClientCode| ReturnPercent| CumulativeReturn (Calculated)
----------------------------------------------------------
1 | AC001 | 3.495868 | 100
2 | AC001 | 4.795868 | 104.795868
3| AC001 | -2.493868 | 102.1823974
1| AC002 | 12.0048 | 100
2| AC002 | 2 | 200
3| AC002 | 0.245568 | 49.1136
Formula:
1. CumulativeReturn = [ReturnPercent]*[Previous Line's CumulativeReturn]
2. First row always starts with 100 (can be change client id wise only) and other row will be null and calculated based on 1st point
3. The number of rows are variable
4. The calculation based uplon client code wise
DECLARE @Scores TABLE
(
IDINT,
ClientCode INT,
ReturnPercent NUMERIC(18,4),
CumulativeReturn NUMERIC(18,4)
)
INSERT @Scores VALUES(1 , 1 , 3.495868 , 100)
INSERT @Scores VALUES(2 , 1 , 4.795868 , NULL)--104.795868 after calculation
INSERT @Scores VALUES(3, 1 , -2.493868 , NULL)--102.1823974 after calculation
INSERT @Scores VALUES(1, 2 , 12.0048 , 100)
INSERT @Scores VALUES(2, 2 , 2 , NULL) -- 200 After Calculation
INSERT @Scores VALUES(3, 2 , 0.245568 , NULL) -- After Calculation 49.1136
May 6, 2010 at 1:30 pm
Please its on urgent basic
May 6, 2010 at 1:33 pm
What have you tried to far? If it's THAT urgent, you must have worked a bit on it?
You know, people that help you are on these forums aren't paid for what they do, so taking the time to get a nicely tested solution for you comes after their work is done at their real workplace. I think you should consider hiring a consultant for quicker answering, if it's that urgent. Otherwise, help us help you, show us what you have, and we'll start from there, when we get a chance.
Cheers,
J-F
May 6, 2010 at 1:33 pm
I think you will get great mileage out of reading an article by Jeff Moden on the subject.
http://www.sqlservercentral.com/articles/T-SQL/68467/
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
May 6, 2010 at 1:36 pm
Of course i tryed from my side but not found any solution
;With RowOrder As
(
Select clnt_id, ret, blnd_bnmk_val, row_num As rn
From #eod_blnd_bnmk
)
,
CumReturnCTE As
(
Select r.clnt_id, r.ret, cast(r.blnd_bnmk_val as float) As CumulativeReturn, r.rn
From RowOrder r
Where r.rn = 1
Union All
Select r1.clnt_id, r1.ret, c.CumulativeReturn * (r1.Ret), r1.rn
From RowOrder r1
Inner Join CumReturnCTE c
On r1.clnt_id = c.clnt_id
and r1.rn = c.rn + 1
)
Select *--ReturnMonth, ReturnPercent, CumulativeReturn
From CumReturnCTE
ORDER by rn
OPTION (MAXRECURSION 0);
May 6, 2010 at 1:39 pm
vijay.s (5/6/2010)
Hi good folks,I'd appreciate some expert assistance on this.
I am looking to produce the third column given the first two columns in
straight SQL without using cursors. Here is a copy of the resultset done in
Excel:
ID|ClientCode| ReturnPercent| CumulativeReturn (Calculated)
----------------------------------------------------------
1 | AC001 | 3.495868 | 100
2 | AC001 | 4.795868 | 104.795868
3| AC001 | -2.493868 | 102.1823974
1| AC002 | 12.0048 | 100
2| AC002 | 2 | 200
3| AC002 | 0.245568 | 49.1136
Formula:
1. CumulativeReturn = [ReturnPercent]*[Previous Line's CumulativeReturn]
2. First row always starts with 100 (can be change client id wise only) and other row will be null and calculated based on 1st point
3. The number of rows are variable
4. The calculation based uplon client code wise
Are you sure you want to multiply here? Cumulative is a sum function especially since you already have the ReturnPercent field calculated as a percent. The Desired output doesn't match your formula description.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
May 6, 2010 at 1:49 pm
Ok, here's a coded version based on the link Jason provided:
DECLARE
@ClientCode INT,
@cumReturn NUMERIC(18,4)
SET @ClientCode=0
SET @cumReturn=0
UPDATE #Scores
SET
@cumReturn=
CumulativeReturn=
CASE
WHEN @ClientCode=ClientCode
THEN @cumReturn * (100 + ReturnPercent)/100
ELSE CumulativeReturn
END,
@ClientCode = clientcode
FROM #Scores WITH (TABLOCKX)
OPTION (MAXDOP 1)
SELECT *
FROM #Scores
A few things to notice:
1) you need to read the article Jason mentioned in order to know about the requirements that must be fulfilled to use this quirky update method.
2) Like Jason mentioned in the previous post, your description and your sample dta don't match. So you might need to modify the solution.
May 6, 2010 at 1:49 pm
No ReturnPercent its only name it have full numeric value like amount.
May 6, 2010 at 1:50 pm
I just noticed that you have two different formulas occurring in your sample data as well. For Client 1 you are summing (correct based on cumulative) and for client2 you are multiplying (not correct for cumulative).
Here is the updated code using the same code you provided.
DECLARE @Scores TABLE
(
ID INT,
ClientCode INT,
ReturnPercent NUMERIC(18,4),
CumulativeReturn NUMERIC(18,4)
)
INSERT @Scores VALUES(1 , 1 , 3.495868 , 100)
INSERT @Scores VALUES(2 , 1 , 4.795868 , NULL)--104.795868 after calculation
INSERT @Scores VALUES(3, 1 , -2.493868 , NULL)--102.1823974 after calculation
INSERT @Scores VALUES(1, 2 , 12.0048 , 100)
INSERT @Scores VALUES(2, 2 , 2 , NULL) -- 200 After Calculation
INSERT @Scores VALUES(3, 2 , 0.245568 , NULL) -- After Calculation 49.1136
;With RowOrder As
(
Select ClientCode, ReturnPercent, CumulativeReturn, ID As rn
From @Scores
)
,
CumReturnCTE As
(
Select r.ClientCode, r.ReturnPercent, cast(r.CumulativeReturn as float) As CumulativeReturn, r.rn
From RowOrder r
Where r.rn = 1
Union All
Select r1.ClientCode, r1.ReturnPercent, c.CumulativeReturn + (cast(r1.ReturnPercent as float)), r1.rn
From RowOrder r1
Inner Join CumReturnCTE c
On r1.ClientCode = c.ClientCode
and r1.rn = c.rn + 1
)
Select *--ReturnMonth, ReturnPercent, CumulativeReturn
From CumReturnCTE
ORDER by ClientCode,rn
OPTION (MAXRECURSION 0);
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
May 6, 2010 at 1:50 pm
vijay.s (5/6/2010)
No ReturnPercent its only name it have full numeric value like amount.
But for client 1 your sample data is summing that column with the cumulative column.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
May 6, 2010 at 1:52 pm
CirquedeSQLeil (5/6/2010)
vijay.s (5/6/2010)
No ReturnPercent its only name it have full numeric value like amount.But for client 1 your sample data is summing that column with the cumulative column.
No, it's not the sum.
Seems like the following formula is used (using my quirky update variable):
@cumReturn * (100 + ReturnPercent) / 100
May 6, 2010 at 1:54 pm
sorry jeson i query i want multiply not addition
May 6, 2010 at 3:26 pm
Here is the update then:
DECLARE @Scores TABLE
(
ID INT,
ClientCode INT,
ReturnPercent NUMERIC(18,4),
CumulativeReturn NUMERIC(18,4)
)
INSERT @Scores VALUES(1 , 1 , 3.495868 , 100)
INSERT @Scores VALUES(2 , 1 , 4.795868 , NULL)--104.795868 after calculation
INSERT @Scores VALUES(3, 1 , -2.493868 , NULL)--102.1823974 after calculation
INSERT @Scores VALUES(1, 2 , 12.0048 , 100)
INSERT @Scores VALUES(2, 2 , 2 , NULL) -- 200 After Calculation
INSERT @Scores VALUES(3, 2 , 0.245568 , NULL) -- After Calculation 49.1136
;With RowOrder As
(
Select ClientCode, ReturnPercent, CumulativeReturn, ID As rn
From @Scores
)
,
CumReturnCTE As
(
Select r.ClientCode, r.ReturnPercent, cast(r.CumulativeReturn as float) As CumulativeReturn, r.rn
From RowOrder r
Where r.rn = 1
Union All
Select r1.ClientCode, r1.ReturnPercent, c.CumulativeReturn * (100 + cast(r1.ReturnPercent as float)) / 100, r1.rn
From RowOrder r1
Inner Join CumReturnCTE c
On r1.ClientCode = c.ClientCode
and r1.rn = c.rn + 1
)
Select *--ReturnMonth, ReturnPercent, CumulativeReturn
From CumReturnCTE
ORDER by ClientCode,rn
OPTION (MAXRECURSION 0);
Simple change to the formula in the cumulative calculation.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply