出库表DBXP_CKD
结构
DJH [char] (15) 单据号
GYS_NO [char] (10) 销售商
SPMC_NO [char] (10) 商品名称
RKSJ [datetime] 出库时间
SL [int] 数量
JE [int] 金额
结构
DJH [char] (15) 单据号
GYS_NO [char] (10) 销售商
SPMC_NO [char] (10) 商品名称
RKSJ [datetime] 出库时间
SL [int] 数量
JE [int] 金额
declare @DBXP_CKD table(DJH varchar(15),GYS_NO varchar(10),SPMC_NO varchar(10),CKSJ datetime,SL int,JE int)insert into @DBXP_RKD select 'rk0320060101002','0001','03','2006-01-01',134,1474
insert into @DBXP_RKD select 'rk0320060101002','0001','02','2006-01-01',125,1250
insert into @DBXP_RKD select 'rk0320060101002','0001','01','2006-01-01',43 ,387insert into @DBXP_CKD select 'ck0320060104001','0010','02','2006-01-04',12 ,108
insert into @DBXP_CKD select 'ck0320060104001','0010','01','2006-01-04',4 ,36
insert into @DBXP_CKD select 'ck0320060106001','0010','03','2006-01-06',16 ,672
insert into @DBXP_CKD select 'ck0320060109001','0004','01','2006-01-09',6 ,1680
insert into @DBXP_CKD select 'ck0320060109001','0004','02','2006-01-09',6 ,1680
insert into @DBXP_CKD select 'ck0320060109001','0004','03','2006-01-09',4 ,1120
insert into @DBXP_CKD select 'ck0320060110001','0009','01','2006-01-10',3 ,840
insert into @DBXP_CKD select 'ck0320060112001','0007','01','2006-01-12',12 ,3360
insert into @DBXP_CKD select 'ck0320060112001','0007','02','2006-01-12',4 ,1120
insert into @DBXP_CKD select 'ck0320060112002','0009','01','2006-01-12',2 ,560
insert into @DBXP_CKD select 'ck0320060116001','0009','01','2006-01-16',3 ,840select
convert(char(10),RKSJ,120)as 发生日期,
SPMC_NO as 商品名称,
case TYPE when 1 then '' else GYS_NO end as 销售商,
case TYPE when 2 then '' else GYS_NO end as 供应商,
sum(case TYPE when 1 then 0 else SL end) as 进货,
sum(case TYPE when 2 then 0 else SL end) as 出货,
isnull((select sum(SL) from @DBXP_RKD where SPMC_NO=a.SPMC_NO and RKSJ<=a.RKSJ),0)-
isnull((select sum(SL) from @DBXP_CKD where SPMC_NO=a.SPMC_NO and CKSJ<=a.RKSJ),0) as 存货
from
(select *,1 as TYPE from @DBXP_RKD
union all
select *,2 as TYPE from @DBXP_CKD) a
group by
RKSJ,SPMC_NO,TYPE,GYS_NO
union all
select
'',
SPMC_NO,
'',
'',
null,
null,
sum(case TYPE when 1 then SL else -SL end)
from
(select *,1 as TYPE from @DBXP_RKD
union all
select *,2 as TYPE from @DBXP_CKD) a
group by
SPMC_NO/*发生日期 商品名称 销售商 供应商 进货 出货 存货
---------- ---------- ---------- ---------- ----------- ----------- -----------
2006-01-01 01 0001 0 43 43
2006-01-01 02 0001 0 125 125
2006-01-01 03 0001 0 134 134
2006-01-04 01 0010 4 0 39
2006-01-04 02 0010 12 0 113
2006-01-06 03 0010 16 0 118
2006-01-09 01 0004 6 0 33
2006-01-09 02 0004 6 0 107
2006-01-09 03 0004 4 0 114
2006-01-10 01 0009 3 0 30
2006-01-12 01 0007 12 0 16
2006-01-12 01 0009 2 0 16
2006-01-12 02 0007 4 0 103
2006-01-16 01 0009 3 0 13
01 NULL NULL 13
02 NULL NULL 103
03 NULL NULL 114
*/
---------------------------------------------------------------------------------------------------------------------------------------
declare @DBXP_RKD table(DJH varchar(15),GYS_NO varchar(10),SPMC_NO varchar(10),RKSJ datetime,SL int,JE int)
declare @DBXP_CKD table(DJH varchar(15),GYS_NO varchar(10),SPMC_NO varchar(10),CKSJ datetime,SL int,JE int)insert into @DBXP_RKD select 'rk0320060101002','0001','03','2006-01-01',134,1474
insert into @DBXP_RKD select 'rk0320060101002','0001','02','2006-01-01',125,1250
insert into @DBXP_RKD select 'rk0320060101002','0001','01','2006-01-01',43 ,387insert into @DBXP_CKD select 'ck0320060104001','0010','02','2006-01-04',12 ,108
insert into @DBXP_CKD select 'ck0320060104001','0010','01','2006-01-04',4 ,36
insert into @DBXP_CKD select 'ck0320060106001','0010','03','2006-01-06',16 ,672
insert into @DBXP_CKD select 'ck0320060109001','0004','01','2006-01-09',6 ,1680
insert into @DBXP_CKD select 'ck0320060109001','0004','02','2006-01-09',6 ,1680
insert into @DBXP_CKD select 'ck0320060109001','0004','03','2006-01-09',4 ,1120
insert into @DBXP_CKD select 'ck0320060110001','0009','01','2006-01-10',3 ,840
insert into @DBXP_CKD select 'ck0320060112001','0007','01','2006-01-12',12 ,3360
insert into @DBXP_CKD select 'ck0320060112001','0007','02','2006-01-12',4 ,1120
insert into @DBXP_CKD select 'ck0320060112002','0009','01','2006-01-12',2 ,560
insert into @DBXP_CKD select 'ck0320060116001','0009','01','2006-01-16',3 ,840select
convert(char(10),RKSJ,120)as 发生日期,
SPMC_NO as 商品名称,
case TYPE when 1 then '' else GYS_NO end as 销售商,
case TYPE when 2 then '' else GYS_NO end as 供应商,
sum(case TYPE when 1 then 0 else SL end) as 进货,
sum(case TYPE when 2 then 0 else SL end) as 出货,
isnull((select sum(SL) from @DBXP_RKD where SPMC_NO=a.SPMC_NO and (RKSJ<a.RKSJ or(RKSJ=a.RKSJ and GYS_NO<=a.GYS_NO))),0)-
isnull((select sum(SL) from @DBXP_CKD where SPMC_NO=a.SPMC_NO and (CKSJ<a.RKSJ or(CKSJ=a.RKSJ and GYS_NO<=a.GYS_NO))),0) as 存货
from
(select *,1 as TYPE from @DBXP_RKD
union all
select *,2 as TYPE from @DBXP_CKD) a
group by
RKSJ,SPMC_NO,TYPE,GYS_NO
union all
select
'',
SPMC_NO,
'',
'',
null,
null,
sum(case TYPE when 1 then SL else -SL end)
from
(select *,1 as TYPE from @DBXP_RKD
union all
select *,2 as TYPE from @DBXP_CKD) a
group by
SPMC_NO/*
发生日期 商品名称 销售商 供应商 进货 出货 存货
---------- ---------- ---------- ---------- ----------- ----------- -----------
2006-01-01 01 0001 0 43 43
2006-01-01 02 0001 0 125 125
2006-01-01 03 0001 0 134 134
2006-01-04 01 0010 4 0 39
2006-01-04 02 0010 12 0 113
2006-01-06 03 0010 16 0 118
2006-01-09 01 0004 6 0 33
2006-01-09 02 0004 6 0 107
2006-01-09 03 0004 4 0 114
2006-01-10 01 0009 3 0 30
2006-01-12 01 0007 12 0 18
2006-01-12 01 0009 2 0 16
2006-01-12 02 0007 4 0 103
2006-01-16 01 0009 3 0 13
01 NULL NULL 13
02 NULL NULL 103
03 NULL NULL 114
*/