# <急>求一个存储过程

liu234415261 2009-04-07 04:42:43

1 2009-04-07 100
2 2009-04-07 -200
3 2009-04-07 50
4 2009-04-06 100
5 2009-04-04 100
6 2009-04-06 -10

2009-04-04 100 0 借方 100
2009-04-06 100 10 借方 190
2009-04-07 150 200 贷方 140
==================================================

1.写一个存储过程，此存储过程有两个参数为日期型参数。
2.汇总显示出在此时间段的每一天的借方金额，贷方金额。
3.金额为正为借方，为负为贷方。
4.在每一个日期汇总一面有一行合计，

5。余额只显示正数
6.余额为正时方向显示借，负显示贷
7。最开始大期初余额统一为0

...全文
84 2 打赏 收藏 转发到动态 举报

2 条回复

ChinaJiaBing 2009-04-07
• 打赏
• 举报

``````
declare @表 table (编号 int,日期 datetime,金额 int)
insert into @表 select 1,'2009-04-07',100
union all  select 2,'2009-04-07',-200
union all  select 3,'2009-04-07',50
union all  select 4,'2009-04-06',100
union all  select 5,'2009-04-04',100
union all  select 6,'2009-04-06',-10
select 日期,借方金额= sum(case when 金额>0 then 金额 else 0 end),
贷方金额=sum(case when 金额<0  then 金额 else 0 end),
余额=(select SUM(金额)  from @表 where 日期<=a.日期 )
from @表 a group by 日期

----------------------- ----------- ----------- -----------
2009-04-04 00:00:00.000 100         0           100
2009-04-06 00:00:00.000 100         -10         190
2009-04-07 00:00:00.000 150         -200        140

(3 行受影响)

``````
mugua604 2009-04-07
• 打赏
• 举报

``````
create table table1(id int,code varchar(1),qty int)
insert into table1 values(1,'A',100)

create table table2(id int,ddate datetime,[type] varchar(10),code varchar(1),qty int)
insert into table2 values(1,'2009-03-02','入库','A',100 )
insert into table2 values(2,'2009-03-03','入库','A',50 )
insert into table2 values(3,'2009-03-04','出库 ','A',150 )
insert into table2 values(4,'2009-03-06','入库','A',20 )
insert into table2 values(5,'2009-03-09','出库 ','A',30 )

SELECT * FROM table1

select code,ddate=null,qcqty=qty,rkqty=0,ckqty=0,jcqty=qty
from table1
union all
SELECT a.code,a.ddate
,qcqty=b.qty+isnull((select sum(case when [type] ='入库' then qty else -1*qty end) from table2 where ddate<a.ddate),0)
,rkqty= case when [type] ='入库' then a.qty else 0 end
,ckqty= case when [type] ='入库' then 0 else a.qty end
,jcqty=(select sum(qty) from table1)+(select sum(case when [type] ='入库' then qty else -1*qty end) from table2 where ddate<=a.ddate)
FROM table2 a
left join table1 b ON a.code = b.code

/*
code ddate                   qcqty       rkqty       ckqty       jcqty
---- ----------------------- ----------- ----------- ----------- -----------
A    NULL                    100         0           0           100
A    2009-03-02 00:00:00.000 100         100         0           200
A    2009-03-03 00:00:00.000 200         50          0           250
A    2009-03-04 00:00:00.000 250         0           150         100
A    2009-03-06 00:00:00.000 100         20          0           120
A    2009-03-09 00:00:00.000 120         0           30          90

(6 行受影响)

*/

``````

22,210

• 近7日
• 近30日
• 至今