逐年降低成本 [英] Reduce the Cost Year by Year

问题描述

亲爱的朋友们，



我想计算车辆成本，每年减少10％。

如何得出这个查询，附上样本查询



选择'2010'= 600000，'2011'= 600000-（600000 * 10）/ 100，'2012'= 540000-（ 540000 * 10）/ 100，'2013'= 486000-（486000 * 10）/ 100

Dear Friends,

I want to calculate the vehicle cost, reducing the 10% every year.
How to derive this in query, enclosed the sample query

Select '2010'=600000,'2011'=600000-(600000*10)/100,'2012'=540000-(540000*10)/100,'2013'=486000-(486000*10)/100

推荐答案

We know the Formula of compound interest in P(1-r/100)^n<br />
so, just implement this in SQl.

像这样。

Like this.

declare @P as decimal(18,2)
declare @rate as decimal(18,2)
declare @Yr as int
set @p=60000 --initial amount
set @rate=10 --rate of reduce 10%
set @Yr=2010-- starting year is 2010

declare @passYr as int
set @passYr=2011 -- calculating for the year of 2011

declare @res as decimal(18,2)
set @res=0
-- we know the formula of compound interest is p(1-r/100)^n

declare @pw as int
declare @yrD as int
set @yrD=@passYr-@Yr

set @pw=0
while @pw<@yrD -- loop for ^n year 
begin
set @res=(1-@rate/100)
set @pw=@pw+1
End

select (@P*@res)

你可以使用 Power 而不是循环。

参见参考文件

https： //msdn.microsoft.com/en-us/library/ms174276.aspx [ ^ ]

You can just Use Power instead of While Loop .
See Ref.
https://msdn.microsoft.com/en-us/library/ms174276.aspx[^]

您可以使用以下方式完成给定的场景。



这个是每年的减少余额逻辑。因此，如果您给出一年中的一系列范围来计算结果，您可以使用以下内容：

you can use the following way to accomplish the given scenario.

this one is a reducing balance logic for each year. so if you give a range of the year to calculate the result you can use the following:

Declare @startYear int = 2000
		, @endYear int = 2010
		, @amt as int = 60000
		, @RunningTotal as int = 0;

DECLARE @t TABLE
(
  TID INT PRIMARY KEY,  
  pct int,
  RunningTotal int
);


------------- Tally table generation
;With tally
as
(
	select TOP 1000 ROW_NUMBER()Over(Order by (Select NULL)) -1 AS Rn
	from  sys.all_columns c
)


--- insert the data in table 
insert into @t
select @startYear + rn as years, case when rn = 0 then 0 else 10 end pct, 0.0
from tally
Where (@startYear + rn) <= @endYear

----------- Applied Quirky method 
UPDATE @t
  SET @RunningTotal = RunningTotal = (@amt - ((@amt * pct)/100))
			, @amt = @RunningTotal
			, pct = 10
  FROM @t
option (maxdop 0);	--- this will stop parallelism  

----------- Result
select * from @t

For更多了解Quirky请查看此链接。大多数人会说这是一种没有文档记录的方式，但是我在SQL SERVER 2000中使用这种方法直到2012年及其工作。

For more understanding on Quirky check this Link. Most of the people will say this is a undocumented way but i am using this approach from SQL SERVER 2000 till 2012 its and its working.

这篇关于逐年降低成本的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！