存储过程如下
CREATE PROC SP_SDF_FLMXZ_CX
@QSNY CHAR(6), --起始年月
@JSNY CHAR(6), --结束年月
@YJSY VARCHAR(20) --一级商业
AS
BEGIN
--DECLARE @RAND INT
DECLARE @TABLENAME VARCHAR(40)
DECLARE @SQL VARCHAR(2048)
DECLARE @TEM FLOAT
DECLARE @ID INT
DECLARE @MESSAGE VARCHAR(200)
SET NOCOUNT ON
--SET @RAND = FLOOR(RAND()*1000)
SET @TABLENAME = '#TEMP_HFMXZ'+CAST(FLOOR(RAND()*1000) AS CHAR(3))
--创建临时表
SET @SQL = 'CREATE TABLE '+@TABLENAME+
' (
T_YJSY VARCHAR(200),
T_EJSY VARCHAR(200),
T_SJSY VARCHAR(200),
T_ZY VARCHAR(200),
T_RQ VARCHAR(6),
T_DJBH VARCHAR(40),
T_QCJE FLOAT,
T_JF FLOAT,
T_DF FLOAT,
T_YE FLOAT,
T_FLAG CHAR(1),
T_ZDRQ VARCHAR(8),
T_JETEM FLOAT,
T_ID INT IDENTITY(1,1)
)'
EXEC (@SQL)
--创建索引
SET @SQL = 'CREATE UNIQUE INDEX TEMP_HFMXZ ON '+@TABLENAME+' (T_YJSY)'
EXEC (@SQL)
--归集起始日期前的数据作为期初 归集所有计提单-所有返利单
--先计算期初余额
SET @TEM=ISNULL((SELECT SUM(YXFYJHMX_JE)
FROM YXFYJH,YXFYJHMX
WHERE YXFYJH_LSBH=YXFYJHMX_LSBH AND YXFYJH_JHLX='1' AND YXFYJH_JHNY<@QSNY AND YXFYJHMX_KH=@YJSY),0)
-
ISNULL((SELECT SUM(YXHBDMX_HBS)
FROM YXHBD,YXHBDMX
WHERE YXHBD_LSBH=YXHBDMX_LSBH AND YXHBD_FYLX='05' AND SUBSTRING(YXHBD_ZDRQ,1,6)<@QSNY AND YXHBDMX_KH=@YJSY),0)
SET @SQL = 'INSERT INTO '+@TABLENAME+' (T_YJSY,T_EJSY,T_SJSY,T_ZY,T_RQ,T_DJBH,T_QCJE,T_JF,T_DF,T_YE,T_FLAG,T_ZDRQ,T_JETEM)
(
SELECT YXKHZD_KHMC YJMC,'' EJSY,'' SJSY,''期初'' QC,'' RQ,'' DJBH,
@TEM QCJE,0 JF,0 DF,@TEM YE,''0'' FLAG,''00000000'' ZDRQ,@TEM JETEM
FROM YXFYJH,YXHBD,YXHBDMX,YXFYJHMX,YXKHZD
WHERE YXFYJH_LSBH=YXFYJHMX_LSBH
AND YXHBD_LSBH=YXHBDMX_LSBH
AND YXFYJHMX_KH=YXKHZD_KHBH
AND YXFYJH_JHNY<@QSNY
AND YXFYJHMX_KH=@YJSY
GROUP BY YXKHZD_KHMC
)'
EXEC (@SQL)
--归集日期内的返利计提、返利单
SET @SQL = 'INSERT INTO '+@TABLENAME+' (T_YJSY,T_EJSY,T_SJSY,T_ZY,T_RQ,T_DJBH,T_QCJE,T_JF,T_DF,T_YE,T_FLAG,T_ZDRQ,T_JETEM)
SELECT YJMC,EJMC,SJMC,ZY,RQ,DJBH,QCJE,JF,DF,YE,FLAG,ZDRQ,JETEM
FROM
(SELECT YJSY.YXKHZD_KHMC YJMC,EJSY.YXKHZD_KHMC EJMC,SJSY.YXKHZD_KHMC SJMC,
YXFYJHMX_FLMC ZY,YXFYJH_JHNY RQ,YXFYJH_DJBH DJBH,'' QCJE,
YXFYJHMX_JE JF,0 DF,YXFYJHMX_JE YE,''1'' FLAG,YXFYJH_ZDRQ ZDRQ,@TEM JETEM
FROM YXFYJH,YXKHZD YJSY,
YXFYJHMX LEFT JOIN YXKHZD EJSY ON YXFYJHMX_KH2=EJSY.YXKHZD_KHBH
LEFT JOIN YXKHZD SJSY ON YXFYJHMX_KH2=EJSY.YXKHZD_KHBH
WHERE YXFYJH_LSBH=YXFYJHMX_LSBH
AND YXFYJHMX_KH=YJSY.YXKHZD_KHBH
AND YXFYJH_JHLX=''1''
AND YXFYJH_JHNY>=@QSNY
AND YXFYJH_JHNY<=@JSNY
AND YXFYJHMX_KH=@YJSY
UNION ALL
SELECT YJSY.YXKHZD_KHMC YJMC,EJSY.YXKHZD_KHMC EJMC,SJSY.YXKHZD_KHMC SJMC,
YXFLFS_MC ZY,YXHBD_ZDRQ RQ,YXHBD_DJBH DJBH,'' QCJE,
0 JF,0 DF,YXHBDMX_HBS YE,''2'' FLAG,YXHBD_ZDRQ ZDRQ,-@TEM JETEM
FROM YXHBD,YXKHZD YJSY,YXFLFS,
YXHBDMX LEFT JOIN YXKHZD EJSY ON YXHBDMX_KH2=EJSY.YXKHZD_KHBH
LEFT JOIN YXKHZD SJSY ON YXHBDMX_KH3=SJSY.YXKHZD_KHBH
WHERE YXHBD_LSBH=YXHBDMX_LSBH AND YXHBDMX_KH=YJSY.YXKHZD_KHBH
AND YXFLFS_BH=YXHBD_FLFS
AND YXHBD_FYLX=''05''
AND SUBSTRING(YXHBD_ZDRQ,1,6)>@QSNY
AND SUBSTRING(YXHBD_ZDRQ,1,6)<@JSNY
AND YXHBDMX_KH=@YJSY ) A
ORDER BY ZDRQ'
EXEC (@SQL)
--更新余额 使用游标循环处理
DECLARE UPDYE_CURSOR CURSOR FOR
SELECT T_ID FROM #TEMP_HFMXZ
OPEN UPDYE_CURSOR
FETCH NEXT FROM UPDYE_CURSOR INTO @ID
WHILE @@FETCH_STATUS=0
BEGIN
SET @SQL = 'UPDATE '+@TABLENAME+' SET T_YE=SUM(T_JETEM) WHERE T_ID<=@ID'
EXEC (@SQL)
IF @@ERROR<>0
BEGIN
SET @MESSAGE='更新余额出错！'
RAISERROR 50001 @MESSAGE