27,579
社区成员
发帖
与我相关
我的任务
分享
业务类型 kaidanriqi danjuhao ypid pinming 本期收入数量 本期收入金额 本期发出数量 本期发出金额
期初结存 '' '' 1001 室内机 0 0 0 0
现金出库 2011-02-15 02 1001 室内机 0 0 10 10*500=5000
挂账入库 2011-05-20 03 1001 室内机 50 50*500=25000 0 0
产品盘点 2012-01-27 '' 1001 室内机 0 0 0 0
产品盘点 2012-01-27 '' 1001 室内机 0 0 0 0
本期结存 '' '' 1001 室内机 0 0 0 0
期初结存 '' '' 1002 室外机 0 0 0 0
本期结存 '' '' 1002 室外机 0 0 0 0
期初结存 '' '' 1003 多联机 0 0 0 0
挂账入库 2011-03-20 04 1003 多联机 80 80*700=56000 0 0
现金出库 2012-01-23 05 1003 多联机 0 0 15 15*700=10500
产品盘点 2012-01-24 '' 1003 多联机 0 0 0 0
挂账退库 2012-01-25 06 1003 多联机 5 5*700=3500 0 0
本期结存 '' '' 1003 多联机 0 0 0 0
接上放不下
本期盘点数量 本期盘点金额 本期结存数量 本期结存金额
0 0 10 10*500=5000
0 0 10-10=0 (10-10)*500=0
0 0 0+50=50 50*500=25000
-1 -1*500=-1000 50-1=49 49*500=24500
-1 -1*500=-1000 49-1=48 48*500=24000
0 0 48 24000
0 0 0 0
0 0 0 0
0 0 0 0
0 0 80 80*700=56000
0 0 80-15=65 65*700=45500
3 3*700=2100 65+3=68 68*700=47600
0 0 68+5=73 73*700=51100
0 0 73 51100
y_baseinfo(基本信息)
ypid pinming jinjia
1001 室内机 500
1002 室外机 600
1003 多联机 700
y_liushui(流水表)
ypid danjuhao shuliang caozuo kaidanriqi fangshi
1001 01 10 入库 2011-01-01 现金
1001 02 10 出库 2011-02-15 现金
1003 04 80 入库 2011-03-20 挂账
1001 03 50 入库 2011-05-20 挂账
1003 05 15 出库 2012-01-23 现金
1003 '' 3 盘点 2012-01-24 产品
1003 06 5 退库 2012-01-25 挂账
1001 '' -1 盘点 2012-01-27 产品
1001 '' -1 盘点 2012-01-27 产品
y_kucun(库存表)
ypid shuliang caozuo riqi
1001 10 入库 2011-01-01
1001 -10 出库 2011-02-15
1003 80 入库 2011-03-20
1001 50 入库 2011-05-20
1003 -15 出库 2012-01-23
1003 3 盘点 2012-01-24
1003 5 退库 2012-01-25
1001 -1 盘点 2012-01-27
1001 -1 盘点 2012-01-27
create table y_liushui(ypid int,danjuhao varchar(10),shuliang int,caozuo varchar(10),riqi datetime,fangshi varchar(10))
insert into y_liushui values(1001 ,'01',10 ,'入库', '2011-01-01','现金')
insert into y_liushui values(1001 ,'02',10 ,'出库', '2011-02-15','现金')
insert into y_liushui values(1001 ,'07',5 ,'出库', '2011-02-17','现金')
insert into y_liushui values(1003 ,'04',80 ,'入库', '2011-03-20','挂账')
insert into y_liushui values(1001 ,'03',50 ,'入库', '2011-05-20','挂账')
insert into y_liushui values(1003 ,'05',15 ,'出库', '2012-01-23','现金')
insert into y_liushui values(1003 ,'06',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 ,-5 ,'出库', '2011-02-17')
insert into y_kucun values(1003 ,80 ,'入库', '2011-03-20')
insert into y_kucun values(1001 ,50 ,'入库', '2011-05-20')
insert into y_kucun values(1003 ,-15 ,'出库', '2012-01-23')
insert into y_kucun values(1003 ,3 ,'盘点', '2012-01-24')
insert into y_kucun values(1003 ,5 ,'退库', '2012-01-25')
insert into y_kucun values(1001 ,-1 ,'盘点', '2012-01-27')
insert into y_kucun values(1001 ,-1 ,'盘点', '2012-01-27')
/*
业务类型 开单日期 danjuhao ypid pinming 本期收入数量 本期收入金额 本期发出数量 本期发出金额 本期盘点数量 本期盘点金额 本期结存数量 本期结存金额
-------- ---------- ---------- ------------------------------ ---------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- -----------
期初结存 1001 室内机 0 0 0 0 0 0 10 5000
现金出库 2011-02-15 02 1001 室内机 0 0 10 5000 0 0 0 0
挂账入库 2011-05-20 03 1001 室内机 50 25000 0 0 0 0 50 25000
产品盘点 2012-01-27 1001 室内机 0 0 0 0 -2 -1000 48 24000
挂账退库 1001 室内机 0 0 0 0 0 0 0 0
本期结存 1001 室内机 0 0 0 0 0 0 48 24000
0 0 0 0 0 0 0 0
期初结存 1002 室外机 0 0 0 0 0 0 0 0
现金出库 1002 室外机 0 0 0 0 0 0 0 0
挂账入库 1002 室外机 0 0 0 0 0 0 0 0
产品盘点 1002 室外机 0 0 0 0 0 0 0 0
挂账退库 1002 室外机 0 0 0 0 0 0 0 0
本期结存 1002 室外机 0 0 0 0 0 0 0 0
0 0 0 0 0 0 0 0
期初结存 1003 多联机 0 0 0 0 0 0 80 56000
现金出库 2012-01-23 05 1003 多联机 0 0 15 10500 0 0 80 56000
挂账入库 2011-03-20 04 1003 多联机 80 56000 0 0 0 0 80 56000
产品盘点 2012-01-24 1003 多联机 0 0 0 0 3 2100 73 51100
挂账退库 2012-01-25 06 1003 多联机 5 3500 0 0 0 0 0 0
本期结存 1003 多联机 0 0 0 0 0 0 73 51100
0 0 0 0 0 0 0 0
(所影响的行数为 21 行)
*/
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,danjuhao varchar(10),shuliang int,caozuo varchar(10),riqi datetime,fangshi varchar(10))
insert into y_liushui values(1001 ,'01',10 ,'入库', '2011-01-01','现金')
insert into y_liushui values(1001 ,'02',10 ,'出库', '2011-02-15','现金')
insert into y_liushui values(1003 ,'04',80 ,'入库', '2011-03-20','挂账')
insert into y_liushui values(1001 ,'03',50 ,'入库', '2011-05-20','挂账')
insert into y_liushui values(1003 ,'05',15 ,'出库', '2012-01-23','现金')
insert into y_liushui values(1003 ,'06',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(1003 ,80 ,'入库', '2011-03-20')
insert into y_kucun values(1001 ,50 ,'入库', '2011-05-20')
insert into y_kucun values(1003 ,-15 ,'出库', '2012-01-23')
insert into y_kucun values(1003 ,3 ,'盘点', '2012-01-24')
insert into y_kucun values(1003 ,5 ,'退库', '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 业务类型,开单日期,danjuhao,ypid,pinming,本期收入数量,本期收入金额,本期发出数量,本期发出金额,本期盘点数量,本期盘点金额,本期结存数量,本期结存金额 from
(
select 业务类型 = '期初结存',
开单日期 = '',
danjuhao = '',
ypid = cast(m.ypid as varchar),
m.pinming ,
本期收入数量 = 0,
本期收入金额 = 0,
本期发出数量 = 0,
本期发出金额 = 0,
本期盘点数量 = 0,
本期盘点金额 = 0,
本期结存数量 = isnull((select sum(shuliang) from y_kucun n where n.ypid = m.ypid and n.caozuo = '入库' 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 n.caozuo = '入库' and convert(varchar(10),n.riqi,120) < '2011-05-20'),0)*m.jinjia,
ypid px_ypid,
pinming px_pinming,
px = 1
from y_baseinfo m
union all
select 业务类型 = '现金出库',
开单日期 = isnull((select top 1 convert(varchar(10),t.riqi,120) from y_liushui t where t.ypid = m.ypid and t.fangshi = '现金' and t.caozuo = '出库' and convert(varchar(10),t.riqi,120) <= '2012-01-31' order by t.riqi),''),
danjuhao = isnull((select top 1 t.danjuhao from y_liushui t where t.ypid = m.ypid and t.fangshi = '现金' and t.caozuo = '出库' and convert(varchar(10),t.riqi,120) <= '2012-01-31' order by t.riqi),''),
ypid = cast(m.ypid as varchar),
m.pinming ,
本期收入数量 = 0,
本期收入金额 = 0,
本期发出数量 = isnull((select sum(t.shuliang) from y_liushui t where t.ypid = m.ypid and t.fangshi = '现金' and t.caozuo = '出库' and convert(varchar(10),t.riqi,120) <= '2012-01-31'),0),
本期发出金额 = isnull((select sum(t.shuliang) from y_liushui t where t.ypid = m.ypid and t.fangshi = '现金' and t.caozuo = '出库' and convert(varchar(10),t.riqi,120) <= '2012-01-31'),0)*m.jinjia,
本期盘点数量 = 0,
本期盘点金额 = 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),
本期结存金额 = 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,
ypid px_ypid,
pinming px_pinming,
px = 2
from y_baseinfo m
union all
select 业务类型 = '挂账入库',
开单日期 = isnull((select top 1 convert(varchar(10),t.riqi,120) from y_liushui t where t.ypid = m.ypid and t.fangshi = '挂账' and t.caozuo = '入库' and convert(varchar(10),t.riqi,120) <= '2012-01-31' order by t.riqi),''),
danjuhao = isnull((select top 1 t.danjuhao from y_liushui t where t.ypid = m.ypid and t.fangshi = '挂账' and t.caozuo = '入库' and convert(varchar(10),t.riqi,120) <= '2012-01-31' order by t.riqi),''),
ypid = cast(m.ypid as varchar),
m.pinming ,
本期收入数量 = isnull((select sum(t.shuliang) from y_liushui t where t.ypid = m.ypid and t.fangshi = '挂账' and t.caozuo = '入库' and convert(varchar(10),t.riqi,120) <= '2012-01-31'),0),
本期收入金额 = isnull((select sum(t.shuliang) from y_liushui t where t.ypid = m.ypid and t.fangshi = '挂账' and t.caozuo = '入库' and convert(varchar(10),t.riqi,120) <= '2012-01-31'),0)*m.jinjia,
本期发出数量 = 0,
本期发出金额 = 0,
本期盘点数量 = 0,
本期盘点金额 = 0,
本期结存数量 = isnull((select sum(shuliang) from y_liushui t where t.ypid = m.ypid and t.fangshi = '挂账' and t.caozuo = '入库' and convert(varchar(10),t.riqi,120) <= '2011-05-20'),0),
本期结存金额 = isnull((select sum(shuliang) from y_liushui t where t.ypid = m.ypid and t.fangshi = '挂账' and t.caozuo = '入库' and convert(varchar(10),t.riqi,120) <= '2011-05-20'),0)*m.jinjia,
ypid px_ypid,
pinming px_pinming,
px = 3
from y_baseinfo m
union all
select 业务类型 = '产品盘点',
开单日期 = isnull((select top 1 convert(varchar(10),t.riqi,120) from y_kucun t where t.ypid = m.ypid and t.caozuo = '盘点' and convert(varchar(10),t.riqi,120) <= '2012-01-31' order by t.riqi),''),
danjuhao = '',
ypid = cast(m.ypid as varchar),
m.pinming ,
本期收入数量 = 0,
本期收入金额 = 0,
本期发出数量 = 0,
本期发出金额 = 0,
本期盘点数量 = isnull((select sum(t.shuliang) from y_kucun t where t.ypid = m.ypid and t.caozuo = '盘点' and convert(varchar(10),t.riqi,120) <= '2012-01-31'),0),
本期盘点金额 = isnull((select sum(t.shuliang) from y_kucun t where t.ypid = m.ypid and t.caozuo = '盘点' and convert(varchar(10),t.riqi,120) <= '2012-01-31'),0)*m.jinjia,
本期结存数量 = isnull((select sum(t.shuliang) from y_kucun t where t.ypid = m.ypid and convert(varchar(10),t.riqi,120) <= '2012-01-31'),0),
本期结存金额 = isnull((select sum(t.shuliang) from y_kucun t where t.ypid = m.ypid and convert(varchar(10),t.riqi,120) <= '2012-01-31'),0)*m.jinjia,
ypid px_ypid,
pinming px_pinming,
px = 4
from y_baseinfo m
union all
select 业务类型 = '挂账退库',
开单日期 = isnull((select top 1 convert(varchar(10),t.riqi,120) from y_liushui t where t.ypid = m.ypid and t.fangshi = '挂账' and t.caozuo = '退库' and convert(varchar(10),t.riqi,120) <= '2012-01-31' order by t.riqi),''),
danjuhao = isnull((select top 1 t.danjuhao from y_liushui t where t.ypid = m.ypid and t.fangshi = '挂账' and t.caozuo = '退库' and convert(varchar(10),t.riqi,120) <= '2012-01-31' order by t.riqi),''),
ypid = cast(m.ypid as varchar),
m.pinming ,
本期收入数量 = isnull((select sum(t.shuliang) from y_liushui t where t.ypid = m.ypid and t.fangshi = '挂账' and t.caozuo = '退库' and convert(varchar(10),t.riqi,120) <= '2012-01-31'),0),
本期收入金额 = isnull((select sum(t.shuliang) from y_liushui t where t.ypid = m.ypid and t.fangshi = '挂账' and t.caozuo = '退库' and convert(varchar(10),t.riqi,120) <= '2012-01-31'),0)*m.jinjia,
本期发出数量 = 0,
本期发出金额 = 0,
本期盘点数量 = 0,
本期盘点金额 = 0,
本期结存数量 = isnull((select sum(shuliang) from y_liushui t where t.ypid = m.ypid and t.fangshi = '挂账' and t.caozuo = '退库' and convert(varchar(10),t.riqi,120) <= '2011-05-20'),0),
本期结存金额 = isnull((select sum(shuliang) from y_liushui t where t.ypid = m.ypid and t.fangshi = '挂账' and t.caozuo = '退库' and convert(varchar(10),t.riqi,120) <= '2011-05-20'),0)*m.jinjia,
ypid px_ypid,
pinming px_pinming,
px = 5
from y_baseinfo m
union all
select 业务类型 = '本期结存',
开单日期 = '',
danjuhao = '',
ypid = cast(m.ypid as varchar),
m.pinming ,
本期收入数量 = 0,
本期收入金额 = 0,
本期发出数量 = 0,
本期发出金额 = 0,
本期盘点数量 = 0,
本期盘点金额 = 0,
本期结存数量 = isnull((select sum(shuliang) from y_kucun n where n.ypid = m.ypid and convert(varchar(10),n.riqi,120) <= '2012-01-31'),0),
本期结存金额 = isnull((select sum(shuliang) from y_kucun n where n.ypid = m.ypid and convert(varchar(10),n.riqi,120) <= '2012-01-31'),0)*m.jinjia,
ypid px_ypid,
pinming px_pinming,
px = 6
from y_baseinfo m
union all
select 业务类型 = '',
开单日期 = '',
danjuhao = '',
ypid = '',
pinming = '',
本期收入数量 = 0,
本期收入金额 = 0,
本期发出数量 = 0,
本期发出金额 = 0,
本期盘点数量 = 0,
本期盘点金额 = 0,
本期结存数量 = 0,
本期结存金额 = 0,
ypid px_ypid,
pinming px_pinming,
px = 7
from y_baseinfo m
) t
order by px_ypid , px
drop table y_baseinfo,y_liushui,y_kucun