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)
 Compounding Returns

Author  Topic 

Kapital123
Starting Member

31 Posts
Posted - 2013-06-06 : 02:21:05

Looking to create a query, as simple as possible, that allows me to compound returns on a rolling three monthly basis. The first problem is how to actually take the product, given there is no PRODUCT syntax. And using EXP(SUM(Log(X))) will also not work given returns can be negative. I have no idea, maybe someone can provide some colour.

So this a small sample of what the output should look like:

PORTFOLIO DATE ONE_MONTH_RETURN THREE_MONTH_RETURN
A 31/1/12 5% NULL
A 31/2/12 8% NULL
A 31/3/12 1% 14.534%
A 31/4/12 10% 19.988%
A 31/5/12 -5% 9.989%
A 31/6/12 -2% 6.722%

So to calculate the three monthly compounded return on 31/3/12 it would be [(1+0.05)*(1+0.08)*(1+0.01)] -1.

I am hoping to use the same logic above to do four monthly, five monthly, six monthly, yearly etc compounded returns.

Greatly appreciate any help on this tricky problem.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2013-06-06 : 03:00:26
[code]
CREATE FUNCTION GETCompoundingReturn
(
@PORTFOLIO varchar(10),
@STARTDATE datetime,
@ENDDATE datetime
)
RETURNS Numeric(20,3)
AS
BEGIN
DECLARE @CompRet Numeric(20,3)

SELECT @CompRet = COALESCE(@CompRet,1) * (1 +
CAST(REPLACE(ONE_MONTH_RETURN,'%','') AS numeric(10,2)) )
FROM Table
WHERE PORTFOLIO = @PORTFOLIO
AND [DATE] > = @STARTDATE
AND [DATE] < @ENDDATE + 1

RETURN (@CompRet)
END

Then call it like
;With CTE
AS
(
SELECT ROW_NUMBER() OVER (PARTITION BY PORTFOLIO ORDER BY [DATE] DESC) AS Seq, *
FROM Table
)
SELECT c1.PORTFOLIO,c1.[DATE],c1.ONE_MONTH_RETURN,
CASE WHEN c2.PORTFOLIO IS NULL THEN NULL ELSE dbo.GETCompoundingReturn(c1.PORTFOLIO,c2.[DATE],c1.[DATE])-1 END
FROM CTE c2
LEFT JOIN CTE c2
ON c2.PORTFOLIO = c1.PORTFOLIO
AND c2.Seq = c1.Seq-2
[/code]


EDIT: added -1 to make formula correct

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

Kapital123
Starting Member

31 Posts
Posted - 2013-06-06 : 03:28:43
Hello,

Thanks for your prompt reply. Could you please clear up what you mean when you say:

Then call it like
;With CTE
AS...

Never used a CREATE FUNCTION. Hoping to get the code working so I can learn from what you have done. Thanks alot
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2013-06-06 : 03:58:54
quote:
Originally posted by Kapital123

Hello,

Thanks for your prompt reply. Could you please clear up what you mean when you say:

Then call it like
;With CTE
AS...

Never used a CREATE FUNCTION. Hoping to get the code working so I can learn from what you have done. Thanks alot

execute the first pasrt (CREATE FNCTIOn) alone
it will create function in database
then for using it run next part starting With CTE
it will invoke function which is already created


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2013-06-06 : 05:29:58
If it was SQL 2012, you could just do this instead of creating UDF etc


SELECT PORTFOLIO, DATE, ONE_MONTH_RETURN,
((1+ REPLACE(PrevPrevValue,'%','')) * (1+ REPLACE(PrevValue,'%','')) * (1+ REPLACE(ONE_MONTH_RETURN,'%','')))-1  AS THREE_MONTH_RETURN
FROM 
(
SELECT *,
LAG(ONE_MONTH_RETURN,1) OVER (PARTITION BY PORTFOLIO ORDER BY [DATE] ASC) AS PrevValue,
LAG(ONE_MONTH_RETURN,2) OVER (PARTITION BY PORTFOLIO ORDER BY [DATE] ASC) AS PrevPrevValue
FROM table
)t


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

Kapital123
Starting Member

31 Posts
Posted - 2013-06-07 : 01:11:40
Unfortunately I couldn't get this to work. In your code did you correctly specify table c2? There seems to be multiply references to it.

quote:
Originally posted by visakh16

quote:
Originally posted by Kapital123

Hello,

Thanks for your prompt reply. Could you please clear up what you mean when you say:

Then call it like
;With CTE
AS...

Never used a CREATE FUNCTION. Hoping to get the code working so I can learn from what you have done. Thanks alot

