例如:单一品种
rq rk xs
20061101 100 10
20061102 0 50
20061104 50 10
20061210 30
。生成的报表的效果:选择时间段从20061104-20061210
期初 入库(rk) 销售(xs) 期末
其中期初和期末是计算出来的,中间时间段不一定每天有数据,
请问怎样实现,以前似乎看到过方法的,这个问题我一直没有搞明白,
谢谢!
rq rk xs
20061101 100 10
20061102 0 50
20061104 50 10
20061210 30
。生成的报表的效果:选择时间段从20061104-20061210
期初 入库(rk) 销售(xs) 期末
其中期初和期末是计算出来的,中间时间段不一定每天有数据,
请问怎样实现,以前似乎看到过方法的,这个问题我一直没有搞明白,
谢谢!
set @sdate='2006-11-04'
set @edate='2006-12-10'select
期初=(select sum(rk-xs) from 表 where rq<@sdate),
入库=(select sum(rk) from 表 where rq between @sdate and @edate),
销售=(select sum(ck) from 表 where rq between @sdate and @edate),
期末=(select sum(rk-xs) from 表 where rq<=@edate)
select sum(rk) as 入库 from tb where rq >='20061104' and rq <='20061210'
select sum(xs) as 销售 from tb where rq >='20061104' and rq <='20061210'
如何把这三个表联合起来就是你的结果了,我不知道怎么搞了.好象要用连接?
Select 期初,入库,销售, 期末=期初+入库-销售 from (
Select 期初=(Select sum(rk-isNULL(xs,0)) from tb where 时间<'20061104'),
入库=sum(rk), 销售=sum(xs)
from tb Where 时间 between '20061104' and '20061210') a
insert into @t select '20061101',100,10
insert into @t select '20061102', 0 ,50
insert into @t select '20061104',50 ,10
insert into @t select '20061210', 0 ,30declare @sdate datetime,@edate datetime
set @sdate='2006-11-04'
set @edate='2006-12-10'select
期初=(select sum(rk-xs) from @t where rq<@sdate),
入库=(select sum(rk) from @t where rq between @sdate and @edate),
销售=(select sum(xs) from @t where rq between @sdate and @edate),
期末=(select sum(rk-xs) from @t where rq<=@edate)/*
期初 入库 销售 期末
----------- ----------- ----------- -----------
40 50 40 50
*/
insert into @t select '20061101',100,10
insert into @t select '20061102', 0 ,50
insert into @t select '20061104',50 ,10
insert into @t select '20061210', 0 ,30declare @sdate datetime,@edate datetime
set @sdate='2006-11-04'
set @edate='2006-12-10'select
日期=a.rq,
期初=(select sum(rk-xs) from @t where rq<a.rq),
入库=sum(rk),
销售=sum(xs),
期末=(select sum(rk-xs) from @t where rq<=a.rq)
from
@t a
where
a.rq between @sdate and @edate
group by
a.rq
/*
日期 期初 入库 销售 期末
------------------------------------------------------ ----------- ----------- ----------- -----------
2006-11-04 00:00:00.000 40 50 10 80
2006-12-10 00:00:00.000 80 0 30 50
*/
from tablename
Where 时间 between '2006-11-04' and '2006-12-10'
先谢谢先