我的表中数据如下
科目代码 代码对应的符号 行号
001 + 1
002 + 1
003 - 2
004 * 3
005 * 3
006 + 4
........现在要写的存储过程 是要实现
1. 按行号 取出相应的科目代码 取出来后进行科目后面的符号运算
2. 若某行只有1条数据 则不进行符号运算
3.每取到1行数据后 从表ht_categorized_accounts_summary中 取出该科目所对应的当天金额 和去年同日金额 进行科目代码后的符号运算
4.打印出每行科目对应的总金额我的存储过程如下 请大家帮忙修改
CREATE PROCEDURE dt_subj_row (@p1 int ,@p2 datetime)
AS
declare @i int
declare @subj_code varchar(16)
declare @flag char(2)
declare @row int
declare @row_count int
declare @today_income numeric(12,2) --当天收入
declare @lastyear_today_income numeric(12,2) --去年同日
declare @month_income numeric(12,2) --本月收入
declare @year_income numeric(12,2) --本年收入
declare @last_time numeric(12,2) --去年同期
declare @growth_rate numeric --增长率declare cur_tmp cursor for
select subj_code,flag,row,(select count(row) from ht_code_flag_table where row=@p1 )
from ht_code_flag_table
where row=@p1
order by row asc
open cur_tmp
Fetch Next From Cur_tmp into @subj_code,@flag,@row,@row_count
WHILE @@FETCH_STATUS = 0
BEGIN
--若@row_count 的计数>1 就进行符号运算
--若该行有2条或2条以上的数据
if @row_count >1
begin
--取今年的数据
select @today_income=rea_amount
from ht_categorized_accounts_summary where subj_code =@subj_code and acc_date =@p2
--取去年同日的数据
select @lastyear_today_income= rea_amount
from ht_categorized_accounts_summary where subj_code =@subj_code and acc_date =dateadd(year,-1,@p2)
exec dt_test_sum @today_income
end
--若该行只有1条数据时 else --取今年的数据
select @today_income=rea_amount
from ht_categorized_accounts_summary
where subj_code =@subj_code and acc_date =@p2
Fetch Next From Cur_tmp into @subj_code,@flag,@row,@row_count
ENDclose cur_tmp
deallocate cur_tmp
GO
科目代码 代码对应的符号 行号
001 + 1
002 + 1
003 - 2
004 * 3
005 * 3
006 + 4
........现在要写的存储过程 是要实现
1. 按行号 取出相应的科目代码 取出来后进行科目后面的符号运算
2. 若某行只有1条数据 则不进行符号运算
3.每取到1行数据后 从表ht_categorized_accounts_summary中 取出该科目所对应的当天金额 和去年同日金额 进行科目代码后的符号运算
4.打印出每行科目对应的总金额我的存储过程如下 请大家帮忙修改
CREATE PROCEDURE dt_subj_row (@p1 int ,@p2 datetime)
AS
declare @i int
declare @subj_code varchar(16)
declare @flag char(2)
declare @row int
declare @row_count int
declare @today_income numeric(12,2) --当天收入
declare @lastyear_today_income numeric(12,2) --去年同日
declare @month_income numeric(12,2) --本月收入
declare @year_income numeric(12,2) --本年收入
declare @last_time numeric(12,2) --去年同期
declare @growth_rate numeric --增长率declare cur_tmp cursor for
select subj_code,flag,row,(select count(row) from ht_code_flag_table where row=@p1 )
from ht_code_flag_table
where row=@p1
order by row asc
open cur_tmp
Fetch Next From Cur_tmp into @subj_code,@flag,@row,@row_count
WHILE @@FETCH_STATUS = 0
BEGIN
--若@row_count 的计数>1 就进行符号运算
--若该行有2条或2条以上的数据
if @row_count >1
begin
--取今年的数据
select @today_income=rea_amount
from ht_categorized_accounts_summary where subj_code =@subj_code and acc_date =@p2
--取去年同日的数据
select @lastyear_today_income= rea_amount
from ht_categorized_accounts_summary where subj_code =@subj_code and acc_date =dateadd(year,-1,@p2)
exec dt_test_sum @today_income
end
--若该行只有1条数据时 else --取今年的数据
select @today_income=rea_amount
from ht_categorized_accounts_summary
where subj_code =@subj_code and acc_date =@p2
Fetch Next From Cur_tmp into @subj_code,@flag,@row,@row_count
ENDclose cur_tmp
deallocate cur_tmp
GO
就頂一下吧,
犹抱琵琶半遮面,ht_categorized_accounts_summary和dt_test_sum都沒說
楼主是不是没把dt_test_sum这个存储过程贴出来
去掉
ht_categorized_accounts_summary表 对应了每个科目具体的当日金额
------------
那上面的store procedure什麼也沒做啊...我看還是貼出2個表的結構和部份數據,然後貼出想得出什麼結果,這樣直接點...
表名 ht_code_flag_table
字段 :code flag row
数据 001 + 1
002 + 1
003 - 2
004 * 3
005 * 3
006 + 4视图名:ht_categorized_accounts_summary
字段:subj_code acc_date rea_amount
001 2002-1-2 47754.77
002 2003-1-2 55.33
003 2004-1-1 222.22存储过程如下 :CREATE PROCEDURE dt_subj_row (@p1 int ,@p2 datetime)
AS
declare @i int
declare @subj_code varchar(16)
declare @flag char(2)
declare @row int
declare @row_count int
declare @today_income numeric(12,2) --当天收入
declare @lastyear_today_income numeric(12,2) --去年同日
declare @month_income numeric(12,2) --本月收入
declare @year_income numeric(12,2) --本年收入
declare @last_time numeric(12,2) --去年同期
declare @growth_rate numeric --增长率declare cur_tmp cursor for
select subj_code,flag,row,(select count(row) from ht_code_flag_table where row=@p1 )
from ht_code_flag_table
where row=@p1
order by row asc
open cur_tmp
Fetch Next From Cur_tmp into @subj_code,@flag,@row,@row_count
WHILE @@FETCH_STATUS = 0
BEGIN
--若@row_count 的计数>1 就进行符号运算
--若该行有2条或2条以上的数据
if @row_count >1begin--取今年的数据
select @today_income=rea_amount
from ht_categorized_accounts_summary where subj_code =@subj_code and acc_date =@p2--取去年同日的数据
select @lastyear_today_income= rea_amount
from ht_categorized_accounts_summary where subj_code =@subj_code and acc_date =dateadd(year,-1,@p2)---这里该如何处理 当日的金额?比如该行有两条数据 而符号是+的话 要把这2条数据加起来送给
@today_income 如果符号是-的话 就执行-操作
end--若该行只有1条数据时else--取今年的数据
select @today_income=rea_amount
from ht_categorized_accounts_summary
where subj_code =@subj_code and acc_date =@p2
Fetch Next From Cur_tmp into @subj_code,@flag,@row,@row_count
ENDclose cur_tmp
deallocate cur_tmp
GO
select A.subj_code,A.flag,A.row,(select count(row) from ht_code_flag_table where row=@p1 ),
B.rea_amount today_income,C.rea_amount lastyear_today_income,
(CASE
WHEN A.flag='+' AND B.rea_amount IS NOT NULL AND C.rea_amount IS NOT NULL THEN C.rea_amount+B.rea_amount
WHEN A.flag='-' AND B.rea_amount IS NOT NULL AND C.rea_amount IS NOT NULL THEN C.rea_amount-B.rea_amount
WHEN A.flag='*' AND B.rea_amount IS NOT NULL AND C.rea_amount IS NOT NULL THEN C.rea_amount*B.rea_amount
WHEN A.flag='/' AND B.rea_amount IS NOT NULL AND C.rea_amount IS NOT NULL THEN C.rea_amount/B.rea_amount
WHEN B.rea_amount IS NULL OR C.rea_amount IS NULL THEN ISNULL(B.rea_amount,C.rea_amount)
END) LAST_income
from ht_code_flag_table A
left join rea_amount B ON A.subj_code=B.subj_code AND B.acc_date =@p2
left join rea_amount C ON A.subj_code=C.subj_code AND C.acc_date =dateadd(year,-1,@p2)
WHERE A.row=@p1
@today_income 如果符号是-的话 就执行-操作就这个?
(CASE
WHEN ,@flag='+' AND @today_income IS NOT NULL AND @lastyear_today_income IS NOT NULL THEN @today_income +@lastyear_today_income
WHEN ,@flag='-' AND @today_incom IS NOT NULL AND @lastyear_today_income IS NOT NULL THEN @lastyear_today_income-@today_incom
WHEN ,@flag='*' AND @today_incom IS NOT NULL AND @lastyear_today_income IS NOT NULL THEN @lastyear_today_income*@today_incom
WHEN ,@flag='/' AND @today_incom IS NOT NULL AND @lastyear_today_income IS NOT NULL THEN @lastyear_today_income/@today_incom
WHEN @today_incom IS NULL OR @lastyear_today_income IS NULL THEN ISNULL(@today_incom,@lastyear_today_income)
END)注意,@today_incom 与 @lastyear_today_income 赋值之前先值NULL 或者其他固定数字~
该变量在游标内部每次+,@today_incom 出了游标就是你要的
这里是不对的
我要的只是计算@today_income 假如 第1行有2条数据 符号是+
就计算:
第1个@today_income的值+第2个@today_income的值
(可能还需要 @total_today_income ,用来记录到目前为止 @today_income 计算总值)处理方式,只不过是把 @pre_today_income 不为默认值是,对@today_income 跟@pre_today_income 进行计算。
然后@pre_today_income =@today_income (或者@total_today_income 。。)