execute the first pasrt (CREATE FNCTIOn) alone
it will create function in database
then for using it run next part starting With CTE
it will invoke function which is already created


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2013-06-07 : 01:54:17
quote:
Originally posted by Kapital123

Unfortunately I couldn't get this to work. In your code did you correctly specify table c2? There seems to be multiply references to it.

quote:
Originally posted by visakh16

quote:
Originally posted by Kapital123

Hello,

Thanks for your prompt reply. Could you please clear up what you mean when you say:

Then call it like
;With CTE
AS...

Never used a CREATE FUNCTION. Hoping to get the code working so I can learn from what you have done. Thanks alot

execute the first pasrt (CREATE FNCTIOn) alone
it will create function in database
then for using it run next part starting With CTE
it will invoke function which is already created


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs



it was atypo. one was c1

CREATE FUNCTION GETCompoundingReturn
(
@PORTFOLIO varchar(10),
@STARTDATE datetime,
@ENDDATE datetime
)
RETURNS Numeric(20,3)
AS
BEGIN
DECLARE @CompRet Numeric(20,3)

SELECT @CompRet = COALESCE(@CompRet,1) * (1 + 
CAST(REPLACE(ONE_MONTH_RETURN,'%','') AS numeric(10,2)) )
FROM Table
WHERE PORTFOLIO = @PORTFOLIO
AND [DATE] > = @STARTDATE
AND [DATE] < @ENDDATE + 1

RETURN (@CompRet)
END

Then call it like
;With CTE
AS
(
SELECT ROW_NUMBER() OVER (PARTITION BY PORTFOLIO ORDER BY [DATE] DESC) AS Seq, *
FROM Table
)
SELECT c1.PORTFOLIO,c1.[DATE],c1.ONE_MONTH_RETURN,
CASE WHEN c2.PORTFOLIO IS NULL THEN NULL ELSE dbo.GETCompoundingReturn(c1.PORTFOLIO,c2.[DATE],c1.[DATE])-1 END
FROM  CTE c1
LEFT JOIN CTE c2
ON c2.PORTFOLIO = c1.PORTFOLIO
AND c2.Seq = c1.Seq-2


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

Kapital123
Starting Member

31 Posts
Posted - 2013-06-07 : 02:13:57
When I run this second piece of code (below) I generate an additional column ("No column name") which simply generates NULLS. Any ideas?

With CTE
AS
(
SELECT ROW_NUMBER() OVER (PARTITION BY PORTFOLIO ORDER BY [DATE] DESC) AS Seq, *
FROM Table
)
SELECT c1.PORTFOLIO,c1.[DATE],c1.ONE_MONTH_RETURN,
CASE WHEN c2.PORTFOLIO IS NULL THEN NULL ELSE dbo.GETCompoundingReturn(c1.PORTFOLIO,c2.[DATE],c1.[DATE])-1 END
FROM CTE c1
LEFT JOIN CTE c2
ON c2.PORTFOLIO = c1.PORTFOLIO
AND c2.Seq = c1.Seq-2



quote:
Originally posted by visakh16

quote:
Originally posted by Kapital123

Unfortunately I couldn't get this to work. In your code did you correctly specify table c2? There seems to be multiply references to it.

quote:
Originally posted by visakh16

quote:
Originally posted by Kapital123

Hello,

Thanks for your prompt reply. Could you please clear up what you mean when you say:

Then call it like
;With CTE
AS...

Never used a CREATE FUNCTION. Hoping to get the code working so I can learn from what you have done. Thanks alot

execute the first pasrt (CREATE FNCTIOn) alone
it will create function in database
then for using it run next part starting With CTE
it will invoke function which is already created


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs



it was atypo. one was c1

CREATE FUNCTION GETCompoundingReturn
(
@PORTFOLIO varchar(10),
@STARTDATE datetime,
@ENDDATE datetime
)
RETURNS Numeric(20,3)
AS
BEGIN
DECLARE @CompRet Numeric(20,3)

SELECT @CompRet = COALESCE(@CompRet,1) * (1 + 
CAST(REPLACE(ONE_MONTH_RETURN,'%','') AS numeric(10,2)) )
FROM Table
WHERE PORTFOLIO = @PORTFOLIO
AND [DATE] > = @STARTDATE
AND [DATE] < @ENDDATE + 1

RETURN (@CompRet)
END

