业务需求: 根据当前期次,累计之前的该字段的值,即至上期累计(比如现在是8月的一条数据,则累计八月之前的,在比如是七月的一条数据,则累计七月之前的累计)
GO
CREATE FUNCTION dbo.func_date_get_DNje(@date_P_HT_ID varchar(8),@date_F_DEPT_ID varchar(8),@date_F_TJNF varchar(8),@date_F_TJYF varchar(8))
RETURNS varchar(20)
as
BEGIN
declare @result_DNje decimal
SELECT
@result_DNje=ISNULL(SUM(ISNULL(F_JFKDJE, 0.00)),0.00)
from TE_HTGL_JFBL
WHERE (F_EFFECTIVE = 1)
and F_HT_ID=@date_P_HT_ID
and F_DEPT_ID=@date_F_DEPT_ID
and F_TJNF=@date_F_TJNF
and F_TJYF < @date_F_TJYF
RETURN @result_DNje
END
GO
CREATE FUNCTION dbo.func_date_get_WNje(@date_P_HT_ID varchar(8),@date_F_DEPT_ID varchar(8),@date_F_TJNF varchar(8))
RETURNS varchar(20)
as
BEGIN
declare @result_WNje decimal
SELECT
@result_WNje=ISNULL(SUM(ISNULL(F_JFKDJE, 0.00)),0.00)
from TE_HTGL_JFBL
WHERE (F_EFFECTIVE = 1)
and F_HT_ID=@date_P_HT_ID
and F_DEPT_ID=@date_F_DEPT_ID
and F_TJNF< @date_F_TJNF
RETURN @result_WNje
END
GO
DECLARE MyCursor CURSOR FOR
SELECT F_ID,F_HT_ID,F_DEPT_ID,F_TJNF,F_TJYF FROM TE_HTGL_JFBL
OPEN MyCursor
declare @date_P_HT_ID nvarchar(50),@date_F_DEPT_ID nvarchar(50),@date_F_TJNF nvarchar(50),@date_F_TJYF nvarchar(50),@date_F_ID nvarchar(50)
fetch next from MyCursor into @date_F_ID,@date_P_HT_ID,@date_F_DEPT_ID,@date_F_TJNF,@date_F_TJYF
while @@FETCH_STATUS = 0
begin
declare @sumje nvarchar(50)
select @sumje = cast(dbo.func_date_get_DNje(@date_P_HT_ID,@date_F_DEPT_ID,@date_F_TJNF,@date_F_TJYF) as decimal)+cast(dbo.func_date_get_WNje(@date_P_HT_ID,@date_F_DEPT_ID,@date_F_TJNF) as decimal)
update TE_HTGL_JFBL set F_ZSQKDJE = @sumje WHERE F_ID=@date_F_ID
fetch next from MyCursor into @date_F_ID,@date_P_HT_ID,@date_F_DEPT_ID,@date_F_TJNF,@date_F_TJYF
end
close Mycursor
deallocate Mycursor
DROP FUNCTION func_date_get_DNje;
DROP FUNCTION func_date_get_WNje;