select 商品编号=code,上期结存=sum(case when date<'2002-10-12' then amount*type else 0 end),
本期收入=sum(case when date>='2002-10-12' and date<='2002-10-13' and type=1 then amount else 0 end),
本期发出=sum(case when date>='2002-10-12' and date<='2002-10-13' and type=-1 then -amount else 0 end),
本期结存=sum(type*amount)
from 库存跟综表
where date<='2002-10-13'
group by code
本期收入=sum(case when date>='2002-10-12' and date<='2002-10-13' and type=1 then amount else 0 end),
本期发出=sum(case when date>='2002-10-12' and date<='2002-10-13' and type=-1 then -amount else 0 end),
本期结存=sum(type*amount)
from 库存跟综表
where date<='2002-10-13'
group by code
create function GetReport(
@dt1 datetime,
@dt2 datetime)
returns table
as
return(
select 商品编号=code,上期结存=sum(case when [date]<@dt1 then amount*type else 0 end),
本期收入=sum(case when [date]>=@dt1 and [date]<=@dt2 and type=1 then amount else 0 end),
本期发出=sum(case when [date]>=@dt1 and [date]<=@dt2 and type=-1 then -amount else 0 end),
本期结存=sum(type*amount)
from 库存跟综表
where [date]<=@dt2
group by code
)
go
调用:
select * from dbo.GetReport('2002-10-12','2002-10-13')
EndDateTime,
结合Haiwer(海阔天空)的应该时很容易完成的。