Then call it like
;With CTE
AS
(
SELECT ROW_NUMBER() OVER (PARTITION BY PORTFOLIO ORDER BY [DATE] DESC) AS Seq, *
FROM Table
)
SELECT c1.PORTFOLIO,c1.[DATE],c1.ONE_MONTH_RETURN,
CASE WHEN c2.PORTFOLIO IS NULL THEN NULL ELSE dbo.GETCompoundingReturn(c1.PORTFOLIO,c2.[DATE],c1.[DATE])-1 END
FROM  CTE c1
LEFT JOIN CTE c2
ON c2.PORTFOLIO = c1.PORTFOLIO
AND c2.Seq = c1.Seq-2


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2013-06-07 : 02:20:36
can you show the output it gives? Also is DATE field of datetime type?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

Kapital123
Starting Member

31 Posts
Posted - 2013-06-07 : 02:39:31
Yes [Date] is of datetime format. See Output directly below. The 9999 you see are simply dummie values...I should probably change them to NULL also.

PORTFOLIO DATE ONE_MONTH (No column name)
EMKARF 2012-12-31 00:00:00.000 9999 NULL
EMKARF 2012-11-30 00:00:00.000 9999 NULL
EMKARF 2012-10-31 00:00:00.000 9999 NULL
EMKARF 2012-09-30 00:00:00.000 9999 NULL
EMKARF 2012-08-31 00:00:00.000 0.00841153401758379 NULL
EMKARF 2012-07-31 00:00:00.000 0.00802371319426965 NULL
EMKARF 2012-06-30 00:00:00.000 0.00437443657740445 NULL
EMKARF 2012-05-31 00:00:00.000 0.00554395280214097 NULL
EMKARF 2012-04-30 00:00:00.000 0.00563714720596636 NULL
EMKARF 2012-03-31 00:00:00.000 0.00714089383766138 NULL
EMKARF 2012-02-29 00:00:00.000 0.00844207651463739 NULL
EMKARF 2012-01-31 00:00:00.000 0.0062080132401013 NULL
EMKARF 2011-12-31 00:00:00.000 0.00582730180835977 NULL
EMKARF 2011-11-30 00:00:00.000 0.00116297605972688 NULL
EMKARF 2011-10-31 00:00:00.000 0.00624001695159963 NULL
EMKARF 2011-09-30 00:00:00.000 0.00192256416522551 NULL
EMKARF 2011-08-31 00:00:00.000 0.00119398447173793 NULL
EMKARF 2011-07-31 00:00:00.000 0.00727460965789952 NULL
EMKARF 2011-06-30 00:00:00.000 0.00170377934155901 NULL
EMKARF 2011-05-31 00:00:00.000 0.00666209970494379 NULL
EMKARF 2011-04-30 00:00:00.000 0.00676732575936364 NULL
EMKARF 2011-03-31 00:00:00.000 0.00524212581391621 NULL
EMKARF 2011-02-28 00:00:00.000 0.00578389405366669 NULL
EMKARF 2011-01-31 00:00:00.000 0.00618130095946863 NULL
EMKARF 2010-12-31 00:00:00.000 0.0043340231628382 NULL
EMKARF 2010-11-30 00:00:00.000 0.00390096695765551 NULL
EMKARF 2010-10-31 00:00:00.000 0.00634518493320922 NULL
EMKARF 2010-09-30 00:00:00.000 0.00364433124770775 NULL
EMKARF 2010-08-31 00:00:00.000 0.00771802616312844 NULL
EMKARF 2010-07-31 00:00:00.000 0.00575590002822877 NULL
EMKARF 2010-06-30 00:00:00.000 0.00444035735220869 NULL
EMKARF 2010-05-31 00:00:00.000 0.00303908122163591 NULL
EMKARF 2010-04-30 00:00:00.000 0.00549300922385962 NULL
EMKARF 2010-03-31 00:00:00.000 0.00565905955688405 NULL
EMKARF 2010-02-28 00:00:00.000 0.00597164678551675 NULL
EMKARF 2010-01-31 00:00:00.000 0.00844920580945296 NULL
EMKARF 2009-12-31 00:00:00.000 0.003603761883477 NULL
EMKARF 2009-11-30 00:00:00.000 0.00704810344491613 NULL
EMKARF 2009-10-31 00:00:00.000 0.00683960201487275 NULL
EMKARF 2009-09-30 00:00:00.000 0.00632306761443302 NULL
EMKARF 2009-08-31 00:00:00.000 0.00692912023717085 NULL
EMKARF 2009-07-31 00:00:00.000 0.0096 NULL
EMKARF 2009-06-30 00:00:00.000 0.00972549235895448 NULL
EMKARF 2009-05-31 00:00:00.000 0.00625769755629232 NULL
EMKARF 2009-04-30 00:00:00.000 0.00680361760806814 NULL
EMKARF 2009-03-31 00:00:00.000 -0.00166666666666648 NULL
EMKARF 2009-02-28 00:00:00.000 0.00453023439038835 NULL
EMKARF 2009-01-31 00:00:00.000 0.00905867453419851 NULL
EMKARF 2008-12-31 00:00:00.000 0.00428070835502203 NULL
EMKARF 2008-11-30 00:00:00.000 0.00742059958444652 NULL
EMKARF 2008-10-31 00:00:00.000 0.0113143135522884 NULL
EMKARF 2008-09-30 00:00:00.000 -0.00175114310730662 NULL
EMKARF 2008-08-31 00:00:00.000 0.0085361067503924 NULL
EMKARF 2008-07-31 00:00:00.000 0.00961632970625437 NULL
EMKARF 2008-06-30 00:00:00.000 0.00264783759929377 NULL
EMKARF 2008-05-31 00:00:00.000 0.00532386867790513 NULL
EMKARF 2008-04-30 00:00:00.000 0.00731510595280582 NULL
EMKARF 2008-03-31 00:00:00.000 0.00400634711863646 NULL
EMKARF 2008-02-29 00:00:00.000 0.00341064959367343 NULL
EMKARF 2008-01-31 00:00:00.000 0.0055043959201333 NULL
EMKARF 2007-12-31 00:00:00.000 0.00558313288817369 NULL
EMKARF 2007-11-30 00:00:00.000 -0.00175860577377662 NULL
EMKARF 2007-10-31 00:00:00.000 0.00578785252135461 NULL
EMKARF 2007-09-30 00:00:00.000 0.00515004456769308 NULL
EMKARF 2007-08-31 00:00:00.000 0.00507664742185954 NULL
EMKARF 2007-07-31 00:00:00.000 0.000510372620311506 NULL
EMKARF 2007-06-30 00:00:00.000 0.00350000000000006 NULL
EMKARF 2007-05-31 00:00:00.000 9999 NULL
EMKARF 2007-04-30 00:00:00.000 9999 NULL
EMKARF 2007-03-31 00:00:00.000 9999 NULL
EMKARF 2007-02-28 00:00:00.000 9999 NULL
EMKARF 2007-01-31 00:00:00.000 9999 NULL


