棘手的SQL问题 [英] tough SQL question
问题描述
这与自联接有关，包括运行平衡问题。
单独使用SQL是否可以做到这一点？
摊还时间表
--------------------------
Givens：期初余额，付款金额，期数，费率每个
期间
时间表将包括余额列，已支付本金，
支付利息，支付数量
产出计算将是，有效支付b $ b余额，支付 - （每期费率*余额），每期费率*
余额，行号
这可以在Access SQL中完成吗？
谢谢，
Elaine
This has to do with self-joins and includes a running balance problem.
Is it possible to do this using SQL alone?
Amortization schedule
--------------------------
Givens: beginning balance, payment amount, # of periods, rate per
period
schedule would consist of columns for balance, principal paid,
interest paid, payment number
output calculation would be, effectively
balance, payment - (rate per period * balance), rate per period *
balance, row number
Can this be done in Access SQL?
Thanks,
Elaine
推荐答案
为什么不使用报告？您可以在没有
问题的情况下创建运行总和。我想你可以在一个查询中创建运行总和 - 我想约翰
Winterbottom不久前发布了SQL。但是在报告中要做的更容易
。
Why not use a report? You can create running sums there without a
problem. I think you can create running sums in a query - I think John
Winterbottom posted the SQL some time ago. But it would be MUCH easier
to do in a report.
2005年3月21日星期一14:15:08 -0900，Elaine
< no ****************** @ commerce.state.ak.us>写道：
On Mon, 21 Mar 2005 14:15:08 -0900, Elaine
<no******************@commerce.state.ak.us> wrote:
这与自联接有关，包括运行平衡问题。
是否可以单独使用SQL来执行此操作？
摊还时间表
--------------------------
Givens：期初余额，付款金额，＃期间，每个期间的费率
付款金额是多余的。它总是可以从
其他三个确定，但是现在让我们假设它是可用的。
计划将包括余额列，支付本金，
支付利息，支付数量
产出计算，有效平衡，支付 - （每期费率*余额），每期费率* 余额，行号
这可以在Access SQL中完成吗？
This has to do with self-joins and includes a running balance problem.
Is it possible to do this using SQL alone?
Amortization schedule
--------------------------
Givens: beginning balance, payment amount, # of periods, rate per
period
payment amount is superfluous. it can always be determined from the
other three, but for now let''s assume that it is available.
schedule would consist of columns for balance, principal paid,
interest paid, payment number
output calculation would be, effectively
balance, payment - (rate per period * balance), rate per period *
balance, row number
Can this be done in Access SQL?
你是否愿意有一个临时表来播种
行？如果是这样的话，让我们假设你想要一个记录集返回一行有60行的
贷款。
选择expr1，expr2，expr3，payment_number来自诱惑哪里
temptable.payment_number< numberofperiods + 1
其中：
numberofperiods = 60
expr1是余额
expr2 = payment_amount - rate_per_period * expr1
expr3 = payment_amount - expr2
因此，这归结为
$ b $余额的确定b期间的开始。
余额是一个简单的数学方程式，是我几天前公布的公式的变化。
。 />
余额= payment_amount *（（1 - （1 + i）^ - n）/（i））其中：
i = rate_per_period
n =付款剩余（这由number_of_payments决定 -
payment_number
根据您使用的访问版本，您可以使用一个
断开连接的记录集来创建种子，所以你可以完全避开临时表。
迈克
Are you willing to have a temporary table available for seeding the
rows? If so, let''s presume that you want a recordset returned for a
loan with 60 rows.
Select expr1, expr2, expr3, payment_number from temptable where
temptable.payment_number < numberofperiods + 1
where:
numberofperiods = 60
expr1 is the balance
expr2 = payment_amount - rate_per_period * expr1
expr3 = payment_amount - expr2
So, this boils down to the determination of the balance at the
beginning of the period.
The balance is a simple mathematical equation that is a variation on
the formula I posted a couple of days ago.
Balance = payment_amount * ((1 - (1+i)^-n)/(i)) where:
i = rate_per_period
n = payments left (this is determined by number_of_payments -
payment_number
Depending on the version of access you are using, you can use a
disconnected recordset to create the seeds, so you can avoid the temp
table completely.
mike
Mike，
付款金额是*不是*多余的unle ss想要重新计算
每行的付款金额，这将是一笔非常多的b
计算。
~ ~~~~
这是一个VB代码片段，它从
给出参数产生摊销计划：
''建立期初余额
fBal = pv
''
for i = 1 to fNPers
fInt = fRatePer * fBal
fPrinc = fPmt - fInt
如果fBal< fPrinc然后''调整最终付款金额
fPrinc = fBal
结束如果
Debug.Print格式（fBal，货币）& ; Chr（9）&格式（fPrinc，
" Currency"）& Chr（9）&格式（fInt，货币）& Chr（9）& CStr（i）
fBal = fBal - fPrinc
下一页我是
~~~~~
而且，不，我不想要临时桌子。我想知道是否可以使用SQL来执行此操作，并且可以执行此操作。
ADO记录集会起作用，但是再说一遍，我对
a解决方案并不感兴趣，这不仅仅是SQL。
感谢您的投入。
2005年3月22日星期二00:08:58 GMT， mb******@pacbell.net.inva 盖子（迈克
普雷斯顿）写道：
Mike,
Payment amount is *not* superfluous unless one wants to recalculate
the payment amount for every row, which would be an awful lot of
calculations.
~~~~~
Here is a VB code fragment that produces an amortization schedule from
given parameters:
'' establish beginning balance
fBal = pv
''
For i = 1 To fNPers
fInt = fRatePer * fBal
fPrinc = fPmt - fInt
If fBal < fPrinc Then '' adjust final payment amount
fPrinc = fBal
End If
Debug.Print Format(fBal, "Currency") & Chr(9) & Format(fPrinc,
"Currency") & Chr(9) & Format(fInt, "Currency") & Chr(9) & CStr(i)
fBal = fBal - fPrinc
Next i
~~~~~
And, no, I don''t want a temporary table. I want to know whether it is
possible to do this using SQL only, having the givens to procede from.
An ADO recordset would work swell, but, again, I am not interesting in
a solution which is not SQL only.
Thanks for your input.
On Tue, 22 Mar 2005 00:08:58 GMT, mb******@pacbell.net.invalid (Mike
Preston) wrote:
2005年3月21日星期一14:15:08 -0900，Elaine
<无****************** @ commerce.state.ak.us>写道：
On Mon, 21 Mar 2005 14:15:08 -0900, Elaine
<no******************@commerce.state.ak.us> wrote:
这与自联接有关，包括运行平衡问题。
是否可以单独使用SQL来执行此操作？
摊还时间表
--------------------------
Givens：期初余额，付款金额，＃期间，每个期间的费率
This has to do with self-joins and includes a running balance problem.
Is it possible to do this using SQL alone?
Amortization schedule
--------------------------
Givens: beginning balance, payment amount, # of periods, rate per
period
付款金额是多余的。它总是可以从其他三个确定，但是现在我们假设它是可用的。
payment amount is superfluous. it can always be determined from the
other three, but for now let''s assume that it is available.
时间表将包括余额列，已支付本金，
支付利息，支付数量
产出计算，有效平衡，支付 - （每期费率*余额），每期费率* 平衡，行号
这可以在Access SQL中完成吗？
schedule would consist of columns for balance, principal paid,
interest paid, payment number
output calculation would be, effectively
balance, payment - (rate per period * balance), rate per period *
balance, row number
Can this be done in Access SQL?
你是否愿意有一个临时表来播种
行？如果是这样的话，让我们假设你想要一个记录集返回60行的
贷款。
从temptable中选择expr1，expr2，expr3，payment_number
temptable。 payment_number< numberofperiods + 1
其中：
numberofperiods = 60
expr1是余额
expr2 = payment_amount - rate_per_period * expr1
expr3 = payment_amount - expr2
因此，这可以归结为期间开始时的平衡的确定。
平衡是一个简单的数学方程式，是一个变体。我在几天前发布的公式。
余额= payment_amount *（（1 - （1 + i）^ - n）/（i））其中：
i = rate_per_period
n =剩余付款（由number_of_payments决定 -
payment_number
根据您使用的访问版本，您可以使用断开记录集来创建种子，这样就可以完全避开临时表。
mike
Are you willing to have a temporary table available for seeding the
rows? If so, let''s presume that you want a recordset returned for a
loan with 60 rows.
Select expr1, expr2, expr3, payment_number from temptable where
temptable.payment_number < numberofperiods + 1
where:
numberofperiods = 60
expr1 is the balance
expr2 = payment_amount - rate_per_period * expr1
expr3 = payment_amount - expr2
So, this boils down to the determination of the balance at the
beginning of the period.
The balance is a simple mathematical equation that is a variation on
the formula I posted a couple of days ago.
Balance = payment_amount * ((1 - (1+i)^-n)/(i)) where:
i = rate_per_period
n = payments left (this is determined by number_of_payments -
payment_number
Depending on the version of access you are using, you can use a
disconnected recordset to create the seeds, so you can avoid the temp
table completely.
mike
这篇关于棘手的SQL问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
