-2

Can anyone help me to generate the below attached output in SQL SERVER

Excel Formula to generate NAV

=(B2+(B2*A3))

enter image description here

Shubham Singh
  • 1
  • 1
    welcome to stackoverflow shubham, please let us know you have tried so far. – Bagus Tesa Apr 11 '22 at 12:11
  • Yes, but not able to generate it. – Shubham Singh Apr 11 '22 at 12:12
  • Please edit the question to limit it to a specific problem with enough detail to identify an adequate answer. – Community Apr 11 '22 at 12:14
  • Images of data doesn't help us help you; we can't use it. Provide sampled data in a **consumable** format; preferable DDL and DML statements, but at least well formatted tabular `text` or a markdown table. Also don't forget to include your attempts, and if you haven't tried, then at least show what you've done to try to solve the question: What articles/documentation/tutorials/questions did you read to try and help you solve the problem yourself? What about them didn't you understand? – Thom A Apr 11 '22 at 12:27

1 Answers1

0

I think that you want the function LAG() which gets the value from the previous row.
Please check this example on dbFiddle. Link at the bottom.

select 
  ret "return",
  coalesce(lag(nav) over (order by inserted_date),100) 
  + (ret * coalesce(lag(nav) over (order by inserted_date),100) ) "nav",
  inserted_date
from t
GO

  return |            nav | inserted_date
-------: | -------------: | :------------
 0.00000 | 100.0000000000 | 2022-01-10   
 0.01600 | 101.6000000000 | 2022-01-11   
-0.00150 | 101.4476000000 | 2022-01-12   
 0.01030 | 102.4925102800 | 2022-01-13   
 0.00030 | 102.5232577530 | 2022-01-14   
 0.00090 | 102.6155309340 | 2022-01-15   
 0.00000 | 102.6155300000 | 2022-01-16   
 0.00000 | 102.6155300000 | 2022-01-17   
 0.09700 | 112.5692364100 | 2022-01-18   
-0.01660 | 110.7005906160 | 2022-01-19   
-0.00230 | 110.4459786430 | 2022-01-20   
-0.00260 | 110.1588104780 | 2022-01-21   
-0.02820 | 107.0523412760 | 2022-01-22   
 0.00000 | 107.0523400000 | 2022-01-23   
 0.00000 | 107.0523400000 | 2022-01-24   
-0.04560 | 102.1707532960 | 2022-01-25   
 0.01320 | 103.5194039000 | 2022-01-26   
 0.00000 | 103.5194000000 | 2022-01-27   
-0.01020 | 102.4635021200 | 2022-01-28   
 0.00750 | 103.2319863250 | 2022-01-29   
 0.00000 | 103.2319800000 | 2022-01-30   
 0.00000 | 103.2319800000 | 2022-01-31   
 0.01730 | 105.0178932540 | 2022-02-01   
 0.00360 | 105.3959644400 | 2022-02-02   
 0.01180 | 106.6396323280 | 2022-02-03   
-0.00710 | 105.8824886270 | 2022-02-04

db<>fiddle here

  • `RETURN` is a [reserved keyword](https://learn.microsoft.com/en-us/sql/t-sql/language-elements/reserved-keywords-transact-sql?view=sql-server-ver15). – Thom A Apr 11 '22 at 12:25
  • @Larnu : thanks - it's simply solved but could be a problem for a newcomer. –  Apr 11 '22 at 12:28
  • Backticks (`\``) aren't a delimit identifier in T-SQL though, @Kendle . You need to use brackets (`[]`) or double quotes (`"`). You were also missing a right parenthesis (`)`). – Thom A Apr 11 '22 at 12:29
  • Hi, I want to calculate the NAV to start with 100 Formula to generate 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 – Shubham Singh Apr 11 '22 at 12:40
  • Ok - I've changed to lag. Is that what you want? –  Apr 11 '22 at 12:41
  • The same is not able to generate desired output – Shubham Singh Apr 11 '22 at 12:46
  • I've added the example. Please check the input and output and try it on DBfiddle. –  Apr 12 '22 at 04:26