quote:
Originally posted by visakh16

can you show the output it gives? Also is DATE field of datetime type?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2013-06-07 : 02:52:51
i think i got the issue. can you make the code like this and see?


;With CTE
AS
(
SELECT ROW_NUMBER() OVER (PARTITION BY PORTFOLIO ORDER BY [DATE] DESC) AS Seq, *
FROM Table
)
SELECT c1.PORTFOLIO,c1.[DATE],c1.ONE_MONTH_RETURN,
CASE WHEN c2.PORTFOLIO IS NULL THEN NULL ELSE dbo.GETCompoundingReturn(c1.PORTFOLIO,c2.[DATE],c1.[DATE])-1 END
FROM  CTE c1
LEFT JOIN CTE c2
ON c2.PORTFOLIO = c1.PORTFOLIO
AND c2.Seq = c1.Seq-2




------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

Kapital123
Starting Member

31 Posts
Posted - 2013-06-13 : 23:53:04
Unfortunately when I make that amendment you suggested, I get the following error:

Msg 8114, Level 16, State 5, Line 1
Error converting data type varchar to numeric.



quote:
Originally posted by visakh16

i think i got the issue. can you make the code like this and see?


;With CTE
AS
(
SELECT ROW_NUMBER() OVER (PARTITION BY PORTFOLIO ORDER BY [DATE] DESC) AS Seq, *
FROM Table
)
SELECT c1.PORTFOLIO,c1.[DATE],c1.ONE_MONTH_RETURN,
CASE WHEN c2.PORTFOLIO IS NULL THEN NULL ELSE dbo.GETCompoundingReturn(c1.PORTFOLIO,c2.[DATE],c1.[DATE])-1 END
FROM  CTE c1
LEFT JOIN CTE c2
ON c2.PORTFOLIO = c1.PORTFOLIO
AND c2.Seq = c1.Seq-2




------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2013-06-14 : 00:39:01
Are you sure ONE_MONTH_RETURN doesnt contain any spurious value ie values other than number and % symbol?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2013-06-14 : 00:43:54
first try this and post back the result


SELECT ONE_MONTH_RETURN 
FROM Table
WHERE ISNUMERIC(REPLACE(ONE_MONTH_RETURN,'%',''))=0


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

Kapital123
Starting Member

31 Posts
Posted - 2013-06-14 : 01:21:12
Returned just the header title i.e. ONE_MONTH_RETURN...no active cells


