存储过程中动态累加的问题请教
现有一存储过程如下:
CREATE procedure sp_StockMonthReport @year char(4),@month char(2)
As
declare @sql varchar(8000),@day int,@dd int,@stock_amount int
/*--得到要查询年月的天数
select @dd=day(dateadd(month,@month,dateadd(year,@year-1999,'1999-01-01'))-1)
,@day=1
,@sql='
SELECT stock.tz_year, stock.tz_month, v_book.fashion_code, v_book.size_code,
stock.qc_amount' */
--declare @dd int,@sql varchar(10)
--declare @n datetime
--select @n=Cast(@year+@month+'01' AS DateTime)
--select @n=@year+'-'+@month+'-01'
--SELECT @n='20030901'
--select @dd = datediff(day,@n-day(@n)+1,dateadd(month,1,@n-day(@n)+1))
--先取出期初数量
--Select @Stock_amount=qc_amount from stock where tz_year=@year --andtz_month=@month
--
Select @day=1,@dd=31,@sql='SELECT stock.tz_year, stock.tz_month, v_book.fashion_code, v_book.size_code, stock.qc_amount'
while @day<=@dd
--进库,出库
select @sql=@sql+',SUM( CASE WHEN day(v_book.date)='+cast(@day as varchar) +' AND (v_book.lb=''02'' OR v_book.lb=''01'') THEN Amount ELSE 0 END) AS rk_'+cast(@day as varchar)
+',SUM(CASE WHEN day(v_book.date)='+cast(@day as varchar)
+' AND (v_book.lb=''10'' OR v_book.lb=''11'') THEN Amount ELSE 0 END) AS ck_'
+cast(@day as varchar)
--当日结存=当日期初(stock_amount)+当日入库(rk_1...) - 当日出库(ck_1.....)
--SELECT @sql=@sql+',SUM( CASE WHEN day(v_book.date)='+cast(@day as varchar) +' AND (v_book.lb=''02'' OR v_book.lb=''01'') THEN Amount ELSE 0 END) AS jc_'+cast(@day as varchar)
Select @day=@day+1
set @sql=@sql+' FROM v_book, stock
WHERE (v_book.fashion_code = stock.fashion_code) AND
(v_book.size_code = stock.fashion_size) AND
stock.tz_year='+cast(@year as varchar)
+' and stock.tz_month='+cast(@month as varchar)+'
GROUP BY stock.tz_year, stock.tz_month, v_book.fashion_code, v_book.size_code,
stock.qc_amount'
exec(@sql)
GO
需要在存储过程中生成每日结存数据。如何实现?