Calculate NAV in SQL server

Question

0.00/5 (No votes)

See more:

Hi, I want to calculate the NAV to start with 100
Below is the formula.

Column B = Col B+ (Col B*Col A)

Example:

Col A          Col B( to be Generate)
0.0000        100
0.0160        101.595
-0.0015       101.446
0.0103        102.492
0.0003        102.519
0.0009        102.611

Hope you can understand the question.

Thanks in Advance

What I have tried:

select Returns,
(100+(100*DailyReturns)) as NAV,
InsertedDate
from tblPortfolio
Order by InsertedDate

Posted 11-Apr-22 2:44am

Shubham Singh Feb2022

Updated 12-Apr-22 10:36am

CHill60

v2

Add a Solution

Comments

OriginalGriff 11-Apr-22 10:08am

This is not a good question - we cannot work out from that little what you are trying to do.
Remember that we can't see your screen, access your HDD, or read your mind - we only get exactly what you type to work with - we get no other context for your project.
Imagine this: you go for a drive in the country, but you have a problem with the car. You call the garage, say "it broke" and turn off your phone. How long will you be waiting before the garage arrives with the right bits and tools to fix the car given they don't know what make or model it is, who you are, what happened when it all went wrong, or even where you are?

That's what you've done here. So stop typing as little as possible and try explaining things to people who have no way to access your project!

Use the "Improve question" widget to edit your question and provide better information.

2 solutions

Solution 1

It's not clear in your question, but the results suggest that you want the previous value in Column B to fit into the calculation for each row.

The function you need is LAG (Transact-SQL) - SQL Server | Microsoft Docs[^]

In order to get that to work you will need something to order the rows by. In the example below I've added an Identity column which implies an order to the table

SQL

declare @demo table (id int identity(1,1), ColA decimal(15,4), ColB decimal(15,4));
insert into @demo (ColA, ColB) values
(0.0000,100),
(0.0160,101.595),
(-0.0015,101.446),
(0.0103,102.492),
(0.0003,102.519),
(0.0009,102.611);

select id, ColA, ColB, LAG(ColB,1,100) OVER (Order by ID) as PrevB
from @demo

The results show that I get the previous value of Column B or 100 if there is no previous value

id	ColA	ColB	PrevB
1	0.0000	100.0000	100.0000
2	0.0160	101.5950	100.0000
3	-0.0015	101.4460	101.5950
4	0.0103	102.4920	101.4460
5	0.0003	102.5190	102.4920
6	0.0009	102.6110	102.5190

As you are going to use that PrevB more than once, I suggest putting the results above into a Common Table Expression, temporary table or sub-query. E.g.

SQL

;with CTE AS
(
	select id, ColA, ColB, LAG(ColB,1,100) OVER (Order by ID) as PrevB
	from @demo
)
select ColA, ColB =  PrevB + (PrevB * ColA)
from cte;

Posted 11-Apr-22 4:00am

CHill60

Comments

Maciej Los 12-Apr-22 16:09pm

Excellent!

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Maciej Los · Accepted Answer · 2022-04-12T10:36:00

Another way is to use CTE[^]. See:

SQL

DECLARE @initialB DECIMAL(15,4) = 100.0000;

;WITH CTE AS
(
  --initial part
  SELECT ColID, ColA, CAST(@initialB AS DECIMAL(15,4)) AS ColB 
  FROM MyData
  WHERE ColID = 1
  --recursive part
  UNION ALL
  SELECT MD.ColID, MD.ColA, CAST(C.ColB + (C.ColB * MD.ColA) AS DECIMAL(15,4)) AS ColB
  FROM MyData AS MD
  INNER JOIN CTE AS C ON MD.ColID > C.ColID
  WHERE MD.ColID - C.ColID = 1
)
SELECT *
FROM CTE;

Result:

ColID 	ColA 	 	ColB
1 	 	0.0000 	 	100.0000
2 	 	0.0160 	 	101.6000
3 	 	-0.0015 	101.4476
4 	 	0.0103 	 	102.4925
5 	 	0.0003 	 	102.5232
6 	 	0.0009 	 	102.6155

Source: SQL Server 2019 | db<>fiddle[^]

Note: To be able to achieve that, you need to define an order of your rows. I'm using IDENTITY field to get auto increment value.