quote:
Originally posted by visakh16

first try this and post back the result


SELECT ONE_MONTH_RETURN 
FROM Table
WHERE ISNUMERIC(REPLACE(ONE_MONTH_RETURN,'%',''))=0


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2013-06-14 : 01:35:22
what about this?


CREATE FUNCTION GETCompoundingReturn
(
@PORTFOLIO varchar(10),
@STARTDATE datetime,
@ENDDATE datetime
)
RETURNS Numeric(20,3)
AS
BEGIN
DECLARE @CompRet Numeric(20,3)

SELECT @CompRet = COALESCE(@CompRet,1) * (1 + 
CAST(REPLACE(ONE_MONTH_RETURN,'%','') AS numeric(20,3)) )
FROM Table
WHERE PORTFOLIO = @PORTFOLIO
AND [DATE] > = @STARTDATE
AND [DATE] < @ENDDATE + 1

RETURN (@CompRet)
END

Then call it like
;With CTE
AS
(
SELECT ROW_NUMBER() OVER (PARTITION BY PORTFOLIO ORDER BY [DATE]) AS Seq, *
FROM Table
)
SELECT c1.PORTFOLIO,c1.[DATE],c1.ONE_MONTH_RETURN,
CASE WHEN c2.PORTFOLIO IS NULL THEN CAST(NULL AS Numeric(20,3)) ELSE dbo.GETCompoundingReturn(c1.PORTFOLIO,c2.[DATE],c1.[DATE])-1 END
FROM  CTE c1
LEFT JOIN CTE c2
ON c2.PORTFOLIO = c1.PORTFOLIO
AND c2.Seq = c1.Seq-2


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

Kapital123
Starting Member

31 Posts
Posted - 2013-06-14 : 01:43:12
Got this error again:

Msg 8114, Level 16, State 5, Line 1
Error converting data type varchar to numeric.

quote:
Originally posted by visakh16

what about this?


CREATE FUNCTION GETCompoundingReturn
(
@PORTFOLIO varchar(10),
@STARTDATE datetime,
@ENDDATE datetime
)
RETURNS Numeric(20,3)
AS
BEGIN
DECLARE @CompRet Numeric(20,3)

SELECT @CompRet = COALESCE(@CompRet,1) * (1 + 
CAST(REPLACE(ONE_MONTH_RETURN,'%','') AS numeric(20,3)) )
FROM Table
WHERE PORTFOLIO = @PORTFOLIO
AND [DATE] > = @STARTDATE
AND [DATE] < @ENDDATE + 1

RETURN (@CompRet)
END

Then call it like
;With CTE
AS
(
SELECT ROW_NUMBER() OVER (PARTITION BY PORTFOLIO ORDER BY [DATE]) AS Seq, *
FROM Table
)
SELECT c1.PORTFOLIO,c1.[DATE],c1.ONE_MONTH_RETURN,
CASE WHEN c2.PORTFOLIO IS NULL THEN CAST(NULL AS Numeric(20,3)) ELSE dbo.GETCompoundingReturn(c1.PORTFOLIO,c2.[DATE],c1.[DATE])-1 END
FROM  CTE c1
LEFT JOIN CTE c2
ON c2.PORTFOLIO = c1.PORTFOLIO
AND c2.Seq = c1.Seq-2


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2013-06-14 : 02:01:45
Are you getting the error while creating the function or are you getting in the second code starting With...

also what are the datatypes of PORTFOLIO ,[DATE] and ONE_MONTH_RETURN?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

Kapital123
Starting Member

31 Posts
Posted - 2013-06-14 : 02:06:02
The function executes successfully. I get the error when I run the second piece of code (i.e. WITH...)

As for datatypes: PORTFOLIO is nvarchar, [Date] is datetime and ONE_MONTH_RETURN is a float.

quote:
Originally posted by visakh16

Are you getting the error while creating the function or are you getting in the second code starting With...

also what are the datatypes of PORTFOLIO ,[DATE] and ONE_MONTH_RETURN?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2013-06-14 : 02:16:42
it may be due to the dummy values. Can you try making them NULL and then applying the code?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

Kapital123
Starting Member

31 Posts
Posted - 2013-06-16 : 22:06:38
So I changed all dummy values to NULL and it produces the same error:

Msg 8114, Level 16, State 5, Line 1
Error converting data type varchar to numeric.

This is proving to be a difficult feat. Maybe its not worth doing this in SQL.

quote:
Originally posted by visakh16

it may be due to the dummy values. Can you try making them NULL and then applying the code?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
    Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site