业务场景
TA表中有比例字段及付款间隔字段;TB表中有月份、合同额、应付款字段。
例如付款间隔为2个月,付款比例为80%,则
3月份应付款=1月份合同额*80%
4月份应付款=2月份合同额*80%
5月份应付款=3月份合同额*80%
6月份应付款=4月份合同额*80%
… …
ALTER PROCEDURE PROC_BI_HTEYC(@DWID VARCHAR(36),@ND VARCHAR(4),@QJ VARCHAR(2))
AS
BEGIN
DECLARE @BL FLOAT --比例
DECLARE @JG INT --间隔
DECLARE @CURDATE DATETIME --当前日期
DECLARE @HTE FLOAT --合同额
SELECT @BL =CBL,@JG = CJG FROM TA WHERE CMPID = @DWID AND YEAR = @ND AND DATE = @QJ
DECLARE CUR_LOOP CURSOR FOR
SELECT CDATE,CFK FROM TB WHERE CMPID = @DWID AND YEAR = @ND AND DATE = @QJ ORDER BY DATA_KEY
OPEN CUR_LOOP
FETCH NEXT FROM CUR_LOOP INTO @CURDATE,@HTE
WHILE @@FETCH_STATUS = 0
BEGIN
UPDATE TB SET CYFK = @HTE*@BL WHERE DATEDIFF(MONTH,@CURDATE,CDATE)=@JG AND CMPID = @DWID AND YEAR = @ND AND DATE = @QJ
FETCH NEXT FROM CUR_LOOP INTO @CURDATE,@HTE
END
CLOSE CUR_LOOP
BEGIN
EXEC(‘SELECT CYFK FROM TB WHERE YEAR=’’’+@ND+’’’ and DATE=’’’+@QJ+’’’ AND CMPID = ‘’’+@DWID+’’’ ORDER BY DATA_KEY’)
END
END