去除金蝶下挂核算项目重复

DECLARE @ITEM VARCHAR(100)

SET @ITEM='客户'

DECLARE @ITEMNUM VARCHAR(100)
select @ITEMNUM=FITEMCLASSID from t_itemclass WHERE FNAME=@ITEM
SET @ITEMNUM='F'+@ITEMNUM
DECLARE @SQL NVARCHAR(2500)
SET @SQL=N'SELECT T.FC,T.'+@ITEMNUM+',I1.FDETAILID INTO MC_TEMP1 FROM ( SELECT COUNT(1) FC,'+@ITEMNUM+N' FROM T_ITEMDETAIL WHERE '+@ITEMNUM+N'>0   GROUP BY '+@ITEMNUM+') T LEFT JOIN T_ITEMDETAIL I1 ON T.'+@ITEMNUM+'=I1.'+@ITEMNUM+' WHERE FC>1 '
EXEC SP_EXECUTESQL @SQL
--SELECT * FROM MC_TEMP1

SET @SQL=N'SELECT '+@ITEMNUM+', MIN(FDETAILID) MINF INTO MC_TEMP2 FROM MC_TEMP1 GROUP BY '+@ITEMNUM
EXEC SP_EXECUTESQL @SQL
--SELECT * FROM MC_TEMP2
SET @SQL='DECLARE Cur1 CURSOR FOR select '+@ITEMNUM+',MINF from MC_TEMP2'
EXEC SP_EXECUTESQL @SQL
DECLARE @FID INT
DECLARE @FDID INT
OPEN Cur1
FETCH NEXT FROM Cur1 into @FID,@FDID
WHILE @@FETCH_STATUS = 0
BEGIN

--SET @SQL=N'UPDATE T_VOUCHERENTRY SET FDETAILID='+cast(@FDID as varchar(10))+N' WHERE FDETAILID IN (SELECT FDETAILID FROM MC_TEMP1 WHERE '+@ITEMNUM+N'='+cast(@FID as varchar(10))+N')'
--EXEC SP_EXECUTESQL @SQL

SET @SQL=N'SELECT '+CAST(@FID AS VARCHAR(10))+' FID ,FYEAR,FPERIOD,FACCOUNTID,FDETAILID,FCURRENCYID,FBEGINBALANCEFOR,FDEBITFOR,FCREDITFOR,FYTDDEBITFOR,FYTDCREDITFOR,FENDBALANCEFOR,FBEGINBALANCE,FDEBIT,FCREDIT,FYTDDEBIT,FYTDCREDIT,FENDBALANCE
INTO MC_TEMP3
FROM T_BALANCE
WHERE FDETAILID IN (SELECT FDETAILID FROM T_ITEMDETAIL WHERE '+@ITEMNUM+'='+CAST(@FID AS VARCHAR(10))+')'
EXEC SP_EXECUTESQL @SQL
--SELECT * FROM MC_TEMP3

SELECT FYEAR,FPERIOD,FACCOUNTID,FCURRENCYID,SUM(FBEGINBALANCEFOR) FBEGINBALANCEFOR,SUM(FDEBITFOR) FDEBITFOR,SUM(FCREDITFOR) FCREDITFOR,SUM(FYTDDEBITFOR) FYTDDEBITFOR,SUM(FYTDCREDITFOR) FYTDCREDITFOR,SUM(FENDBALANCEFOR) FENDBALANCEFOR,SUM(FBEGINBALANCE) FBEGINBALANCE,SUM(FDEBIT) FDEBIT,SUM(FCREDIT) FCREDIT,SUM(FYTDDEBIT) FYTDDEBIT,SUM(FYTDCREDIT) FYTDCREDIT,SUM(FENDBALANCE) FENDBALANCE
INTO MC_TEMP4
FROM MC_TEMP3
GROUP BY FYEAR,FPERIOD,FACCOUNTID,FCURRENCYID
--SELECT * FROM MC_TEMP4

UPDATE T SET T.FBEGINBALANCEFOR=T2.FBEGINBALANCEFOR,T.FDEBITFOR=T2.FDEBITFOR,T.FCREDITFOR=T2.FCREDITFOR,T.FYTDDEBITFOR=T2.FYTDDEBITFOR,T.FYTDCREDITFOR=T2.FYTDCREDITFOR,T.FENDBALANCEFOR=T2.FENDBALANCEFOR,T.FBEGINBALANCE=T2.FBEGINBALANCE,T.FDEBIT=T2.FDEBIT,T.FCREDIT=T2.FCREDIT,T.FYTDDEBIT=T2.FYTDDEBIT,T.FYTDCREDIT=T2.FYTDCREDIT,T.FENDBALANCE=T2.FENDBALANCE
FROM T_BALANCE T LEFT JOIN MC_TEMP4 T2 ON T.FYEAR=T2.FYEAR AND T.FPERIOD=T2.FPERIOD AND T.FACCOUNTID=T2.FACCOUNTID AND T.FCURRENCYID=T2.FCURRENCYID
WHERE T.FDETAILID=@FDID

--SELECT * FROM T_BALANCE WHERE FDETAILID=@FDID
SET @SQL=N'DELETE FROM T_BALANCE WHERE FDETAILID IN (SELECT FDETAILID FROM MC_TEMP1 WHERE '+@ITEMNUM+'='+CAST(@FID AS VARCHAR(10))+') AND  FDETAILID NOT IN (SELECT MINF FROM MC_TEMP2 WHERE '+@ITEMNUM+'='+CAST(@FID AS VARCHAR(10))+')'
EXEC SP_EXECUTESQL @SQL

SET @SQL=N'DELETE FROM T_ITEMDETAIL WHERE FDETAILID IN (SELECT FDETAILID FROM MC_TEMP1 WHERE '+@ITEMNUM+'='+CAST(@FID AS VARCHAR(10))+') AND FDETAILID NOT IN (SELECT MINF FROM MC_TEMP2 WHERE '+@ITEMNUM+'='+CAST(@FID AS VARCHAR(10))+')'
EXEC SP_EXECUTESQL @SQL

DROP TABLE MC_TEMP4
DROP TABLE MC_TEMP3
FETCH NEXT FROM Cur1 into @FID,@FDID
END

CLOSE Cur1
DEALLOCATE Cur1
DROP TABLE MC_TEMP2
DROP TABLE MC_TEMP1

--select * from t_balance
--UPDATE T_BALANCE SET FBEGINBALANCEFOR=SUM(FBEGINBALANCEFOR) WHERE FDETAILID=3824 GROUP BY FYEAR,FPERIOD,FACCOUNTID,FCURRENCYID