需要计算并包含在Select语句中 [英] Need to calculate and include in the Select statement

问题描述

SQL SERVER - 存储过程

  CREATE   TABLE  #ARREAR 
（
 RIDNO  VARCHAR （ 20 ），
 RLOANUMBER  VARCHAR （ 20 ），
 RCUSTOMER  VARCHAR （ 50 ），
 RMONTH  NUMERIC （ 18 ， 0 ），
 RYEAR1  NUMERIC （ 18 ， 0 ），
 REXP_AMT  NUMERIC （ 18 ， 2 ），
 REXP_PRIN  NUMERIC （ 18 ， 2 ），
 REXP_INT  NUMERIC （ 18 ， 2 ），
 RACT_AMT  NUMERIC （ 18 ， 2 ），
 RACT_PRIN  NUMERIC （ 18 ， 2 ）， 
 RACT_INT  NUMERIC （ 18 ， 2 ），
 RARR_AMT  NUMERIC （ 18 ， 2 ），
 RAVG_PAY  NUMERIC （ 18 ， 2 ），
 RBANKCD  VARCHAR （ 50 ）， 
 RBANKNAME  VARCHAR （ 50 ）
）
 
 
  INSERT   INTO  #ARREAR 
  SELECT ＃VLOANDED.AIDNO  AS  RIDNO， 
＃VLOANDED.ALOANUMBER  AS  RLOANUMBER，
＃VLOANDED.ACUSTOMER  AS  RCUSTOMER， 
  @ CMONTH   AS  RMONTH，
  @CYEAR   AS  RYEAR，
 SUM（＃VLOANDED.APAY_AMT） AS  REXP_AMT，
 SUM（＃VLOANDED.APRINR_PMT） AS  REXP_PRIN，
 SUM（＃VLOANDED.AINT_PMT） AS  REXP_INT，
 SUM（ CASE   WHEN  #VTRANS.BTRANS_TYPE = < span class =code-string>'  TOTAL REPAYMENT' 那么 BAMOUNT  ELSE   0   END ） AS  RACT_AMT，
 SUM （ CASE   WHEN  #VTRANS.BTRANS_TYPE = '  PRINCIPAL REPAYMENT'  THEN  BAMOUNT  ELSE  < span class =code-digit> 0   END ） AS  RACT_PRIN，
 SUM（ CASE   WHEN  #VTRANS.BTRANS_TYPE = '  INTEREST REPAYMENT' 那么 BAMOUNT  ELSE   0   END ） AS  RACT_INT，
 AVG（＃VLOANDED.APAY_AMT） AS  RAVG_PAY， 
＃VLOANDED.ABANKCD  AS  RBANKCD，
＃VLOANDED.ABANKNAME  AS  RBANKNAME 
  FROM  #VLOANDED  JOIN  #VTRANS 
  ON ＃VLOANDED.AIDNO =＃VTRANS.BIDNO  AND ＃VLOANDED.ALOANUMBER =＃VTRANS.BLOANUMBER 
  GROUP   BY ＃VLOANDED.AIDNO，＃VLOANDED.ALOANUMBER 
  ORDER   BY ＃VLOANDED.AIDNO，＃VLOANDED.ALOANUMBER 

需要在 INSERT INTO表中计算并包含（REXP_AMT-RACT_AMT）AS RARR_AMT #ARREAR



你是如何做到这一点的？



谢谢

解决方案

您可以在INSERT INTO #ARREA下面编写外部选择语句R。

两种方式：



1）将查询用作子查询：

  INSERT   INTO  #ARREAR（< Destination_Fields>）
  SELECT < SourceFields>，（REXP_AMT  -  RACT_AMT） AS  RARR_AMT 
  FROM （
   -   您的查询 
） AS  T 

SQL：INSERT [ ^ ]



或

2）更新您的数据插入新的后：

 更新 #ARARAR  SET  RARR_AMT = REXP_A MT  -  RACT_AMT 

SQL：更新 [ ^

SQL SERVER - STORED PROCEDURE

CREATE TABLE #ARREAR
(
RIDNO VARCHAR(20),
RLOANUMBER VARCHAR(20),
RCUSTOMER VARCHAR(50),
RMONTH NUMERIC(18,0),
RYEAR1 NUMERIC(18,0),
REXP_AMT NUMERIC(18,2),
REXP_PRIN NUMERIC(18,2),
REXP_INT NUMERIC(18,2),
RACT_AMT NUMERIC(18,2),
RACT_PRIN NUMERIC(18,2),
RACT_INT NUMERIC(18,2),
RARR_AMT NUMERIC(18,2),
RAVG_PAY NUMERIC(18,2),
RBANKCD VARCHAR(50),
RBANKNAME VARCHAR(50)
)


INSERT INTO #ARREAR 
SELECT #VLOANDED.AIDNO AS RIDNO ,
#VLOANDED.ALOANUMBER AS RLOANUMBER ,
#VLOANDED.ACUSTOMER AS RCUSTOMER ,
@CMONTH AS RMONTH ,
@CYEAR AS RYEAR ,
SUM( #VLOANDED.APAY_AMT) AS REXP_AMT ,
SUM( #VLOANDED.APRINR_PMT) AS REXP_PRIN ,
SUM( #VLOANDED.AINT_PMT) AS REXP_INT ,
SUM( CASE WHEN #VTRANS.BTRANS_TYPE = 'TOTAL REPAYMENT' THEN BAMOUNT ELSE 0 END ) AS RACT_AMT ,
SUM( CASE WHEN #VTRANS.BTRANS_TYPE = 'PRINCIPAL REPAYMENT' THEN BAMOUNT ELSE 0 END ) AS RACT_PRIN ,
SUM( CASE WHEN #VTRANS.BTRANS_TYPE = 'INTEREST REPAYMENT' THEN BAMOUNT ELSE 0 END ) AS RACT_INT ,
AVG(#VLOANDED.APAY_AMT) AS RAVG_PAY , 
#VLOANDED.ABANKCD AS RBANKCD ,
#VLOANDED.ABANKNAME AS RBANKNAME 
FROM #VLOANDED JOIN #VTRANS
ON #VLOANDED.AIDNO =#VTRANS.BIDNO AND #VLOANDED.ALOANUMBER =#VTRANS.BLOANUMBER 
GROUP BY #VLOANDED.AIDNO , #VLOANDED.ALOANUMBER 
ORDER BY #VLOANDED.AIDNO , #VLOANDED.ALOANUMBER 

Need to calculate and include (REXP_AMT-RACT_AMT) AS RARR_AMT in the INSERT INTO table #ARREAR

How do you accomplish this?

Thanks

解决方案

You can write outer select statement below "INSERT INTO #ARREAR".

Two ways:

1) Use your query as a subquery:

INSERT INTO #ARREAR (<Destination_Fields>)
SELECT <SourceFields>, (REXP_AMT - RACT_AMT) AS RARR_AMT
FROM (
-- your query here
) AS T

SQL: INSERT[^]

or
2) Update your data just after inserting new one:

UPDATE #ARREAR SET RARR_AMT = REXP_AMT - RACT_AMT

SQL: UPDATE[^]

这篇关于需要计算并包含在Select语句中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！