create table y_baseinfo(ypid int,pinming varchar(10),jinjia int)
insert into y_baseinfo values(1001 ,'室内机', 500)
insert into y_baseinfo values(1002 ,'室外机', 600)
insert into y_baseinfo values(1003 ,'多联机', 700)
create table y_liushui(ypid int,shuliang int,caozuo varchar(10),riqi datetime)
insert into y_liushui values(1001 ,10 ,'入库', '2011-01-01')
insert into y_liushui values(1001 ,10 ,'出库', '2011-02-15')
insert into y_liushui values(1001 ,50 ,'入库', '2011-05-20')
insert into y_liushui values(1003 ,80 ,'入库', '2011-03-20')
insert into y_liushui values(1003 ,15 ,'出库', '2012-01-23')
insert into y_liushui values(1003 ,5  ,'退库', '2012-01-25')
create table y_kucun(ypid int,shuliang int,caozuo varchar(10),riqi datetime)
insert into y_kucun values(1001 ,10  ,'入库', '2011-01-01')
insert into y_kucun values(1001 ,-10 ,'出库', '2011-02-15')
insert into y_kucun values(1001 ,50  ,'入库', '2011-05-20')
insert into y_kucun values(1003 ,80  ,'入库', '2011-03-20')
insert into y_kucun values(1003 ,-15 ,'出库', '2012-01-23')
insert into y_kucun values(1003 ,5   ,'退库', '2012-01-25')
insert into y_kucun values(1003 ,3   ,'盘点', '2012-01-25')
insert into y_kucun values(1001 ,-1  ,'盘点', '2012-01-27')
insert into y_kucun values(1001 ,-1  ,'盘点', '2012-01-27')
go

select m.ypid ,m.pinming,
       上期结存数量 = isnull((select sum(shuliang) from y_kucun n where n.ypid = m.ypid and convert(varchar(10),n.riqi,120) < '2011-05-20'),0),
       上期结存金额 = isnull((select sum(shuliang) from y_kucun n where n.ypid = m.ypid and convert(varchar(10),n.riqi,120) < '2011-05-20'),0)*m.jinjia,
       本期收入数量 = isnull((select sum(shuliang) from y_kucun n where n.ypid = m.ypid and n.caozuo in ('入库','退库') and convert(varchar(10),n.riqi,120) between '2011-05-20' and '2012-01-31'),0),
       本期收入金额 = isnull((select sum(shuliang) from y_kucun n where n.ypid = m.ypid and n.caozuo in ('入库','退库') and convert(varchar(10),n.riqi,120) between '2011-05-20' and '2012-01-31'),0)*m.jinjia,
       本期发出数量 = isnull((select sum(shuliang) from y_kucun n where n.ypid = m.ypid and n.caozuo = '出库' and convert(varchar(10),n.riqi,